T-SQL Script Detach, Move, and Attach Database

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

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