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.

Tagged with: , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*