If you are migrating several databases from one server to another server, do not miss to use this script. It will help you to complete the migration faster.
Database migration
As the name implies, major part of database migration involves moving databases from one SQL server to another SQL server. Backup of target databases from old SQL server can be used to restore them again on the new SQL server or you can detach databases, copy MDF and LDF files to the target server and attach them.
Points to remember:
When you are using the latter method, place all mdf and ldf files in a single folder, update the below script with the proper folder paths and run it. Now you can sit back and relax. Databases will now appear on your new server.
DECLARE @Thisfolder SYSNAME = '<PATH OF MDF and LDF Files>' -- please make sure the path ends with "\" DECLARE @mdfpath VARCHAR(500) ,@ldfpath VARCHAR(500) SET @mdfpath = '<MDF FILE PATH ON YOUR NEW SERVER>' -- please make sure the path ends with "\" SET @ldfpath = '<LDF FILE PATH ON YOUR NEW SERVER>' -- please make sure the path ends with "\" IF OBJECT_ID('tempdb..#Thistable') IS NOT NULL DROP TABLE #Thistable CREATE TABLE #Thistable ( cId INT identity(1, 1) ,csdir NVARCHAR(255) ,cdpth SMALLINT ,cflg BIT ,cpd INT ) INSERT INTO #Thistable ( csdir ,cdpth ,cflg ) EXEC master..xp_dirtree @Thisfolder ,10 ,1 UPDATE #Thistable SET cpd = ( SELECT MAX(CId) FROM #Thistable d2 WHERE cdpth = d.cdpth - 1 AND d2.CId < d.CId ) FROM #Thistable d DECLARE @nID INT ,@nFile VARCHAR(MAX) ,@cdpth TINYINT ,@cflg BIT ,@cpd INT ,@nwkSubcpd INT ,@nwkcsdir VARCHAR(MAX) DECLARE @nFiles TABLE (FPATH VARCHAR(MAX)) DECLARE Fucrosr CURSOR LOCAL FORWARD_ONLY FOR SELECT * FROM #Thistable WHERE cflg = 1 OPEN Fucrosr FETCH NEXT FROM Fucrosr INTO @nID ,@nFile ,@cdpth ,@cflg ,@cpd SET @nwkSubcpd = @cpd WHILE @@FETCH_STATUS = 0 BEGIN WHILE @nwkSubcpd IS NOT NULL BEGIN SELECT @nwkcsdir = csdir ,@nwkSubcpd = cpd FROM #Thistable WHERE cId = @nwkSubcpd SELECT @nFile = @nwkcsdir + '\' + @nFile END SELECT @nFile = @Thisfolder + @nFile INSERT INTO @nFiles (FPATH) VALUES (@nFile) FETCH NEXT FROM Fucrosr INTO @nID ,@nFile ,@cdpth ,@cflg ,@cpd SET @nwkSubcpd = @cpd END CLOSE Fucrosr DEALLOCATE Fucrosr DECLARE @finalscript VARCHAR(8000) SET @finalscript = '' DECLARE cur1 CURSOR FOR SELECT * FROM @nFiles WHERE FPATH LIKE '%.mdf' DECLARE @file VARCHAR(1000) DECLARE @script VARCHAR(1000) OPEN cur1 FETCH NEXT FROM cur1 INTO @file WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #T1 ( cprop SQL_VARIANT ,cvalue SQL_VARIANT ) SET @script = 'dbcc checkprimaryfile (''' + @file + ''', 2)' INSERT #T1 ( cprop ,cvalue ) EXEC (@script) DECLARE @ourdb SQL_VARIANT DECLARE @ourdbtext VARCHAR(200) SET @ourdb = ( SELECT TOP 1 cvalue FROM #T1 ) SET @ourdbtext = cast(@ourdb AS VARCHAR) DROP TABLE #T1 SET @finalscript = @finalscript + 'CREATE DATABASE [' + @ourdbtext + '] ON' + CHAR(13) CREATE TABLE #T2 ( c1 INTEGER ,c2 INTEGER ,c3 VARCHAR(100) ,c4 VARCHAR(1000) ) INSERT INTO #T2 ( c1 ,c2 ,c3 ,c4 ) EXEC ('dbcc checkprimaryfile (''' + @file + ''',3)') UPDATE #T2 SET c4 = rtrim(ltrim(reverse(substring(reverse(c4), 1, charindex('\', reverse(c4), 1) - 1)))) DECLARE cur2 CURSOR FOR SELECT c4 FROM #T2 DECLARE @file2 VARCHAR(1000) OPEN cur2 FETCH NEXT FROM cur2 INTO @file2 WHILE @@FETCH_STATUS = 0 BEGIN IF charindex('.mdf', @file2, 1) <> 0 BEGIN DECLARE @cmd2 VARCHAR(1000) SET @cmd2 = 'MOVE "' + @Thisfolder + @file2 + '" "' + @mdfpath + @file2 + '"' EXEC master..xp_cmdshell @cmd2 ,no_output SET @finalscript = @finalscript + '( FILENAME = ''' + @mdfpath + @file2 + '''),' + CHAR(13) END IF charindex('.ldf', @file2, 1) <> 0 BEGIN SET @cmd2 = 'MOVE "' + @Thisfolder + @file2 + '" "' + @ldfpath + @file2 + '"' EXEC master..xp_cmdshell @cmd2 ,no_output SET @finalscript = @finalscript + '( FILENAME = ''' + @ldfpath + @file2 + '''),' + CHAR(13) END IF charindex('.ndf', @file2, 1) <> 0 BEGIN SET @cmd2 = 'MOVE "' + @Thisfolder + @file2 + '" "' + @mdfpath + @file2 + '"' EXEC master..xp_cmdshell @cmd2 ,no_output SET @finalscript = @finalscript + '( FILENAME = ''' + @mdfpath + @file2 + '''),' + CHAR(13) END FETCH NEXT FROM cur2 INTO @file2 END CLOSE cur2 DEALLOCATE cur2 DROP TABLE #T2 SET @finalscript = LEFT(@finalscript, LEN(@finalscript) - 2) + CHAR(13) + 'FOR ATTACH' + CHAR(13) FETCH NEXT FROM cur1 INTO @file END CLOSE cur1 DEALLOCATE cur1 SET @finalscript = 'Use [master]' + CHAR(13) + @finalscript PRINT @finalscript EXEC master..xp_cmdshell @finalscript
In case of any database crashes during such migrations, do not hesitate to use SQL recovery tools like DataNumen SQL Recovery.
Author Introduction:
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook data error and excel recovery software products. For more information visit www.datanumen.com
Leave a Reply