SQL Server 2008 Swapping Database Names (sp_renameDB Bug)
Published: 3rd May 2013
This is a bug I found within SQL 2008 (and R2) which has caused me endless problems within my system. I now have a workaround, but it’s clumsy and I really don’t appreciate having to do it.

Background



I was at a company in which they had a very large reporting database holding masses of aggregate data. The data in this database changed with such frequency that it was far more time, cpu, and io efficient to drop and re-create all tables within the master copy of this database than it was to run insert / update / delete (or merge) statements.

Therefore what happened was this… a staging reporting database (we’ll call it “RepDataStaging”) was generated each night from scratch involving the dropping and re-creating of all tables. Then, because a backup / restore was too time consuming and this was connected to a 24 hour reporting system, we used the minimal downtime option of renaming the database to “flip” them. This only takes a couple of seconds.

So we renamed as follows:
        • RepDataLive -> RepDataOld
        • RepDataStaging -> RepDataLive
        • RepDataOld -> RepDataStaging

This worked perfectly within SQL Server 2005 and all versions before. However, in SQL Server 2008 (and R2) we started getting reports of incorrect data and errors appearing.

After a lot of head scratching and investigation I found that SQL Server was not honouring a name swap with regard to views in other databases. I will explain this more clearly in the following example:

Example



Create 3 databases (I’m going to use the most basic of statements and allow the SQL defaults to be used for all database creation settings)…

use master
go

create database repDataLive
go

create database repDataStaging
go

create database testFlip
go


Now, let’s create a basic table on each repData database…

create table repDataLive.dbo.repDataTable
(
  randomData varchar(100)
)
go

create table repDataStaging.dbo.repDataTable
(
  randomData varchar(100)
)
go


Let’s put some very simple data into the table so that we can differentiate between the two quite clearly…

insert into repDataLive.dbo.repDataTable
select 'This is the original Live database'

insert into repDataStaging.dbo.repDataTable
select 'This is the original Staging database'
go


Create a view

use testFlip
go

create view dbo.myTestView
as
       select
*
      
from repDataLive.dbo.repDataTable
go


We can test this simply enough by just running a select on the view…

select *
from testFlip.dbo.myTestView


Also, for clarity, we’ll pull up the database IDs for the databases so that we can verify our name changes once we make them…

select database_id, name
from sys.databases
where name like 'rep%'


It’s now time to make the name changes in order to “flip” the databases…

use master
go

exec sp_renamedb 'repDataLive', 'repDataOld'
go

exec sp_renamedb 'repDataStaging', 'repDataLive'
go

exec sp_renamedb 'repDataOld', 'repDataStaging'
go


Double check this in the databases system table…
select database_id, name
from sys.databases
where name like 'rep%'


And we have indeed swapped the two databases which is exactly what we require.

However, this is where everything falls down… If you select from the view in testFlip we get the following:

select *
from testFlip.dbo.myTestView


Yes… there’s no change at all. Although the databases have changed, SQL Server has not fully registered this and now all views are pointing to the original, not the new database. As such, in the case of the firm I was working for, all the data was now out of date and so producing unexpected results to queries.

Solution



The way to workaround this is to “refresh the view”. This is demonstrated as follows:

As this screenshot shows, running sp_refreshView fixes the problem.

That’s great, but not entirely helpful when you have a server with in excess of 100 referencing views!!! As such I had to create a loop which runs as the next step in the scheduled task of the job performing the flip.

This was not good enough in itself though as there is inevitably the odd view that will not recompile maybe because it is old, columns no longer exist, and it has never been dropped.

These will cause sp_refreshView to fail with a critical error that cannot be caught in a try and catch statement. Because of this I ended up creating a 2 step loop on the end of my flip job which looked like this…

Step 5 was a guaranteed successful step as it was simply “select 1” and therefore it always fired back into step 4 which was running my sp_refreshView code.

In order to stop an endless loop, if a view failed to recompile, the name is entered into a table and skipped on the next run. This meant that, once successful, all my views were now looking at the correct data and I also had a table containing any views that needed investigating and probably deletion.

The full script I used is as follows: (this creates all tracking tables if they do not exist but you will have to change database names where applicable)

set nocount on

use boredDBA
go

if OBJECT_ID('dbo.viewRefreshException') is null
begin
    create table dbo.viewRefreshException
    (
        dbName varchar(100) not null,
        objectName varchar(250) not null,
        objectType char(1) not null
    )
end

use master
go

if OBJECT_ID('tempDB..#views') is not null drop table #views
go

declare @flippedDB varchar(100) = 'repDataLive' -- change accordingly
declare @db table
(
    id tinyint identity(1, 1),
    dbName varchar(100)
)
insert into @db
select name
from sys.databases

create table #views
(
    id int identity(1, 1),
    dbName varchar(100),
    name varchar(250)
)

declare @counter int = 1, @dbName varchar(100), @viewName varchar(250),
    @sql nvarchar(max)

declare @reset table
(
    id int identity(1, 1),
    cmd nvarchar(max)
)
insert into @reset
select 'if exists (select * from ' + dbName + '.sys.objects where name = ''' +
    objectName + ''' and modify_date > dateadd(dd, -1, current_timestamp)) begin     delete from boredDBA.dbo.viewRefreshException where dbName = ''' + dbName + '''     and objectName = ''' + objectName + ''' end'
from boredDBA.dbo.viewRefreshException

while @counter <= (select MAX(id) from @reset)
begin
    select @sql = cmd
    from @reset
    where id = @counter

    exec sp_executeSQL @sql

    set @counter += 1
end

set @counter = 1

while @counter <= (select MAX(id) from @db)
begin
    select @sql = 'insert into #views select table_catalog, table_name     from ' + dbName + '.information_schema.views     where view_definition like ''%' + @flippedDB + '.%''     and table_catalog + ''..'' + table_name not in
    ( select dbName + ''..'' + objectName     from boredDBA.dbo.viewRefreshException     where objectType = ''V'' )'

    from @db
    where id = @counter

    exec sp_executeSQL @sql

    set @counter += 1
end

set @counter = 1

while @counter <= (select MAX(id) from #views)
begin
    select @dbName = dbName, @viewName = name,
            @sql = 'use ' + dbName + '; exec sp_refreshView ''' + name + ''''
    from #views
    where id = @counter

    insert into boredDBA.dbo.viewRefreshException
    select @dbName, @viewName, 'V'

    exec sp_executeSQL @sql

    delete from boredDBA.dbo.viewRefreshException
    where objectName = @viewName
    and dbName = @dbName

    set @counter += 1
end

set nocount off
Comments:
NB: Comments will only appear once they have been moderated.