0
(0)

Many a times when we are working on database migration project, we migrate all user databases from old SQL Server to new SQL Server and at the end of successful migration we have to rename databases on old SQL Server to avoid unwanted access on it. If databases are very few, then it can be achieved easily by manually renaming databases. However, when number of databases are more, then it becomes cumbersome to kill the active connections first and then renaming it . In such cases, below query can be quite useful to rename all databases in one go.

Declare @DBRENAME AS VARCHAR(52)
-- Mention keyword that needs to be added at the end of DB
SET @DBRENAME='_RETIRED' 
DECLARE @DBName AS VARCHAR(52)

DECLARE Cur CURSOR FOR

--List user databaes
  SELECT name from
sys.databases
where database_id>4

OPEN Cur
FETCH Next FROM Cur INTO @DBName
WHILE @@FETCH_STATUS = 0
  BEGIN
  
--Innser  Cursor Start
--Kill all user connection in case open for any database
  DECLARE @Spid INT
DECLARE KillProcessCur CURSOR FOR
 SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID(@DBName) 
OPEN KillProcessCur
FETCH Next FROM KillProcessCur INTO @Spid
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL VARCHAR(500)=NULL
      SET @SQL='Kill ' + CAST(@Spid AS VARCHAR(5))
      EXEC (@SQL)
      PRINT 'ProcessID =' + CAST(@Spid AS VARCHAR(5))
            + ' killed successfull'
      FETCH Next FROM KillProcessCur INTO @Spid
  END
CLOSE KillProcessCur
DEALLOCATE KillProcessCur
--Inner Cursor Ends

--Outer Cursor: Rename Database
      DECLARE @SQLDBRename NVARCHAR(MAX)=NULL
      SET @SQLDBRename='ALTER DATABASE ['+@DBName+'] 
	           SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
      SET @SQLDBRename+=' EXEC sp_renamedb ['+@DBName+'],
          ['+@DBName+@DBRENAME+']'
      SET @SQLDBRename+=' ALTER DATABASE ['+@DBName+@DBRENAME+'] 
      SET MULTI_USER WITH ROLLBACK IMMEDIATE'

         Print @SQLDBRename
         EXEC (@SQLDBRename)
      FETCH Next FROM Cur INTO @DBName
  END
CLOSE Cur
DEALLOCATE Cur

--Get list of Databases
Select @@servername as 'Server', name as DBName 
from sys.databases 
where  database_id>4

Sample Output:

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.