One of our clients uses MSSQL for their primary database and has more than 150 databases, where theykeep their clients data. With purchasing new server, those databases needed to be migrated from the old server to the new one. To do it manually this can be some tedious task, so writing a script to do it for you automatically is a logical choice.

First - lets divide a problem into two parts. In the first part, we'll create automated backup of all the databases to chosen folder on the disk. The backup will create bak files, which will later be used for restoration of databases on the new server. Of course, as we don't have infinite disk, we'll also compress the bak files with 7z to minimize used space.

As we will use some commands in scripts, that are not enabled in MSSQL server ba default it is time that we enable them now.

Part 1 - enable MSSQL commands

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

Part 2 - creating backup files

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileName7z VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @7z_cmd VARCHAR(256) 
DECLARE @delete_cmd VARCHAR(256) 
DECLARE @mailsend_cmd VARCHAR(256) 
DECLARE @delete_old_cmd VARCHAR(256) 

SET @path = 'E:\Backup\ '
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
     SET @delete_old_cmd = 'forfiles.exe /p E:\Backup /m *.* /d -60 /c "cmd /c del @file"' 
     EXEC master..xp_CMDShell @delete_old_cmd

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name 
WHILE @@FETCH_STATUS = 0 
BEGIN
     SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
     SET @fileName7z = @path + @name + '_' + @fileDate + '.7Z'
     SET @fileName = REPLACE ( @fileName , '&' , '') 
     SET @fileName7z = REPLACE ( @fileName7z , '&' , '')
     SET @7z_cmd = '7z a -t7z -mx9 ' + @fileName7z + ' ' + @fileName 
     SET @delete_cmd= 'del ' + @fileName

     BACKUP DATABASE @name TO DISK = @fileName
     EXEC master..xp_CMDShell @7z_cmd
     EXEC master..xp_CMDShell @delete_cmd

     FETCH NEXT FROM db_cursor INTO @name 
END
CLOSE db_cursor 
DEALLOCATE db_cursor 

SET @mailsend_cmd = 'mailsend -f [email protected] -d company.com -smtp smtpserver -sub "Backup for a day ' + @fileDate + '" -M "Backup was created successfully" -t joh.do[email protected] +cc +bc'
EXEC master..xp_CMDShell @mailsend_cmd

The upper script can easily ba used as a backup script for your daily backup process. To do so you just need to put it into backup.sql file and store it somewhere in the file system. Then you can trigger it like this:

"C:\Program Files\Microsoft SQL Server\90\Tools\binn\SQLCMD.EXE" -E -S "sqlsrv\instance" -i "C:\system\backup.sql"

Upper comman will direct the server to create backup files and 7z them in the E:\Backup directory. Of course backup files will be equiped with a date of backup. On the end, we'll ge an email, that the backup was completed.

After creation of backup files, we can copy those to the new server.

Part 2 - Restoration of backup files

In the next step you have to obtain logical names for your mdf and ldf files. You can do that with following command:

RESTORE FILELISTONLY FROM DISK = 'D:\Backup\bak\BackupFile.bak'
GO 

Next step is to put those names into following script:

-- Restore Databases from bak files
DECLARE
     @dataDir VARCHAR(200),
     @7zFile VARCHAR(200),
     @backupDir VARCHAR(200)

     SET @dataDir = 'c:\Data'
     SET @backupDir = 'd:\Backup'

-- Create temporary table with files from backup directory
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;
CREATE TABLE #DirectoryTree (
     id int IDENTITY(1,1),
     subdirectory nvarchar(512),
     depth int,
     isfile bit
);

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @backupDir,0,1

DECLARE db_cursor CURSOR FOR
SELECT subdirectory FROM #DirectoryTree WHERE isfile = 1 AND RIGHT(subdirectory,3) = '.7Z' ORDER BY id

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @7zFile
WHILE @@FETCH_STATUS = 0
BEGIN
     PRINT @7zFile
     DECLARE
           @bakFile VARCHAR(200),
           @dbName VARCHAR(200),
           @7z_cmd VARCHAR(200),
           @mdfFile VARCHAR(200),
           @ldfFile VARCHAR(200)

     SET @bakFile = @backupDir + '\bak\' + LEFT(@7zFile, LEN(@7zFile)-3) + '.BAK'
     SET @dbName = LEFT(@7zFile, LEN(@7zFile)-12)
     SET @mdfFile = @dataDir + '\' + @dbName + '.mdf'
     SET @ldfFile = @dataDir + '\' + @dbName + '.ldf'

     PRINT @mdfFile
     PRINT @ldfFile

     SET @7z_cmd = '"C:\Program Files\7-Zip\7z.exe" x ' + @backupDir + '\' + @7zFile + ' -o' + @backupDir + '\bak'
     EXEC master..xp_CMDShell @7z_cmd

     -- When decompressed we have to create database
     RESTORE DATABASE @dbName FROM DISK = @bakFile 
          WITH MOVE 'MDFLogicalName' TO @mdfFile, 
          MOVE 'LDFLogicalName TO @ldfFile
     FETCH NEXT FROM db_cursor INTO @7zFile
END
CLOSE db_cursor
DEALLOCATE db_cursor 

Run the script.

That's it. All your backup files have been restoed into the new server. MSSQL automatically process the database to upgrade it to the new version.

Your're done!