info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools Services
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



SQL Server Automated Database Migration using Backup & Restore Method (v2)
by BF (Principal Consultant; Architecture; Engineering)
2021-10-11








T-SQL: SQL Server Automated Database Migration using Backup & Restore Method (v2)


Process:

- Populate a Migration Driver table. This is used to store the databases to be migrated and also the location for restored MDF and LDF files.

- Each Stored Procedure outputs the commands for Full Backup or Differential Backup or Log Backup or Full Restore or Differential Restore or Log Restore. *Note the parameter for No Recovery or Recovery.

- Ensure all Database and Log Backups are disabled prior to Migration FULL Backups start time. (Technically only unknown Differential & Log Backups cause an issue)

- Ensure all Databases are in FULL Recovery Model prior to FULL Backups start time.

- Typically:
- Take Migration FULL Backups 2 days before
- Take Migration DIFFERENTIAL Backups 12 hours before
- Migration starts and Applications Offline
- Switch Source Databases to Offline
- Switch Source Databases to Read-Only
- Switch Source Databases to Online
- Take Migration LOG Backup.
- Switch Source Databases to Offline
- On the Target issue FULL Restore with No Recovery
- On the Target issue DIFFERENTIAL Restore with No Recovery
- On the Target issue LOG Restore with Recovery.
- On the Target switch Database to Read-Write mode.


Select name, recovery_model_desc, state_desc, is_read_only, user_access_desc from master.sys.databases
where name in (Select dbname from [msdb].[dbo].[driver_nw])

--Set Reovery = FULL
Select 'Alter Database ['+name+'] Set Recovery Full' from master.sys.databases
where database_id > 4
and state_desc = 'Online'
and name in (Select dbname from [msdb].[dbo].[driver_nw])

--Set Offline
Select 'Alter Database ['+name+'] Set Offline With Rollback Immediate' from master.sys.databases
where database_id > 4
and state_desc = 'Online'
and name in (Select dbname from [msdb].[dbo].[driver_nw])

--Set Read_Only
Select 'Alter Database ['+name+'] Set Read_Only' from master.sys.databases
where database_id > 4
and state_desc = 'Online'
and name in (Select dbname from [msdb].[dbo].[driver_nw])

--Set Online
Select 'Alter Database ['+name+'] Set Online' from master.sys.databases
where database_id > 4
and state_desc = 'Online'
and name in (Select dbname from [msdb].[dbo].[driver_nw])

--Set Read_Write
Select 'Alter Database ['+name+'] Set Read_Write' from master.sys.databases
where database_id > 4
and state_desc = 'Online'
and name in (Select dbname from [msdb].[dbo].[driver_nw])





Create a Migration Driver Table:

/****** Object: Table [dbo].[Driver] Script Date: 4/28/2018 7:41:03 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Driver](
[dbname] [varchar](100) NULL,
[DataPath] [nvarchar](500) NULL,
[LogPath] [nvarchar](500) NULL
) ON [PRIMARY]

GO



Automated Backup:


USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[Automated_Backup] Script Date: 10/11/2021 8:07:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter Procedure [dbo].[Automated_Backup]
@type varchar(100), --FULL, DIFFERENTIAL, LOG
@path varchar(1000), --BACKUP LOCATION
@uniquecode varchar(25) = 'AUTOMATED_SQL'

As

/*
1. Automated Backup & Automated Restore requires all backup jobs on the SQL Instance and/or 3rd party backup tools to be stopped + disabled before running it since do not want other backup jobs breaking the backup chain.
2. Do not use COPY_ONLY backups for Automated FULLs or DIFFs and these will not link together when you try the restores. (With a Copy-only backup it doesn't touch the DatabaseBackupLSN and thus sql server cannot link them)
(If use copy_only backups, the diff restore will error out: "This differential backup cannot be restored because the database has not been restored to the correct earlier state."
3. IF you must take ad-hoc backups during this migration process, just use the copy_only option. (It is not necesary as a non-copy-only FULL will not break the log chain, but I rather just use the copy_only regardless to be on the safe side)
4. Add COMPRESSION to the backup command if desired.
5. The Automated Restore will generate its restore code based off the SQL Instance MSDB BACKUPSET table.
6. Run the Automated Restore immediately after the Automated Backup completes to get the restore generated script.
7. Both the Automated Backup & Automated Restore output the scripts to run.
8. Always test the full process in pre-migration mocks
9. Review all scripts before running them in your environment.
*/

Set NoCount On

BEGIN
DECLARE @dbname VARCHAR(50) --Database Name
DECLARE @fileName VARCHAR(256) --File Name for backup
DECLARE @fileDate VARCHAR(20) --Used for Backup File Name
DECLARE @SQL NVARCHAR(MAX)
DECLARE @BkpPath nvarchar(500)

IF (@TYPE='FULL')
BEGIN
--Specify filename format
SELECT @fileDate = CONVERT(varchar(10),GETDATE(), 20) + '-' + replace(convert(varchar(5),getdate(),108),':','')

DECLARE db_cursor CURSOR FOR
select a.dbname from msdb..driver a join master.sys.databases d on a.DBName = d.name

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @BkpPath = ''+@path+'\'
SET @fileName = @BkpPath + @uniquecode + '_BAC' + '_FULL_' + @dbname + '_' + @fileDate + '.BAK'

SET @SQL='BACKUP DATABASE ' + QUOTENAME(@dbname) + ' TO DISK = '+''''+@fileName+'''' +' '

PRINT (@SQL)

FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor
END

IF (@TYPE='DIFF')
BEGIN
--Specify filename format
SELECT @fileDate = CONVERT(varchar(10),GETDATE(), 20) + '-' + replace(convert(varchar(5),getdate(),108),':','')

DECLARE db_cursor CURSOR FOR
Select a.dbname from msdb..driver a join master.sys.databases d on a.DBName = d.name

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @BkpPath = ''+@path+'\'
SET @fileName = @BkpPath + @uniquecode + '_BAC' + '_DIFF_' + @dbname + '_' + @fileDate + '.BAK'

SET @SQL='BACKUP DATABASE ' + QUOTENAME(@dbname) + ' TO DISK = '+''''+@fileName+'''' +' WITH DIFFERENTIAL '

PRINT (@SQL)

FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor
END

IF (@TYPE='LOG')
BEGIN
--Specify filename format
SELECT @fileDate = CONVERT(varchar(10),GETDATE(), 20) + '-' + replace(convert(varchar(5),getdate(),108),':','')

DECLARE db_cursor CURSOR FOR
select a.dbname from msdb..driver a join master.sys.databases d on a.DBName = d.name where d.name not in ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @BkpPath = ''+@path+'\'
SET @fileName = @BkpPath + @uniquecode + '_BAC' + '_TLOG_' + @dbname + '_' + @fileDate + '.TRN'

SET @SQL='BACKUP LOG ' + QUOTENAME(@dbname) + ' TO DISK = '+''''+@fileName+'''' +' '

PRINT (@SQL)

FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

END
END
GO



Automated Restore:


USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[Automated_Restore] Script Date: 10/11/2021 8:35:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[Automated_Restore]
@TYPE NVARCHAR(100), --FULL or DIFFERENTIAL OR LOG
@RECOVER NVARCHAR(3), --'No' or 'Yes'
@BKPATH nvarchar(1000),
@uniquecode varchar(25) = 'AUTOMATED_SQL'

AS
BEGIN
DECLARE @SQL nvarchar(max)
DECLARE @SQL1 nvarchar(max)
DECLARE @name VARCHAR(MAX)
DECLARE @fileid varchar(10)
DECLARE @TEXT NVARCHAR(MAX)
DECLARE @dbname VARCHAR(MAX)
DECLARE @ID INT
DECLARE @SQLString NVARCHAR(MAX)
DECLARE @DataPath nvarchar(500)
DECLARE @LogPath nvarchar(500)

SET NOCOUNT ON
IF (SELECT OBJECT_ID('#RESTORE_SQL')) IS NOT NULL DROP TABLE #RESTORE_SQL
CREATE TABLE #RESTORE_SQL (DBNAME NVARCHAR(500),FILEID NVARCHAR(10),SQL NVARCHAR(2000))

IF(@TYPE='FULL')
BEGIN
DECLARE db_cursor CURSOR FOR

Select sf.fileid,db_name(sf.dbid) as [name],dr.datapath as datapath,dr.logpath as logpath from master..sysaltfiles sf join msdb.dbo.[driver] dr on db_name(sf.dbid)=dr.dbname
where sf.dbid in (select db_id(a.dbname) from msdb.dbo.[driver] a join sys.databases d on a.DBName = d.name)
group by sf.fileid,sf.dbid,dr.datapath,dr.logpath

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @fileid,@name,@datapath,@logpath

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = (SELECT '''MOVE N'''''+name+''''' TO N'''''+ @DataPath +'\' + RIGHT([FILENAME],charindex('\',reverse([FILENAME]),1)-1)+''''','''
from MASTER..sysaltfiles
where db_name(dbid)=@name and fileid=@fileid and groupid <> 0
UNION
SELECT '''MOVE N'''''+name+''''' TO N'''''+ @LogPath +'\' + RIGHT([FILENAME],charindex('\',reverse([FILENAME]),1)-1)+''''','''
from MASTER..sysaltfiles
where db_name(dbid)=@name and fileid=@fileid and groupid = 0)

SET @SQL1 = 'Insert into #RESTORE_SQL values ('''+@name+''','''+@fileid+''','+@SQL+')'

EXEC (@SQL1)

FETCH NEXT FROM db_cursor INTO @fileid,@name,@datapath,@logpath
END
CLOSE db_cursor
DEALLOCATE db_cursor

DECLARE db_cursor CURSOR FOR

SELECT DBNAME FROM #RESTORE_SQL GROUP BY DBNAME

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN

IF (@RECOVER='NO')
BEGIN
SELECT @SQLString = ''
SELECT @SQLString = [SQL] +' ' + @SQLString from #RESTORE_SQL WHERE DBNAME=@dbname ORDER BY FILEID
IF LEN(LTRIM(RTRIM(@SQLString))) <> 0
BEGIN
SELECT @TEXT='USE [master]
GO
RESTORE DATABASE ['+DBNAME+'] FROM DISK = N'''+ @BKPATH+'\'+ RIGHT(BF.physical_device_name, CHARINDEX('\', REVERSE(BF.physical_device_name)) - 1)+''' WITH FILE = 1,
'+@SQLString+'
NOUNLOAD, NORECOVERY, STATS = 5
GO' FROM #RESTORE_SQL RS
INNER JOIN msdb.dbo.backupset BS ON RS.DBNAME = BS.database_name
INNER JOIN MSDB.DBO.backupmediafamily BF ON BS.media_set_id=BF.media_set_id
WHERE DBNAME=@dbname AND BS.backup_set_id in (select max(backup_set_id) from msdb..backupset where type='D' group by database_name)
AND
BF.physical_device_name like '%' + @uniquecode + '%'
GROUP BY RS.DBNAME,BF.physical_device_name
END

PRINT (@TEXT)
END
IF (@RECOVER='YES')
BEGIN
SELECT @SQLString = ''
SELECT @SQLString = [SQL] +' ' + @SQLString from #RESTORE_SQL WHERE DBNAME=@dbname ORDER BY FILEID
IF LEN(LTRIM(RTRIM(@SQLString))) <> 0
BEGIN
SELECT @TEXT='USE [master]
GO
RESTORE DATABASE ['+DBNAME+'] FROM DISK = N'''+ @BKPATH+'\'+RIGHT(BF.physical_device_name, CHARINDEX('\', REVERSE(BF.physical_device_name)) - 1)+''' WITH FILE = 1,
'+@SQLString+'
NOUNLOAD, STATS = 5
GO' FROM #RESTORE_SQL RS
INNER JOIN msdb.dbo.backupset BS ON RS.DBNAME = BS.database_name
INNER JOIN MSDB.DBO.backupmediafamily BF ON BS.media_set_id=BF.media_set_id
WHERE DBNAME=@dbname AND BS.backup_set_id in (select max(backup_set_id) from msdb..backupset where type='D' group by database_name)
AND
BF.physical_device_name like '%' + @uniquecode + '%'
GROUP BY RS.DBNAME,BF.physical_device_name
END

PRINT (@TEXT)
END

FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
END


IF (@TYPE='DIFF')
BEGIN

CREATE TABLE #DIFF_BKPID (ID NVARCHAR(100),DBNAME NVARCHAR(500))

DECLARE db_cursor CURSOR FOR

select (a.dbname) AS dbname from msdb.dbo.[driver] a join sys.databases d on a.DBName = d.name

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @TEXT='INSERT INTO #DIFF_BKPID SELECT max(backup_set_id),database_name DBNAME FROM msdb.dbo.backupset BS
INNER JOIN MSDB.DBO.backupmediafamily BF ON BS.media_set_id=BF.media_set_id
WHERE BS.database_name='''+@dbname+''' AND TYPE=''I'' AND BS.backup_set_id >
(select max(backup_set_id) from msdb..backupset where database_name='''+@dbname+''' and type= ''D'' group by database_name)
group by database_name'

EXEC (@TEXT)


FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

DECLARE db_cursor CURSOR FOR
Select id,dbname from #DIFF_BKPID

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN

IF (@RECOVER='NO')
BEGIN
SELECT @TEXT='USE [master]
GO
RESTORE DATABASE ['+BS.database_name+'] FROM DISK = N'''+ @BKPATH+'\'+ RIGHT(BF.physical_device_name, CHARINDEX('\', REVERSE(BF.physical_device_name)) - 1)+''' WITH FILE = 1,
NOUNLOAD, NORECOVERY, STATS = 5
GO' FROM msdb.dbo.backupset BS
INNER JOIN MSDB.DBO.backupmediafamily BF ON BS.media_set_id=BF.media_set_id
WHERE BS.DATABASE_NAME=@dbname AND BS.backup_set_id=@ID
AND
BF.physical_device_name like '%' + @uniquecode + '%'

PRINT (@TEXT)
END

IF (@RECOVER='YES')
BEGIN
SELECT @TEXT='USE [master]
GO
RESTORE DATABASE ['+BS.database_name+'] FROM DISK = N'''+ @BKPATH+'\'+ RIGHT(BF.physical_device_name, CHARINDEX('\', REVERSE(BF.physical_device_name)) - 1)+''' WITH FILE = 1,
NOUNLOAD, STATS = 5
GO' FROM msdb.dbo.backupset BS
INNER JOIN MSDB.DBO.backupmediafamily BF ON BS.media_set_id=BF.media_set_id
WHERE BS.DATABASE_NAME=@dbname AND BS.backup_set_id=@ID
AND
BF.physical_device_name like '%' + @uniquecode + '%'

PRINT (@TEXT)
END

FETCH NEXT FROM db_cursor INTO @id,@dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

DROP TABLE #DIFF_BKPID

END

IF (@TYPE='LOG')
BEGIN

CREATE TABLE #LOG_NOREC (ID NVARCHAR(100),DBNAME NVARCHAR(500))

DECLARE db_cursor CURSOR FOR

select (a.dbname) AS dbname from msdb.dbo.[driver] a join sys.databases d on a.DBName = d.name

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @TEXT='INSERT INTO #LOG_NOREC SELECT backup_set_id,database_name DBNAME FROM msdb.dbo.backupset BS
INNER JOIN MSDB.DBO.backupmediafamily BF ON BS.media_set_id=BF.media_set_id
WHERE BS.database_name='''+@dbname+''' AND TYPE=''l'' AND BS.backup_set_id >
(select max(backup_set_id) from msdb..backupset where database_name='''+@dbname+''' and (type=''I'' OR TYPE=''D'') group by database_name)
AND BS.backup_set_id < (select max(backup_set_id) from msdb..backupset where type=''L'' AND database_name='''+@dbname+''' group by database_name) '

EXEC (@TEXT)


FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

DECLARE db_cursor CURSOR FOR
Select id,dbname from #LOG_NOREC order by 2,1

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @ID,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @TEXT='USE [master]
GO
RESTORE LOG ['+BS.database_name+'] FROM DISK = N'''+ @BKPATH+'\'+ RIGHT(BF.physical_device_name, CHARINDEX('\', REVERSE(BF.physical_device_name)) - 1)+''' WITH FILE = 1,
NOUNLOAD, NORECOVERY, STATS = 5
GO' FROM msdb.dbo.backupset BS
INNER JOIN MSDB.DBO.backupmediafamily BF ON BS.media_set_id=BF.media_set_id
WHERE BS.DATABASE_NAME=@dbname AND BS.backup_set_id=@ID
AND
BF.physical_device_name like '%' + @uniquecode + '%'

PRINT (@TEXT)

FETCH NEXT FROM db_cursor INTO @ID,@dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

CREATE TABLE #LOG_REC (ID NVARCHAR(100),DBNAME NVARCHAR(500))

DECLARE db_cursor CURSOR FOR
Select (a.dbname) AS dbname from msdb.dbo.[driver] a join sys.databases d on a.DBName = d.name

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @TEXT='INSERT INTO #LOG_REC SELECT MAX(backup_set_id),database_name FROM msdb.dbo.backupset BS
INNER JOIN MSDB.DBO.backupmediafamily BF ON BS.media_set_id=BF.media_set_id
WHERE BS.database_name='''+@DBNAME+''' AND TYPE=''l'' AND
backup_set_id > (select max(backup_set_id) from msdb..backupset where database_name='''+@dbname+''' and (type=''I'' OR TYPE=''D'') group by database_name)
GROUP BY database_name'

EXEC (@TEXT)

FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

DECLARE db_cursor CURSOR FOR
Select ID,dbname from #LOG_REC order by 2,1

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @ID,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @TEXT='USE [master]
GO
RESTORE LOG ['+BS.database_name+'] FROM DISK = N'''+ @BKPATH+'\'+ RIGHT(BF.physical_device_name, CHARINDEX('\', REVERSE(BF.physical_device_name)) - 1)+''' WITH FILE = 1,
NOUNLOAD, STATS = 5
GO' FROM msdb.dbo.backupset BS
INNER JOIN MSDB.DBO.backupmediafamily BF ON BS.media_set_id=BF.media_set_id
WHERE BS.DATABASE_NAME=@dbname AND BS.backup_set_id=@ID
AND
BF.physical_device_name like '%' + @uniquecode + '%'

PRINT (@TEXT)

FETCH NEXT FROM db_cursor INTO @ID,@dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

DROP TABLE #LOG_NOREC
DROP TABLE #LOG_REC

END

DROP TABLE #RESTORE_SQL

END
GO