troubleshooting Question

Problems "swapping" SQL Server DBs

Avatar of canuckconsulting
canuckconsultingFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server
2 Comments1 Solution113 ViewsLast Modified:
We have a database which is refreshed three times throughout the day via SSIS.  To minimize downtime we populate an identical staging database and then "swap" the production DB with the staging DB.  This is accomplished by the script below.

We are having two problems:
1) Occasionally the DB swap fails due to one of the DBs having active connections despite setting it to single user mode prior to swap
2) Despite  SSRS reports having data sources pointing to DB_Production, after the swap they appear to be referencing DB_Staging.

Is there a better way to swap the DBs?

use DB_Production;
--Remove User from production DB about to be swapped out
exec sp_dropuser 'MyUser';

--Set to single user to close all open connections
ALTER DATABASE DB_Production SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE DB_Production SET MULTI_USER WITH ROLLBACK IMMEDIATE;

--Rename 
exec SP_RENAMEDB 'DB_Production','DB_TMP';

--Set to single user to close all open connections
ALTER DATABASE DB_Production_staging SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE DB_Production_staging SET MULTI_USER WITH ROLLBACK IMMEDIATE;

--Rename
exec SP_RENAMEDB 'DB_Staging','DB_Production's;
exec SP_RENAMEDB 'DB_TMP','DB_Staging';	

use DB_Production;
--Add User to newly swapped production DB
exec sp_adduser 'MyUser';
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros