How to Migrate SQL Server Database Fast and Completely

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

SQL Database MigrationAs 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

SQL Server DatabaseIn 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

Your email address will not be published. Required fields are marked *