We help IT Professionals succeed at work.
Get Started

Problems "swapping" SQL Server DBs

112 Views
Last Modified: 2018-04-06
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';

Open in new window

Comment
Watch Question
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE