It is a common DBA task to migrate databases from one server to another. When dealing with multiple user databases, I prefer to use scripts and the backup/restore method. For very large databases, I schedule a full backup and full restore ahead of the migration time. At the zero hour, I disconnect the client application, perform a differential backup, and then perform a differential restore.
Recently, I have been tasked with migrating a dozen or more small databases to a new server. I have built a stored procedure that will receive a varchar parameter as the database name to be restored. It locates the logical names of the data and log files from the .BAK file. It then uses the parameter and logical names to perform a full restore.
The idea is that I can backup data directly from server 1 to server 2. Then, I can remotely call the restore procedure from server 1.
NOTE: This only migrates the user databases. You will still need to migrate the SQL logins and SQL Agent jobs, if necessary.
Install this procedure on server 2. Here is the stored procedure:
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[restoreDBrecovery] @name VARCHAR(25) AS /** Install this stored procedure on the remote server. Create a path on the remote server for storing the backup files. Grant the necessary permissions to the folder. This procedure will be called remotely from the primary database server. This procedure receives a varchar parameter for database name. It locates the backup file and restores with recovery. Gary Gwyn 2/4/14 **/ -- Logical file name extraction: great stuff via stackoverflow: http://stackoverflow.com/questions/7089627/how-can-i-retrieve-the-logical-file-name-of-the-database-from-backup-file DECLARE @Table TABLE ( LogicalName VARCHAR(128) ,[PhysicalName] VARCHAR(128) ,[Type] VARCHAR ,[FileGroupName] VARCHAR(128) ,[Size] VARCHAR(128) ,[MaxSize] VARCHAR(128) ,[FileId] VARCHAR(128) ,[CreateLSN] VARCHAR(128) ,[DropLSN] VARCHAR(128) ,[UniqueId] VARCHAR(128) ,[ReadOnlyLSN] VARCHAR(128) ,[ReadWriteLSN] VARCHAR(128) ,[BackupSizeInBytes] VARCHAR(128) ,[SourceBlockSize] VARCHAR(128) ,[FileGroupId] VARCHAR(128) ,[LogGroupGUID] VARCHAR(128) ,[DifferentialBaseLSN] VARCHAR(128) ,[DifferentialBaseGUID] VARCHAR(128) ,[IsReadOnly] VARCHAR(128) ,[IsPresent] VARCHAR(128) ,[TDEThumbprint] VARCHAR(128) ) DECLARE @LogicalNameData VARCHAR(128) ,@LogicalNameLog VARCHAR(128) INSERT INTO @table EXEC ( ' RESTORE FILELISTONLY FROM DISK = ''M:\Backups\Full\' + @name + '.bak'' ' ) SET @LogicalNameData = ( SELECT LogicalName FROM @Table WHERE Type = 'D' ) SET @LogicalNameLog = ( SELECT LogicalName FROM @Table WHERE Type = 'L' ) DECLARE @command VARCHAR(250); SET @command = 'RESTORE DATABASE ' + @name + ' FROM DISK = ''M:\Backups\Full\' + @name + '.bak'' WITH recovery, move ''' + @LogicalNameData + ''' to ''D:\MSSQL\Data\' + @name + '_Data.mdf'', move ''' + @LogicalNameLog + ''' to ''D:\MSSQL\Log\' + @name + '_Log.ldf''' EXEC (@command);
Here is the code to execute on server 1. This code will perform the backups and remotely call the restore procedure:
USE master; GO /* Use the below code to migrate user databases from SERVER 1 to SERVER 2. This method works great for small databases. You can migrate several small databases from one server to another with minimal application downtime. Disconnect your end user applications, then execute the below script. It backs up databases from server 1 to a share on server 2. At the end of the script, it makes a remote call to a stored procedure which resides on server 2. That stored procedure remotely restores the data with recovery. !!! The only necessary prep work is to install the stored procedure on server 2 and ensure that permissions are configured properly for the backup destination. You will also need to create a linked server on server 1 to access server 2 remotely. For large databases, it would be best to schedule a full backup and full restore with NORECOVERY. When ready for the migration, you then disconnect the app, perform a differential backup, then perform a differential restore with recovery. Gary Gwyn 2/4/14 ************/ -- Backup the databases EXEC sp_msforeachdb ' if (''?'' not in(''master'', ''msdb'', ''tempdb'', ''model'')) begin BACKUP DATABASE [?] TO DISK = N''\\SERVER2\BACKUPS\Full\?.bak'' WITH COMPRESSION, INIT, NAME = ''? - Full'' end' -- Restore the databases EXEC sp_msforeachdb ' if (''?'' not in(''master'', ''msdb'', ''tempdb'', ''model'')) begin exec [SERVER2].[master].[dbo].[restoredbrecovery] @name = ''?'' end'