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
