info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
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
|
|
|
|
|
|
|
|
|
|