canuckconsulting
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER