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
I began programming in C++ when I was in college. Odd for a business major, but hey I am a Dork. After college I got a job as System Administrator. As a System Administrator I was in charge of web administration. My journey as a PHP web developer had begun. Since that time I have gained an in depth knowledge of CSS, Javascript, XML and MySQL. With changes and advances to technology I have also began learning AJAX. I started Blue Fire Development to do freelance work in my spare time.
Leave a Reply