Tag Archives: t-sql

Migrate all user databases from one server to another

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'

Advertisements
Tagged , ,