(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:
- Migrate instance logins keeping the original SID and password .
- Backup databases and transfer to the destination sites
- 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
zabolab 