SQL Script

To improve a monthly process I created a SQL script to break mirroring, drop the old database, and swap the new database into rotation. This process was a manual task that is performed every month in three regions. By using the new script we will see some significant reduction in downtime caused by the process. Below is a sample of the code from the script to illustrate the process with only one database.

 
USE master
GO

/* Remove Mirroring */
ALTER DATABASE OldDBName SET PARTNER OFF
GO

/* Drop Old Database */ 
EXEC sp_dboption OldDBName, ‘Single User’, True
GO
EXEC master.dbo.sp_detach_db @dbname = N’OldDBName’
GO

/* Rename New Database to the same name as the Old Database */
EXEC sp_dboption NewDBName, ‘Single User’, True
GO
EXEC sp_renamedb ‘NewDBName’, ‘OldDBName’
GO
EXEC sp_dboption OldDBName, ‘Single User’, False
GO