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