Massive SQL Server Database Moving Using Detach – Attach: The Complete Procedure

This is a complete configurable metascript, prepared to create detach statements, file move statement and re-attach statements for every database in your instance.
You have only to configure the final destination for datafiles and the database list you want to migrate.

The final generated script for every database selected will be something like this (remembar to use ALT-T to use text output mode in Management Studio)

============================================================
  DATABASE: myTestDB
============================================================
 
--  1) DETACH DATABASE
 
USE [master]
GO
ALTER DATABASE [myTestDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'myTestDB', @keepfulltextindexfile=N'false'
GO
 
--  2) DATAFILE OS MOVING
 
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_log.LDF" L:LogPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB.mdf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_2.ndf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_3.ndf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_4.ndf" D:DataPath'
exec xp_cmdshell 'move "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmyTestDB_5.ndf" D:DataPath'
 
--  3) ATTACH DATABASE
 
CREATE DATABASE [myTestDB] ON 
( FILENAME = N'L:LogPathmyTestDB_log.LDF' ),
( FILENAME = N'D:DataPathmyTestDB.mdf' ),
( FILENAME = N'D:DataPathmyTestDB_2.ndf' ),
( FILENAME = N'D:DataPathmyTestDB_3.ndf' ),
( FILENAME = N'D:DataPathmyTestDB_4.ndf' ),
( FILENAME = N'D:DataPathmyTestDB_5.ndf' ) 
FOR ATTACH
GO

This is the complete metascript:

set nocount on
----------------------------------------------------
--CONFIGURE HERE
----------------------------------------------------
--     DESTINATION PATH
----------------------------------------------------
--data files destination 
    declare @pathdata nvarchar(max) 
    set @pathdata = 'D:DataPath'
--log files destination   
    declare @pathlog nvarchar(max) 
    set @pathlog = '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 where name not in ('master','model','msdb','tempdb')
---------------------------------------------------

declare @aDB nvarchar(255)
declare @aFile nvarchar(255)
declare @aType nvarchar(255)
declare @file_n integer
declare @count integer
declare @sep nvarchar(1)

declare db_cur CURSOR FOR select * from #dbs
OPEN db_cur
FETCH NEXT FROM db_cur INTO @aDB
WHILE @@FETCH_STATUS = 0
BEGIN
	print '============================================================'
    print '  DATABASE: ' + @aDB
    print '============================================================'
    print ''
	
	print '--  1) DETACH DATABASE'
	print ''
	print 'USE [master]'
	print 'GO'
	print 'ALTER DATABASE [' + @aDB + '] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE'
	print 'GO'
	print 'EXEC master.dbo.sp_detach_db @dbname = N''' + @aDB +''', @keepfulltextindexfile=N''false'''
	print 'GO'
	print ''
	
	print '--  2) DATAFILE OS MOVING'	
	print ''
		
	declare file_cur CURSOR FOR select type_desc,physical_name from sys.master_files where db_name(database_id) = @aDB order by type_desc,physical_name
	OPEN file_cur
		
    FETCH NEXT FROM file_cur INTO @aType,@aFile
    WHILE @@FETCH_STATUS = 0
    BEGIN
		if (@aType='ROWS')
		BEGIN			
			print 'exec xp_cmdshell ''move "' + @aFile + '" ' + @pathdata + ''''
		END
		ELSE
		BEGIN			
			print 'exec xp_cmdshell ''move "' + @aFile + '" ' + @pathlog + ''''
		END
		set @count=@count+1
	FETCH NEXT FROM file_cur INTO @aType,@aFile
	END
	CLOSE file_cur
	DEALLOCATE file_cur	
	print ''
	print '--  3) ATTACH DATABASE'	
	print ''
	--Create ATTACH Statement
	set @count=1
	set @sep=','	
	PRINT 'CREATE DATABASE [' + @aDB + '] ON '
	declare file_cur CURSOR FOR select type_desc,physical_name from sys.master_files where db_name(database_id) = @aDB order by type_desc,physical_name
	select @file_n=count(*) from sys.master_files where db_name(database_id) = @aDB
	OPEN file_cur
    FETCH NEXT FROM file_cur INTO @aType,@aFile
    WHILE @@FETCH_STATUS = 0
    BEGIN
		if (@count=@file_n)
		BEGIN
			set @sep=' '
		END
		
		if (@aType='ROWS')
		BEGIN
			print '( FILENAME = N''' + @pathdata + RIGHT(@aFile, CHARINDEX('', REVERSE(@aFile))-1) +''' )' + @sep
		END
		ELSE
		BEGIN
			print '( FILENAME = N''' + @pathlog + RIGHT(@aFile, CHARINDEX('', REVERSE(@aFile))-1) +''' )' + @sep
		END
		set @count=@count+1
	FETCH NEXT FROM file_cur INTO @aType,@aFile
	END
	CLOSE file_cur
	DEALLOCATE file_cur
	
	print 'FOR ATTACH'
	print 'GO'
	print ''
FETCH NEXT FROM db_cur INTO @aDB
END
CLOSE db_cur
DEALLOCATE db_cur
drop table #dbs
Pubblicità

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