MSSQL Exchange names 2 databases

Using the following Stored Procedure you can swap the names of two databases. For example, to bring a recently updated database online, after which the old database can be filled again. To exchange database names, a third name is required. This is the name of database2 with the addition ‘_OLD’.

/****** Object:  StoredProcedure [dbo].[DbNameExchange]    Script Date: 12/01/2012 09:15:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ==============================================================
-- Author:		Tim van Kooten Niekerk
-- Create date: 2012-12-01-A
-- Description:	Exchange database names between 2 databases...
-- ==============================================================

CREATE PROCEDURE [dbo].[DbNameExchange]
    @vDatabase1 nvarchar(256),
    @vDatabase2 nvarchar(256)
    
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @H1 AS int
  DECLARE @vDatabaseT AS nvarchar(256)
  DECLARE @vQuery1 AS nvarchar(512)

  -- T.b.v. wisselen van database namen is er een derde naam nodig...
  SET @vDatabaseT = @vDatabase2 + N'_OLD'

  -- Check rename action prefered state before rename action 1/3...
  IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
     AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
     AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 0
  BEGIN
    -- Geef de secundaire db een tijdelijke naam...
    BEGIN TRY
      SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase2 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;WAITFOR DELAY ''00:00:05'';ALTER DATABASE [' + @vDatabase2 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabase2 + '] MODIFY NAME = [' + @vDatabaseT + '];ALTER DATABASE [' + @vDatabaseT + '] SET MULTI_USER'
      EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
    END TRY
    BEGIN CATCH
    END CATCH
    -- Check rename action prefered state before rename action 2/3...
    IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
      AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 0
      AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 1
    BEGIN
      -- Geef de primaire db de naam van de secundaire db...
      BEGIN TRY
	    SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase1 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;WAITFOR DELAY ''00:00:05'';ALTER DATABASE [' + @vDatabase1 + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabase1 + '] MODIFY NAME = [' + @vDatabase2 + '];ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
      END TRY
      BEGIN CATCH
      END CATCH
  	  -- Check rename action prefered state before rename action 3/3...
      IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 0
        AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
        AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 1
      BEGIN
        -- Geef de db met de tijdelijke naam de naam van de primaire db...
        BEGIN TRY
	      SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase1 + '];ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
          EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
        END TRY
	    BEGIN CATCH
        END CATCH
	    IF (SELECT COUNT(*) FROM sys.databases where name = @vDatabase1) = 1
          AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabase2) = 1
          AND (SELECT COUNT(*) FROM sys.databases where name = @vDatabaseT) = 0
	    BEGIN
	      PRINT 'INFO: Rollout actie correct uitgevoerd...'
	    END
	    ELSE BEGIN
          PRINT 'ERROR: Rollout actie is niet volledig uitgevoerd, probeer het nomaals...'
	      BEGIN TRY 
	        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase1 + '];ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
            EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
          END TRY
	      BEGIN CATCH
          END CATCH
          -- Niet gelukt, dan in ieder geval multi_user activeren...
		  SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET MULTI_USER'
          EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
	    END
	  END
    ELSE BEGIN
	    -- Something went wrong trying to rollback...
	    PRINT 'ERROR: Fout geconstateerd, probeer terug te draaien...'
        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabaseT + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [' + @vDatabaseT + '] MODIFY NAME = [' + @vDatabase2 + '];ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
        SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase1 + '] SET MULTI_USER'
        EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
  	  END
    END
    ELSE BEGIN
    -- Something went wrong trying to rollback...
    PRINT 'ERROR: Fout geconstateerd, probeer terug te draaien...'
    SET @vQuery1 = 'ALTER DATABASE [' + @vDatabase2 + '] SET MULTI_USER'
    EXEC sp_prepexec @H1 OUTPUT, null , @vQuery1
    END
    -- Unprepare statements...
    EXEC sp_unprepare @H1
  END
  ELSE BEGIN
    PRINT 'ERROR: Startsituatie niet correct...'
  END
END

GO