usp_ToggleDatabases
usp_ToggleDatabases is a simple procedure that will switch the names of two databases.
One of the main problems with using sp_renamedb to switch databases is that the procedure will wait until the database is free before the rename can be completed. If the new live database is also in use then there is a likelyhood that the renaming of the current database could be successful and the second database will fail.
The procedure attached will wait until all databases are free before trying to switch them around.
A total of 5 sets of databases can be passed to this procedure and it will not start switching any database unless all have noone accessing any of them at a given point.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/* **********************************************************************************
* Procedure : usp_ToggleDatabases
*
* Description:
* Switches two databases around
*
* Parameters:
* @inLatestDatabasex : Name of database containing latest data
* @inCurrentDatabasex : name of database to switch with
*
* Note: There can be up to 5 database combinations defined in any one call
*
* History:
* Date | Author | Notes
* ---------+-------------------+-----------------------------------------------------
* Jul 2005 | David Rowland | Initial development
********************************************************************************** */ALTER PROCEDURE usp_ToggleDatabases
(@inLatestDatabase1 sysname,
@inCurrentDatabase1 sysname,
@inLatestDatabase2 sysname = NULL,
@inCurrentDatabase2 sysname = NULL,
@inLatestDatabase3 sysname = NULL,
@inCurrentDatabase3 sysname = NULL,
@inLatestDatabase4 sysname = NULL,
@inCurrentDatabase4 sysname = NULL,
@inLatestDatabase5 sysname = NULL,
@inCurrentDatabase5 sysname = NULL)
AS
BEGIN
-- Define temporary database name
declare @BackupDBName sysname
-- While any of the databases are being used, wait for a period of time
WHILE EXISTS (SELECT 1 FROM sysprocesses WHERE DB_NAME(dbid) IN (@inCurrentDatabase1,@inCurrentDatabase2,@inCurrentDatabase3,@inCurrentDatabase4, @inCurrentDatabase5,
@inLatestDatabase1,@inLatestDatabase2,@inLatestDatabase3,@inLatestDatabase4, @inLatestDatabase5))
WAITFOR DELAY '00:00:10'
-- Set the backup name to currentname + '_old'
SET @BackupDBName = @inCurrentDatabase1 + '_old'
-- Rename current database to backup
Exec sp_renamedb @inCurrentDatabase1, @BackupDBName
-- Rename latest database to current
Exec sp_renamedb @inLatestDatabase1, @inCurrentDatabase1
-- Rename backup back to latest
Exec sp_renamedb @BackupDBName, @inLatestDatabase1
-- Repeat the steps for remaining databases
IF @inLatestDatabase2 IS NOT NULL
BEGIN
SET @BackupDBName = @inCurrentDatabase2 + '_old'
Exec sp_renamedb @inCurrentDatabase2, @BackupDBName
Exec sp_renamedb @inLatestDatabase2, @inCurrentDatabase2
Exec sp_renamedb @BackupDBName, @inLatestDatabase2
END
IF @inLatestDatabase3 IS NOT NULL
BEGIN
SET @BackupDBName = @inCurrentDatabase3 + '_old'
Exec sp_renamedb @inCurrentDatabase3, @BackupDBName
Exec sp_renamedb @inLatestDatabase3, @inCurrentDatabase3
Exec sp_renamedb @BackupDBName, @inLatestDatabase3
END
IF @inLatestDatabase4 IS NOT NULL
BEGIN
SET @BackupDBName = @inCurrentDatabase4 + '_old'
Exec sp_renamedb @inCurrentDatabase4, @BackupDBName
Exec sp_renamedb @inLatestDatabase4, @inCurrentDatabase4
Exec sp_renamedb @BackupDBName, @inLatestDatabase4
END
IF @inLatestDatabase5 IS NOT NULL
BEGIN
SET @BackupDBName = @inCurrentDatabase5 + '_old'
Exec sp_renamedb @inCurrentDatabase5, @BackupDBName
Exec sp_renamedb @inLatestDatabase5, @inCurrentDatabase5
Exec sp_renamedb @BackupDBName, @inLatestDatabase5
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 
