Microsoft SQL Server Database Migration

Hi My followers As we are database administrator (DBA) and DB Consultant we should now How we can Migrate the databases from the production server to another server without downtime  we have two cases we will need the migration on it.

• SQL Server Upgrade :

As we see Microsoft is going for more enhancement in SQL Server like SQL Server 2014 and now SQL Server 2016 so if you need to upgrade your Database production you have two option :

• Upgrade your production in the same site (No need to migration)
• build new Environment then migrate the database from the old server to the new server.(Need SQL Server Migration)

• SQL Server Consolidation : 

Here in this case if you have some SQL server Instances and you need to consolidate all of them in one or Two instance with high specification.

Database Migration Steps :

• Create Full backup device for all databases will be Migrated and make the backup device URL on the Destination server (B) \\Server_B so by this Way you will take the Full backup direct to the Target server.

Select ‘USE [master]
GO
EXEC master.dbo.sp_addumpdevice @devtype = N”disk”,
@logicalname = N”’+name+’_tape”, @physicalname
= N”K:\BackupDB\P-S-SQLCLSMSSQL2008backup\’+name+’_tape.bak”
GO
‘from Sys.Databases where state_desc =’online’
and database_id >4

• Create backup Stored procured in MSDB DB Exists in Source Server

USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[DMV_BackupAll] Script Date: 3/16/2015 7:17:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [msdb]
GO

/****** Object: UserDefinedTableType [dbo].[Exceptionlist] Script Date: 17/09/2013 02:36:13 PM ******/
CREATE TYPE [dbo].[Exceptionlist] AS TABLE(
[list] [int] NULL
)
GO

Create procedure [dbo].[DMV_BackupAll] (@start_DB_ID int, @END_DB_ID int,@TapeBackupLocation varchar(500),@TranBackupLocation varchar(500)
,@FULLBackup INT = 0 , @DiffBackup INT =0 , @TXNLOG INT = 0 ,@TXLLOGType INT = 0
,@Job_Name Nvarchar(50) = ‘No Job Name’
,@Exceptionlist_DBs Exceptionlist readonly
,@p_recipients nvarchar (1000)

)
as
begin
set nocount on
— Multiple Recipients can be added to @p_recipients by using semicolon
Declare @recovery_model int
Declare @TXLLOGTypeName varchar (20)
DECLARE @name NVARCHAR(500)
— database name
DECLARE @sql NVARCHAR(MAX) = N”
— sql statment
DECLARE @p_body AS NVARCHAR(MAX) , @p_subject AS NVARCHAR(MAX), @p_profile_name AS NVARCHAR(MAX)
SET @p_subject = N’Backup failed on Job Name”’ + @Job_Name + ”’ On Server ‘ + ( CAST((SELECT SERVERPROPERTY(‘ServerName’)) AS nvarchar))
DECLARE @msg VARCHAR(max)
Declare @patherror varchar(50)
————————————————————————————————————–

—-Path for @TapeBackupLocation
DECLARE @FileName varchar(255)
DECLARE @IsExist Int
Declare @Temp Table
(File_Exist int , Directory int , Parent int)
SELECT @FileName= @TapeBackupLocation
Insert Into @Temp
EXEC Master.dbo.xp_fileexist @filename
Set @IsExist = (Select Directory from @Temp)

—- Check Path @TranBackupLocation
DECLARE @FileName2 varchar(255)
DECLARE @IsExist2 Int
Declare @Temp2 Table
(File_Exist int , Directory int , Parent int)
SELECT @FileName2= @TranBackupLocation
Insert Into @Temp2
EXEC Master.dbo.xp_fileexist @FileName2
Set @IsExist2 = (Select Directory from @Temp2)

—-Send Error massage
if (isnull(@IsExist,0) <> 1 ) OR @IsExist2 <> 1
begin
set @patherror=’ Because This Path ”’ + @TapeBackupLocation + ”’ or this Path ”’ + @TranBackupLocation + ”’ Is not Correct’
SET @p_body = ‘Database Backup Will not Complete on the server </br></br>’ +
+ ‘Server : <b>’ + ( CAST((SELECT SERVERPROPERTY(‘ServerName’)) AS nvarchar))+ ‘</b></br>’ +
‘ and On the Job Name ”’+ isnull(@Job_Name,”) +”’ ‘ + ‘</b></br>’ +
‘and the corrupted database: <b>’ + isnull(@name,”) +'</b></br></br>’ + isnull(@patherror,”)
PRINT @p_body
—Send massage

EXEC msdb.dbo.sp_send_dbmail
@recipients = @p_recipients,
@body = @p_body ,
@body_format = ‘HTML’,
@subject = @p_subject
GOTO GOOUT
end
——————————————————————————————————————-
——————————————————————————————————————-
DECLARE @h_body as nvarchar(max)
Declare @BackupType as varchar(50)
Create table #JobFailed
(Dbname sysname,
[Errormessage] varchar(300),
BackupType varchar(30))
————————————————————-

DECLARE db_cursor CURSOR fast_forward read_only forward_only FOR SELECT name FROM sys.databases
WHERE database_id >= @start_DB_ID and database_id<=@END_DB_ID and state_desc = ‘ONLINE’
and database_id not in (select * from @Exceptionlist_DBs)

declare @count int
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRY
DECLARE @Return INT
set @sql = N”
—————————————————
— Check Full OR Deffrential Backup Device
IF (@FULLBackup = 1 or @DiffBackup = 1)
begin
set @count = 0
select @count= count(*) from sys.backup_devices
where lower(name) = lower(@name) + lower(‘_Tape’)

if @count = 0
begin

set @sql = @sql + N’

USE [master]
EXEC master.dbo.sp_addumpdevice @devtype = N”disk”, @logicalname = N”’ + @name + ‘_Tape”, @physicalname = N”’ + @TapeBackupLocation + @name + ‘_tape.bak”

‘
end
end
–Full Backup————————————————————————————
IF (@FULLBackup = 1)
BEGIN
set @BackupType=’Full Backup’
set @sql = @sql + N’

BACKUP DATABASE [‘ + @name + ‘] TO [‘ + @name + ‘_Tape] WITH NOFORMAT, INIT, NAME = N”’ + @name + ‘-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10
‘
END
————————————————————————————————–
–Diffrential Backup————————————————————————————
IF (@DiffBackup = 1)
BEGIN
set @BackupType=’Differentical Backup’

set @sql = @sql + N’

BACKUP DATABASE [‘ + @name + ‘] TO [‘ + @name + ‘_Tape] WITH DIFFERENTIAL , NOFORMAT, INIT, NAME = N”’ + @name + ‘-Differential Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10

‘

END
————————————————————————————————–
–TXN Log overwirte——————————————————————————-

IF (@TXNLOG = 1 AND @TXLLOGType >=0 AND @TXLLOGType <2)
BEGIN
set @BackupType=’Transactional Backup’
SELECT @recovery_model= recovery_model FROM sys.databases WHERE name = @name
IF(@recovery_model in (1,2))
BEGIN

IF @TXLLOGType = 1
SET @TXLLOGTypeName = ‘INIT’
else
SET @TXLLOGTypeName = ‘NOINIT’

set @count = 0
set @recovery_model = 1
select @count= count(*) from sys.backup_devices
where lower(name) = lower(@name) –+ lower(‘_TapeDiff’)

if (@count > 0 )
begin
set @sql = @sql + N’

BACKUP LOG [‘ + @name + ‘] TO [‘ + @name + ‘] WITH NOFORMAT, ‘ + @TXLLOGTypeName + ‘, NAME = N”’ + @name + ‘-Transaction Log Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10

‘
end
else
begin

set @sql = @sql + N’

USE [master]
EXEC master.dbo.sp_addumpdevice @devtype = N”disk”, @logicalname = N”’ + @name + ”’, @physicalname = N”’ + @TranBackupLocation + @name + ”’

BACKUP LOG [‘ + @name + ‘] TO [‘ + @name + ‘] WITH NOFORMAT, ‘ + @TXLLOGTypeName + ‘, NAME = N”’ + @name + ‘-Transaction Log Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10
‘
end
END
END– Recovery model
————————————————————————————————–
SET @msg = ‘Database Backup failed for the database ‘ + @name
print @sql
EXEC @Return = sp_executesql @sql
END TRY
BEGIN CATCH

if @return > 0
PRINT @return
begin
raiserror (@msg,16,1) with log
SET @msg = ( SELECT ERROR_MESSAGE())
PRINT @Msg
SET @p_body = ‘Database Backup failed on the server </br></br>’ +
+ ‘Server : <b>’ + ( CAST((SELECT SERVERPROPERTY(‘ServerName’)) AS nvarchar))+ ‘</b></br>’ +
‘ and On the Job Name ”’+ isnull(@Job_Name,”) +”’ ‘ + ‘</b></br>’ +
‘and the corrupted database: <b>’ + isnull(@name,”) +'</b></br></br>’ + isnull(@msg,”)
PRINT @p_body
–Haja Failure data into temp table
Insert into #JobFailed(Dbname,Errormessage,BackupType)
select @name,@p_body,@BackupType
—-print @p_body
end
END CATCH
WAITFOR DELAY ‘000:00:15’
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

–Newly created by Haja for generate the Error message into HTML table
set @h_body = cast( (
select td = Dbname + ‘</td><td>’ + cast( Errormessage as varchar(max) ) + ‘</td><td>’ + cast( BackupType as varchar(max) )
from (
select Dbname,
Errormessage,
BackupType

from #JobFailed
) as d
for xml path( ‘tr’ ), type ) as varchar(max) )

set @H_body = ‘<table cellpadding=”2″ cellspacing=”2″ border=”1″>’
+ ‘<tr><th>Database </th><th>Error Message</th><th>BackupType</th></tr>’
+ replace( replace( @H_body, ‘<‘, ‘<‘ ), ‘>’, ‘>’ )
+ ‘</table>’

DECLARE @COUNT1 INT = (SELECT COUNT (*) FROM #JobFailed )
IF isnull(@COUNT1,0) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = @p_recipients, @body = @H_body ,
@body_format = ‘HTML’, @subject = @p_subject
END
DROP TABLE #JobFailed
GOOUT:
end

• Put migrated database in Source Server in Read_only mode.

Select ‘SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
ALTER DATABASE [‘+name+’] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [‘+name+’] SET Read WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [‘+name+’]SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
‘from Sys.Databases where Database_Id > 4 and state_desc = ‘ONLINE’ and name <> ‘distribution’
And is_read_only = 0

• Take Full backup from Source server (backup will take with option read_only mode)

USE [msdb]
GO

DECLARE @return_value int
declare @Exceptionlist_value Exceptionlist
insert into @Exceptionlist_value select 2
EXEC @return_value = [dbo].[DMV_BackupAll]
@start_DB_ID = 1,
@END_DB_ID = 200,
@TapeBackupLocation = N’H:\bkp\’,
@TranBackupLocation = N’H:\bkp\’,
@FULLBackup = 1,
@TXNLOG = 0,
@TXLLOGType = 1,
@Exceptionlist_DBs=@Exceptionlist_value,
@job_name=’NOJob’,
@p_recipients = ‘dbConsultant@MostafaElmasry.con’

SELECT ‘Return Value’ = @return_value

GO

• Restore Full backup on the Target server with No Recovery to be in the restore mode.(here the script will give you the default restore script so you should check the database file (MDF,NDF,LDF) name and update the Script manual)

Select Name , Replace(Name,’_Tape’,”) AS Database_Name into #backupdevice FROM sys.backup_devices
where name like ‘%_Tape’
and Name not in (‘master_Tape’,’model_Tape’,’msdb_Tape’,’tempdb_Tape’)
GO
—–Restore FULL backup With NoRecovery Option
Select ‘
RESTORE DATABASE [‘+Database_Name+’] FROM [‘+Name+’] WITH FILE = 1,
MOVE ”’+Database_Name+”’ TO N”D:\UserDatabase\’+Database_Name+’_Data.MDF”,
MOVE ”’+Database_Name+’_log” TO N”I:\APP_LOG\’+Database_Name+’_log.lDF”,
NORECOVERY, NOUNLOAD, STATS = 5
GO
‘
from #backupdevice

To Check the Database Name File to update the Output of the Restore Script use the below Script

SELECT D.Name AS Database_Name,M.name, M.physical_name AS current_file_location
FROM sys.master_files AS M
inner join SYS.Databases As D
on D.database_id = M.database_id
where D.database_id > 4

• Take DIFF backup from Source Database

USE [msdb]
GO
DECLARE @return_value int
declare @Exceptionlist_value Exceptionlist
insert into @Exceptionlist_value select 2
EXEC @return_value = [dbo].[DMV_BackupAll]
@start_DB_ID = 1,
@END_DB_ID = 200,
@TapeBackupLocation = N’H:\bkp\’,
@TranBackupLocation = N’H:\bkp\’,
@FULLBackup = 0,
@DiffBackup = 1,
@TXNLOG = 0,
@TXLLOGType = 1,
@Exceptionlist_DBs=@Exceptionlist_value,
@job_name=’NOJob’,
@p_recipients = ‘DBConsultant@MostafaElmasry.con’
SELECT ‘Return Value’ = @return_value

GO

• Restore DIFF backup on target server with Recovery option to return back the database to the online mode. .(here the script will give you the default restore script so you should check the database file (MDF,NDF,LDF) name and update the Script manual)

Select Name , Replace(Name,’_Tape’,”) AS Database_Name into #backupdevice FROM sys.backup_devices
where name like ‘%_Tape’
and Name not in (‘master_Tape’,’model_Tape’,’msdb_Tape’,’tempdb_Tape’)
GO
—-Resore DIFF backup with Recovery Option And With Overwrite
Select ‘
RESTORE DATABASE [‘+Database_Name+’] FROM [‘+Name+’] WITH FILE = 2,
MOVE ”’+Database_Name+”’ TO N”D:\UserDatabase\’+Database_Name+’_Data.MDF”,
MOVE ”’+Database_Name+’_log” TO N”I:\APP_LOG\’+Database_Name+’_log.lDF”,
RECOVERY,Replace, NOUNLOAD, STATS = 5
GO
‘
from #backupdevice
Drop Table #backupdevice

• Return the database in target server to the read_Write mode

Select ‘SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
ALTER DATABASE [‘+name+’] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [‘+name+’] SET READ_WRITE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [‘+name+’]SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
‘from Sys.Databases where Database_Id > 4 and state_desc = ‘ONLINE’ and name <> ‘distribution’
And is_read_only = 1

• Check Database Configuration on the Source sever and apply it on Target Server like (RCSI , Service broker , DB Compatibility)

select name,
‘
EXEC sp_resetstatus ”’ + name + ”’
GO
ALTER DATABASE ‘ + name + ‘ SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
ALTER DATABASE ‘ + name + ‘ SET READ_COMMITTED_SNAPSHOT ON;
go
ALTER DATABASE ‘ + name + ‘ SET MULTI_USER WITH ROLLBACK IMMEDIATE
go
‘
as ‘Run on Destination Server’
from sys.databases
where is_read_committed_snapshot_on = 1
and database_id>4 and name <>’distribution’

GO

Select name,
‘
USE master ;
GO
ALTER DATABASE [‘ + name + ‘] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE ‘ + name + ‘ SET ENABLE_BROKER ;
GO
ALTER DATABASE [‘ + name + ‘] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
‘
from sys.databases where is_broker_enabled = 1
and database_id>4 and name <>’distribution’

• Move the users from source server to the target server and be sure everything is ok on Server level and database level

For moving the users it is big subject i will share with you in the next post How we can move it by multiple ways

View all my tips , LinkedIn , Slideshare ,Youtube Channel.My posts in MSDN