19 September,2008 by Tom Collins
Even though I'd prefer to Backup a database and then move , often a third party supplier
will need it as a MDF or LDF , which they then will have a script to reattach .
The main steps are :
Detach DB
Move to designated path
ReAttach DB
2 input parameters: Name of DB on SAN, targeted path
The main issue to consider is permissions on network to actually transport the files .
The 'xp_cmdshell' is used (which I don't often recommend , but it does serve its purpose on this script
--------------------CODE START---------------------------------- DECLARE @DB_NAME NVARCHAR(128); DECLARE @FILE_PATH NVARCHAR(256); SET @DB_NAME = 'MY_DB' SET @FILE_PATH = 'MY_FILE_PATH' --variables DECLARE @SQL_A VARCHAR(300); DECLARE @SQL_B VARCHAR(300); DECLARE @SQL_C VARCHAR(300); DECLARE @SQL_D VARCHAR(300); DECLARE @SQL_E VARCHAR(2000); DECLARE @DATA_FILE VARCHAR(600); DECLARE @LOG_FILE VARCHAR(600); DECLARE @MDF_FILE NVARCHAR(256); DECLARE @LDF_FILE NVARCHAR(256); DECLARE @FILENAME_TBL TABLE(fileType INT, FilePath NVARCHAR(256)); --establish physical path and filename of MDF and LDF files SET @SQL_A = 'SELECT 1,filename from '+@DB_NAME+'.sys.sysfiles WHERE fileid = 1'; SET @SQL_B = 'SELECT 2,filename from '+@DB_NAME+'.sys.sysfiles WHERE fileid = 2'; INSERT INTO @FILENAME_TBL EXEC (@SQL_A); INSERT INTO @FILENAME_TBL EXEC (@SQL_B); SET @MDF_FILE = (SELECT FilePath FROM @FILENAME_TBL WHERE fileType=1); SET @LDF_FILE = (SELECT FilePath FROM @FILENAME_TBL WHERE fileType=2); ---- DETACH DB PRINT 'Detach DB : START'; --set the DB to SINGLE USER SET @SQL_C = 'ALTER DATABASE ' + @DB_NAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'; EXEC (@SQL_C); EXEC sp_detach_db @DB_NAME,'true' PRINT 'Detach DB : FINISH'; --MOVE MDF file PRINT 'MOVE MDF & LDF : START'; DECLARE @dataFile1Cmd VARCHAR(200), @dataFile2Cmd VARCHAR(200) SET @dataFile1Cmd = 'copy "'+@MDF_FILE + '" "' + @FILE_PATH + '"'; SET @dataFile2Cmd = 'copy "'+@LDF_FILE + '" "' + @FILE_PATH + '"'; EXEC master..xp_cmdshell @dataFile1Cmd EXEC master..xp_cmdshell @dataFile2Cmd PRINT 'MOVE MDF & LDF : FINISH'; --REATTACH DB PRINT 'Attach DB : START'; SET @SQL_E = 'EXEC sp_attach_db @dbname = N'''+@DB_NAME +''',@filename1 = N'''+@MDF_FILE+''',@filename2 = N'''+@LDF_FILE+''''; PRINT @SQL_E; EXEC (@SQL_E); SET @SQL_D = 'ALTER DATABASE ['+@DB_NAME+'] SET MULTI_USER WITH ROLLBACK IMMEDIATE'; EXEC (@SQL_D); PRINT 'Attach DB : FINISH'; --------------------CODE END----------------------------------
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |