Avatar of canuckconsulting
canuckconsulting
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

Problems "swapping" SQL Server DBs

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

Microsoft SQL Server

Avatar of undefined
Last Comment
canuckconsulting

8/22/2022 - Mon