Massive Database Migration between SQL Server instances: the complete procedure v.2.0 *UPDATED*

(05/04/2014) Procedure Upgrades :
– Added compresson to reduce bandwith,space necessary and transfer time
– Reduced stat value for very large database
– Added backup type parameter to choose from FULL,FULL_COPYONLY or DIFFERENTIAL backup (for bigger database migration)
– Added Maxtransfersize and Buffercount parameters to improve backup performance (warning – this needs more memory!)
– Corrected a bug to show correct error message of any kind

Introduction :
This is my complete procedure to accomplish this task, completely based on TSQL and metascripts. Nothing more is necessary.

There are many ways to migrate databases (backup-restore, detach-copy-attach, sql database copy tool and so on..). In this procedure we’ll follow the backup-restore way. Why? Simply because is more flexible and require less bandwith and disk space then others. Obviusly every DBA have to choose the correct strategy for every scenario but this way is in my opinon one of the most adaptable for complex instances with many and big databases to transfer.

Those are the macro-steps we’ll follow to reach our goal:

1. Migrate instance logins keeping the original SID and password .
2. Backup databases and transfer to the destination sites
3. Restore database relocating datafiles in the new fs

Migration of logins with original sid and password hash

This step is necessary to transfer logins from the source instance. We want to keep the source user password and the source user sid. The source user sid is necessary to preserve alignment between instance login SIDs and database user SIDs. The correct procedure to complete this task change for different SQL Server version because in different builds are used different ways to store login informations in system catalog.

The complete MS procedures are here:

http://support.microsoft.com/kb/246133/en-us (SQL 2000)

http://support.microsoft.com/kb/918992/en-us (SQL 2005,2008,2012*) *see note at page bottom

Here you can see the complete script for a common scenario (2008):

1) Run this script in the source instance and save the complete output as text (ALT+T before execute in SSMS).

USE [master]
GO

/****** Object: UserDefinedFunction [dbo].[fn_hexadecimal] ****/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_hexadecimal]
(
-- Add the parameters for the function here
@binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
return @charvalue

END
GO

SET NOCOUNT ON
GO
--use MASTER
GO
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
PRINT '-----------------------------------------------------------------------------'
PRINT ''
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the windows logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ''' + [name] + ''')
CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' +
default_database_name + '], DEFAULT_LANGUAGE=[us_english]
GO

'
FROM master.sys.server_principals
where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] not like 'BUILTIN%'
and [NAME] not like 'NT AUTHORITY%'
and [name] not like '%SQLServer%'
GO

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the SQL Logins'
PRINT '-----------------------------------------------------------------------------'
select 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
CREATE LOGIN [' + [name] + ']
WITH PASSWORD=' + [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED,
SID = ' + [master].[dbo].[fn_hexadecimal]([sid]) + ',
DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
ALTER LOGIN [' + [name] + ']
WITH CHECK_EXPIRATION=' +
CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' +
CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO

'
--[name], [sid] , password_hash
from master.sys.sql_logins
where type_desc = 'SQL_LOGIN'
and [name] not in ('sa', 'guest')

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Disable any logins'
PRINT '-----------------------------------------------------------------------------'
SELECT '--ALTER LOGIN [' + [name] + '] DISABLE
GO
'
from master.sys.server_principals
where is_disabled = 1

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Assign groups'
PRINT '-----------------------------------------------------------------------------'
select
'EXEC master..sp_addsrvrolemember @loginame = N''' + l.name + ''', @rolename = N''' + r.name + '''
GO

'
from master.sys.server_role_members rm
join master.sys.server_principals r on r.principal_id = rm.role_principal_id
join master.sys.server_principals l on l.principal_id = rm.member_principal_id
where l.[name] not in ('sa')
AND l.[name] not like 'BUILTIN%'
and l.[NAME] not like 'NT AUTHORITY%'
and l.[name] not like '%SQLServer%'

2) Run the result script in the destination instance

Backup databases and transfer to the destination site

This step is not diffuclt but can be very long and frustrating on big instances with many databases. This is a metascript to generate and run the backup script in a smarter way skipping and logging errors with no halts.

----------------------------------------------------
--CONFIGURE HERE
----------------------------------------------------
-- DESTINATION PATH
----------------------------------------------------
    declare @destpath nvarchar(255) = 'G:backup'
----------------------------------------------------
-- BACKUP TYPE (use FULL_COPYONLY | FULL | DIFF)
----------------------------------------------------
    declare @bck_type nvarchar(25) = 'FULL_COPYONLY'  
----------------------------------------------------
-- DATABASE LIST
----------------------------------------------------
    create table #dbs (dbname nvarchar(255))
    insert into #dbs values('db1')
    insert into #dbs values('db2')
    insert into #dbs values('db3')

--Note: if you want to move ALL database simply insert all db names in #dbs table using:
--  Insert into #dbs select name from master..sysdatabases
--TO backup every user db excluding system database use
--  Insert into #dbs select name from master..sysdatabases where 
--	name not in ('master','model','msdb','tempdb')

----------------------------------------------------

declare @aDB nvarchar(255)
declare @aQry nvarchar(2048)

DECLARE em_cur CURSOR FOR select * from #dbs

OPEN em_cur
FETCH NEXT FROM em_cur INTO @aDB
WHILE @@FETCH_STATUS = 0
BEGIN
   set @aQry = 'BACKUP DATABASE [' + @aDB + '] TO  DISK = ''' + 
                 @destpath + @aDB + '.bak'' WITH  NOFORMAT, NOINIT,  NAME = N''' + @aDB + 
                 '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 3, COMPRESSION'

--ADD buffercount = 64, maxtransfersize = 4194304 to improve performance.
--WARNING!! This require MORE memory and can lead to OUT OF MEMORY errors. Use and raise this parameters ONLY
--when you are sure this will not create any problem to your users (with stopped applications or with less used instances)
   
   if (@bck_type='DIFF')
   BEGIN
		set @aQry = @aQry + ',DIFFERENTIAL'
   END
   ELSE
   BEGIN
		if (@bck_type='FULL_COPYONLY')
		BEGIN
			set @aQry = @aQry + ',COPY_ONLY'
		END		
   END
      
    begin try
        print '-------------'
        print @aQry
        exec (@aQry)
        print '-------------'        
    end try
    begin catch
        print 'ERROR during backup: ' + ERROR_MESSAGE()
    end catch

    FETCH NEXT FROM em_cur INTO @aDB
END

CLOSE em_cur
DEALLOCATE em_cur
drop table #dbs

Restore database relocating datafiles in the new fs

On the source instance use this metascript to generate the restore script. We’ll use the generated script to restore all databases (into the destination instance) relocating datafiles in the new fs.

set nocount on
----------------------------------------------------
--CONFIGURE HERE
----------------------------------------------------
--     DESTINATION PATH
----------------------------------------------------
--backup files position
    declare @restorepath nvarchar(max) = 'G:backup'
--data files destination 
    declare @pathdata nvarchar(max) = 'D:DataPath'
--log files destination   
    declare @pathlog nvarchar(max)  = 'L:LogPath'
----------------------------------------------------
--     DATABASE LIST
----------------------------------------------------
    create table #dbs (dbname nvarchar(255))
    insert into #dbs values('db1')
    insert into #dbs values('db2')
    insert into #dbs values('db3')
--Note: if you want to move ALL database simply insert all db names in #dbs table using:
--Insert into #dbs select name from master..sysdatabases
---------------------------------------------------

declare @aDB nvarchar(255)
declare em_cur CURSOR FOR select * from #dbs

OPEN em_cur
FETCH NEXT FROM em_cur INTO @aDB
WHILE @@FETCH_STATUS = 0
BEGIN
select 'RESTORE DATABASE [' + @aDB + '] from disk=N''' + @restorepath + @aDB + '.bak'' WITH '
union all
select
CASE
WHEN m.type_desc = 'ROWS' THEN 'MOVE N''' + m.name + ''' TO N''' +
@pathdata + RIGHT(physical_name, CHARINDEX('', REVERSE(physical_name))-1) +''','
WHEN m.type_desc = 'LOG'  THEN 'MOVE N''' + m.name + ''' TO N''' +
@pathlog  + RIGHT(physical_name, CHARINDEX('', REVERSE(physical_name))-1) +''','
END
from sys.master_files m
inner join sys.databases d
on (m.database_id = d.database_id)
where d.name=@aDB
union all
select 'NOUNLOAD,  STATS = 10'
print 'GO'

FETCH NEXT FROM em_cur INTO @aDB
END

CLOSE em_cur
DEALLOCATE em_cur
drop table #dbs

Now take the generated script and run it on the destination instance to restore every database in the correct position.

*NOTE: (Update 22-07-2012) In my current tests on SQL 2012 the official MS procedure to export login password hashes export procedure does not function. To export logins with original password hash AND the original SID use the following workaround

select   
convert(varchar(50),'CREATE LOGIN [' + name + '] with password='),CAST(password AS VARBINARY(26)),
'HASHED,SID=',
CAST(sid AS VARBINARY(26))
from sys.syslogins  where password is not null