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