Beware: Renaming a database with non-schemabound views

For many years now I have been aware that views may need refreshing. If you create a view without using the “SCHEMABINDING” clause, you will need to refresh the view if any of the underlying objects change (e.g. change the length of a column). This can be achieved by using either sp_refreshview or sp_recompile.

This past week I got reminded about this in a most unexpected situation. At a customer who is using Microsoft Dynamics CRM, we had a need to create some views to abstract others from the sometimes complicated nature of a CRM database. Because the owners of the CRM database did not want us to add these views to the actual CRM database, we created a new database purely to house the views. This database is guaranteed to live on the same SQL instance as the CRM database so we simply created the views with cross database queries (e.g. select columnA from SourceDB.dbo.Table1). This solution has been working fine for months. I should mention that this environment was a test environment (NOT Production).

Where it went pear-shaped was when they decided to rename the CRM database and restore from a recent backup. The restored database was named the same as the original CRM database name. Without giving it any major thought, I assumed that this would be a seamless change as far as the views go. I was wrong.

To cut a long story short, the views kept on working, but to our dismay we eventually realised that the data being returned was from the old renamed database, not the restored one. Since renaming a database is simply a metadata change, the views continued to point to the original database. Although I now understand why this is, it caught me off guard and I feel that this is a situation that Microsoft should address.

Below are some scripts that will allow you to view this behaviour for yourself. These assume that you are a sysadmin on the server you’re using. These scripts were run against SQL Server 2008 R2 SP1.

Firstly, create a source database with a simple table and one row of data (make sure that you adjust the physical location of the data files below):

use master
go

create database SourceDB
ON (NAME = SourceDb, FILENAME = ‘ <Folder>\SourceDB.mdf’)
LOG ON (NAME = SourceDb_Log, FILENAME = ‘<Folder>\SourceDB_Log.ldf’)
go

use SourceDB;
go

create table SourceTable (ID int, Name nvarchar(10));

insert into SourceTable(ID, Name)
values(1, ‘Craig’);

Now create the database containing a view:

use master
go

create database ViewDB;
go

use ViewDB;
go

create view vSourceTable
as
select ID, Name
from SourceDB.dbo.SourceTable;

go

If you select the data from the view now you’ll see the data that we inserted into the database:

select ID, Name from vSourceTable;

image

Now rename the SourceDB:

use master
go
ALTER DATABASE SourceDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO 

exec SP_RENAMEDB ‘SourceDB’, ‘SourceDB_old’
Go
ALTER DATABASE SourceDB_old  SET MULTI_USER
GO

At this point some might think that the view should fail if you query it, but it doesn’t. It returns the same result set as before.

Now create a new database with the same name as the original but with different data:

use master
go

create database SourceDB
ON (NAME = SourceDb_New, FILENAME = ‘C:\Databases\Database Files\SourceDB_New.mdf’)
LOG ON (NAME = SourceDb_Log_New, FILENAME = ‘C:\Databases\Database Files\SourceDB_Log_New.ldf’)
go

use SourceDB;
go

create table SourceTable (ID int, Name nvarchar(10));

insert into SourceTable(ID, Name)
values(2, ‘Bryden’);

If you query the view it is still showing the original values:

select ID, Name from vSourceTable;

image

In order to get the view to start working on the correct database, we need to refresh the view:

exec sp_refreshview ‘vSourceTable’

Now if we query the view, it is using the correct source:

select ID, Name from vSourceTable;

image

Strange but true!

Advertisements

About Craig Bryden

I am a husband, father and sometimes I masquerade as an experienced Business Intelligence practitioner and Database Developer. I focus on the Microsoft SQL Server suite of products, and in addition have experience with C# and some team leading experience. I have also presented at SQL Saturday events and user groups I currently hold multiple Microsoft certifications, MCTS - SQL Server 2005, and MCTS - SQL Server 2008 Business Intelligence Development and Maintenance, MCTS - SQL Server 2008 Database Developer, MCITP - Business Intelligence Developer 2008, and Microsoft Certified Trainer, MCSA & MCSE - Business Intelligence (2012)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s