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 (v1)
by BF (Principal Consultant; Architecture; Engineering)
2018-04-28








VERSION #1. VERSION #2 has enhancements for naming conventions used. Search site for v2.


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


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:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[Automated_Backup]
@type varchar(100), --FULL, DIFFERENTIAL, LOG
@path varchar(1000) --BACKUP LOCATION

As

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 + '\BKP_'+ @dbname + '_FULL_' + @fileDate + '.BAK'

SET @SQL='BACKUP DATABASE ['+@dbname +'] TO DISK = '+''''+@fileName+'''' +' '

PRINT (@SQL)

FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor
END

IF (@TYPE='DIFFERENTIAL')
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 + '\BKP_'+ @dbname + '_DIFF_' + @fileDate + '.BAK'

SET @SQL='BACKUP DATABASE ['+@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 + '\BKP_'+ @dbname + '_TLOG_' + @fileDate + '.TRN'

SET @SQL='BACKUP LOG ['+@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:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[Automated_Restore]
@TYPE NVARCHAR(100), --FULL or DIFFERENTIAL OR LOG
@RECOVER NVARCHAR(3), --'No' or 'Yes'
@BKPATH nvarchar(1000)

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 +'\' + replace(right(SUBSTRING([FILENAME],0,(LEN([FILENAME])- CHARINDEX('\',REVERSE([FILENAME]),0)+1)),charindex('\',REVERSE(SUBSTRING([FILENAME],0,(LEN([FILENAME])- CHARINDEX('\',REVERSE([FILENAME]),0)+1))),0)),'\','')+''+RIGHT([FileName],CHARINDEX('\',REVERSE([FileName]),-1 ))+''''','''
from MASTER..sysaltfiles
where db_name(dbid)=@name and fileid=@fileid and groupid <> 0
UNION
SELECT '''MOVE N'''''+name+''''' TO N'''''+ @LogPath +'\' + replace(right(SUBSTRING([FILENAME],0,(LEN([FILENAME])- CHARINDEX('\',REVERSE([FILENAME]),0)+1)),charindex('\',REVERSE(SUBSTRING([FILENAME],0,(LEN([FILENAME])- CHARINDEX('\',REVERSE([FILENAME]),0)+1))),0)),'\','')+''+RIGHT([FileName],CHARINDEX('\',REVERSE([FileName]),-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)
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)
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='DIFFERENTIAL')
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

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

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

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

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