T-SQL Script Detach, Move, and Attach Database

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!)

 

001.DECLARE @name VARCHAR(50) -- database name    
002.DECLARE @nameDB VARCHAR(50) -- database name    
003.DECLARE @nameLog VARCHAR(50) -- database log name    
004.DECLARE @path VARCHAR(256) -- path for backup files    
005.DECLARE @fileNameDB VARCHAR(256) -- filename for backup  
006.DECLARE @fileNameLog VARCHAR(256) -- filename for backup    
007.DECLARE @destinationPath VARCHAR(256) -- new path for db  
008.DECLARE @dbid bigint -- db id  
009.DECLARE @enableCMDPermanently bit 
010.-- ============USER CONFIGURABLE VARIABLES START=============  
011.SET @path = 'D:\Databases\'   -- Destination Path for all Databases  
012.SET @enableCMDPermanently = 1  
013.-- ============USER CONFIGURABLE VARIABLES FINISH============  
014.  
015.-- =====Please do not edit variables below this line=========  
016.  
017.-- Enabled CMD from T-SQL Script  
018.EXEC master.dbo.sp_configure 'show advanced options', 1  
019.RECONFIGURE  
020.EXEC master.dbo.sp_configure 'xp_cmdshell', 1 -- enable CMD  
021.RECONFIGURE  
022.  
023.-- Table variable for name/id key/values  
024.  
025.DECLARE @sysdb TABLE  
026.(  
027.    dbname nvarchar(max),  
028.    database_id bigint  
029.)  
030.  
031.INSERT INTO @sysdb (dbname,database_id) SELECT [name],[dbid] FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')    
032.  
033.-- Table variable for db file details  
034.DECLARE @sysfiles TABLE  
035.(  
036.    dbname nvarchar(max),  
037.    physical_name nvarchar(max),  
038.    database_id bigint,  
039.    type_desc nvarchar(max)  
040.)  
041.  
042.INSERT INTO @sysfiles (dbname,physical_name,database_id,type_desc) SELECT [name], [physical_name],[database_id],type_desc FROM sys.master_files  
043.  
044.-- Start CURSOR to iterate through database ids  
045.DECLARE db_cursor CURSOR FOR SELECT [database_id] FROM @sysdb  
046.OPEN db_cursor     
047.FETCH NEXT FROM db_cursor INTO @dbid     
048.  
049.-- BEGIN LOOP  
050.  
051.WHILE @@FETCH_STATUS = 0     
052.BEGIN  
053.    --Read DB Details & File Locations  
054.    PRINT @dbid  
055.    SET @name = (SELECT [dbname] FROM @sysdb WHERE database_id = @dbid)  
056.    PRINT @name  
057.    SET @nameDB = (SELECT [dbname] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'ROWS')  
058.    SET @fileNameDB = (SELECT [physical_name] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'ROWS')  
059.    PRINT @fileNameDB  
060.    SET @nameLog = (SELECT [dbname] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'LOG')  
061.    SET @fileNameLog = (SELECT [physical_name] FROM @sysfiles WHERE [database_id] = @dbid AND [type_desc] = 'LOG')  
062.  
063.    DECLARE @cmd nvarchar(500) -- temp string for dynamic sql queries  
064.  
065.    -- Force Disconnect Active Connections to Database  
066.    SET @cmd = 'ALTER DATABASE ' +  quotename(@name) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'  
067.  
068.    PRINT @cmd  
069.  
070.    EXECUTE sp_executesql @cmd  
071.  
072.    SET @cmd = 'ALTER DATABASE ' +  quotename(@name) + ' SET OFFLINE WITH ROLLBACK IMMEDIATE'  
073.  
074.    PRINT @cmd  
075.  
076.    EXECUTE sp_executesql @cmd  
077.  
078.    -- Point DB to new file location  
079.  
080.    SET @cmd = 'ALTER DATABASE '+ quotename(@name) +' MODIFY FILE ( NAME = '+quotename(@nameDB)+', FILENAME = '+quotename(@path + @nameDB  + '.mdf')+ ' )'  
081.  
082.    EXECUTE sp_executesql @cmd  
083.  
084.    SET @cmd = 'ALTER DATABASE '+ quotename(@name) +' MODIFY FILE ( NAME = '+quotename(@nameLog)+', FILENAME = '+quotename(@path + @nameLog  + '.LDF')+ ' )'  
085.  
086.    EXECUTE sp_executesql @cmd  
087.  
088.    -- Detach DB  
089.  
090.    SET @cmd = 'sp_detach_db ' + quotename(@name)  
091.  
092.    PRINT @cmd  
093.  
094.    EXECUTE sp_executesql @cmd  
095.  
096.  
097.    -- Execute move command on shell to move physical file to new directory  
098.  
099.    PRINT @fileNameLog  
100.  
101.    PRINT 'cp ' + @fileNameDB + ' ' + @path + @nameDB + '.mdf'  
102.  
103.    PRINT @path + @nameDB + '.mdf'  
104.  
105.    SET @cmd = 'xp_cmdshell ' + quotename('move ' + '"'+@fileNameDB+'"' + ' ' + '"'+@path + @nameDB + '.mdf'+'"')  
106.  
107.    EXECUTE sp_executesql @cmd  
108.  
109.  
110.    -- Execute move command on shell to move physical file to new directory  
111.  
112.    PRINT @path + @nameLog + '.LDF'  
113.  
114.    PRINT 'cp ' + @fileNameLog + ' ' + @path + @nameLog + '.LDF'  
115.  
116.    SET @cmd = 'xp_cmdshell ' + quotename('move ' + '"'+@fileNameLog+'"' + ' ' + '"'+@path + @nameLog + '.LDF'+'"')  
117.  
118.    EXECUTE sp_executesql @cmd  
119.  
120.     
121.    -- Reattach Database to SQL Instance  
122.  
123.    PRINT 'sp_attach_db ' + quotename(@name) + ', ' + quotename( @path + @nameDB + '.mdf') + ',' + quotename(@path + @nameLog + '.LDF')  
124.  
125.    SET @cmd = 'sp_attach_db ' + quotename(@name) + ', ' + quotename( @path + @nameDB + '.mdf') + ',' + quotename(@path + @nameLog + '.LDF')  
126.  
127.    EXECUTE sp_executesql @cmd  
128.  
129.     
130.  
131.    FETCH NEXT FROM db_cursor INTO @dbid     
132.  
133.END     
134.  
135.     
136.  
137.--Close Cursor  
138.  
139.CLOSE db_cursor     
140.DEALLOCATE db_cursor   
141.  
142.  
143.--OPTIONAL  
144.  
145.IF @enableCMDPermanently = 0  
146.  
147.BEGIN  
148.  
149.    EXEC master.dbo.sp_configure 'show advanced options', 1  
150.  
151.    RECONFIGURE  
152.  
153.    EXEC master.dbo.sp_configure 'xp_cmdshell', 0 --disable CMD  
154.  
155.    RECONFIGURE  
156.  
157.END 
Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 1 (1 items)