T-SQL Script Detach, Move & Attach

One of my tasks recently involved moving the physical files from a set of MSSQL databases from one partition to another.

There were a number of databases so I created the following script in order to detach, move and re-attach the databases.

This script was used to move databases on the same box. But I believe with the use of a Linked Server, a mapped drive and a nice OPENQUERY on the linked server you could adapt this script to move this to another server as well. I don't have that itch at the moment, but if it does come up I will make another post with the updated script (otherwise if you make the change please share!)

DECLARE @name VARCHAR(50) -- database name  
DECLARE @nameDB VARCHAR(50) -- database name  
DECLARE @nameLog VARCHAR(50) -- database log name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileNameDB VARCHAR(256) -- filename for backup
DECLARE @fileNameLog VARCHAR(256) -- filename for backup  
DECLARE @destinationPath VARCHAR(256) -- new path for db
DECLARE @dbid bigint -- db id
DECLARE @enableCMDPermanently bit

-- ============USER CONFIGURABLE VARIABLES START=============
SET @path = 'D:\Databases\'   -- Destination Path for all Databases
SET @enableCMDPermanently = 1
-- ============USER CONFIGURABLE VARIABLES FINISH============



-- =====Please do not edit variables below this line=========


-- Enabled CMD from T-SQL Script
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1 -- enable CMD
RECONFIGURE


-- Table variable for name/id key/values
DECLARE @sysdb TABLE
(
 dbname nvarchar(max),
 database_id bigint
)
INSERT INTO @sysdb (dbname,database_id) SELECT [name],[dbid] FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')  
 
-- Table variable for db file details
DECLARE @sysfiles TABLE
(
 dbname nvarchar(max),
 physical_name nvarchar(max),
 database_id bigint,
 type_desc nvarchar(max)
)
INSERT INTO @sysfiles (dbname,physical_name,database_id,type_desc) SELECT [name], [physical_name],[database_id],type_desc FROM sys.master_files

-- Start CURSOR to iterate through database ids
DECLARE db_cursor CURSOR FOR SELECT [database_id] FROM @sysdb
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @dbid   

-- BEGIN LOOP
WHILE @@FETCH_STATUS = 0   
BEGIN
 --Read DB Details & File Locations
 PRINT @dbid
 SET @name = (SELECT [dbname] FROM @sysdb WHERE database_id = @dbid)
 PRINT @name
 SET @nameDB = (SELECT [dbname] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'ROWS')
 SET @fileNameDB = (SELECT [physical_name] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'ROWS')
 PRINT @fileNameDB
 SET @nameLog = (SELECT [dbname] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'LOG')
 SET @fileNameLog = (SELECT [physical_name] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'LOG')

 DECLARE @cmd nvarchar(500) -- temp string for dynamic sql queries
 
 -- Force Disconnect Active Connections to Database
 SET @cmd = 'ALTER DATABASE ' +  quotename(@name) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
 PRINT @cmd
 EXECUTE sp_executesql @cmd

 SET @cmd = 'ALTER DATABASE ' +  quotename(@name) + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'
 PRINT @cmd
 EXECUTE sp_executesql @cmd
 
 -- Point DB to new file location
 SET @cmd = 'ALTER DATABASE '+ quotename(@name) +' MODIFY FILE ( NAME = '+quotename(@nameDB)+', FILENAME = '+quotename(@path + @nameDB  + '.mdf')+ ' )'
 EXECUTE sp_executesql @cmd

 SET @cmd = 'ALTER DATABASE '+ quotename(@name) +' MODIFY FILE ( NAME = '+quotename(@nameLog)+', FILENAME = '+quotename(@path + @nameLog  + '.LDF')+ ' )'
 EXECUTE sp_executesql @cmd

 -- Detach DB
 SET @cmd = 'sp_detach_db ' + quotename(@name)
 PRINT @cmd
 EXECUTE sp_executesql @cmd

 -- Execute move command on shell to move physical file to new directory
 PRINT @fileNameLog
 PRINT 'cp ' + @fileNameDB + ' ' + @path + @nameDB + '.mdf'
 PRINT @path + @nameDB + '.mdf'
 SET @cmd = 'xp_cmdshell ' + quotename('move ' + '"'+@fileNameDB+'"' + ' ' + '"'+@path + @nameDB + '.mdf'+'"')
 EXECUTE sp_executesql @cmd

 -- Execute move command on shell to move physical file to new directory
 PRINT @path + @nameLog + '.LDF'
 PRINT 'cp ' + @fileNameLog + ' ' + @path + @nameLog + '.LDF'
 SET @cmd = 'xp_cmdshell ' + quotename('move ' + '"'+@fileNameLog+'"' + ' ' + '"'+@path + @nameLog + '.LDF'+'"')
 EXECUTE sp_executesql @cmd

 -- Reattach Database to SQL Instance
 PRINT 'sp_attach_db ' + quotename(@name) + ', ' + quotename( @path + @nameDB + '.mdf') + ',' + quotename(@path + @nameLog + '.LDF')
 SET @cmd = 'sp_attach_db ' + quotename(@name) + ', ' + quotename( @path + @nameDB + '.mdf') + ',' + quotename(@path + @nameLog + '.LDF')
 EXECUTE sp_executesql @cmd

    FETCH NEXT FROM db_cursor INTO @dbid   
END   

--Close Cursor
CLOSE db_cursor   
DEALLOCATE db_cursor 


--OPTIONAL
IF @enableCMDPermanently = 0
BEGIN
 EXEC master.dbo.sp_configure 'show advanced options', 1
 RECONFIGURE
 EXEC master.dbo.sp_configure 'xp_cmdshell', 0 --disable CMD
 RECONFIGURE
END