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