Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



SQL Server Automated Database Migration using Backup & Restore Method
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-04-28









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


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



Recommended Migration tasks:


1. DBCC UPDATEUSAGE (Transact-SQL)

- Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

EXEC sp_MSforeachdb 'USE ? DBCC UPDATEUSAGE (?)'
go


2. sp_updatestats

- Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.
- sp_updatestats updates statistics on disabled nonclustered indexes and does not update statistics on disabled clustered indexes.
- For disk-based tables, sp_updatestats updates statistics based on the modification_counter information in the sys.dm_db_stats_properties catalog view, updating statistics where at least one row has been modified. Statistics on memory-optimized tables are always updated when executing sp_updatestats. Therefore do not execute sp_updatestats more than necessary.
- sp_updatestats can trigger a recompile of stored procedures or other compiled code. However, sp_updatestats might not cause a recompile, if only one query plan is possible for the tables referenced and the indexes on them. A recompilation would be unnecessary in these cases even if statistics are updated.

EXEC sp_MSforeachdb 'USE ? exec sp_updatestats'
go


3. Check SQL Logins & Password are Migrated and Synced

Use Master
go

Use Master
go

IF EXISTS(
Select n1.name as 'Login Name', n1.type, n1.default_database_name, n1.sid, n2.name from sys.server_principals n1
left join [Linked_Server].Master.sys.server_principals n2 on n1.name = n2.name
where n2.name is null)
Begin
RAISERROR('Node 1 SQL Logins not synced to Node 2.', 16, 1)
End

IF EXISTS(
Select n1.name as 'Login Name', n1.type, n1.default_database_name, n1.sid, n2.name from sys.server_principals n1
right join [Linked_Server].Master.sys.server_principals n2 on n1.name = n2.name
where n1.name is null)
Begin
RAISERROR('Node 2 SQL Logins not synced to Node 1.', 16, 1)
End

DECLARE @cnt int = 0
SELECT @cnt = COUNT(*) from (

/*create windows logins*/
SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+
CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, CHECK_POLICY=OFF, '+
N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';' as value
FROM [Linked_Server].master.sys.server_principals AS sp
INNER JOIN [Linked_Server].master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE sp.[type]='S' AND sp.is_disabled=0 and not sp.[name] = 'sa'

except
SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+
CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, CHECK_POLICY=OFF, '+
N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';' as value
FROM [Linked_Server].master.sys.server_principals AS sp
INNER JOIN [Linked_Server].master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE sp.[type]='S' AND sp.is_disabled=0 and not sp.[name] = 'sa'
) as ServerLoginNotMatching

if @cnt > 0
begin
RAISERROR('Node 1 SQL Logins or passwords not synced to Node 2.', 16, 1)
end

SELECT @cnt = COUNT(*) from (

/*create windows logins*/
SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+
CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, CHECK_POLICY=OFF, '+
N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';' as value
FROM [Linked_Server].master.sys.server_principals AS sp
INNER JOIN [Linked_Server].master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE sp.[type]='S' AND sp.is_disabled=0 and not sp.[name] = 'sa'

except
SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+
CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, CHECK_POLICY=OFF, '+
N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';' as value
FROM [Linked_Server].master.sys.server_principals AS sp
INNER JOIN [Linked_Server].master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE sp.[type]='S' AND sp.is_disabled=0 and not sp.[name] = 'sa'
) as ServerLoginNotMatching

if @cnt > 0
begin
RAISERROR('Node 2 SQL Logins or passwords not synced to Node 1.', 16, 1)
end


4. Check SQL Jobs Migrated and Synced

Use MSDB
go

IF EXISTS(
select j.name, j2.name, j.description, j.category_id
from msdb.[dbo].[sysjobs] j
full outer join [Linked_Server].msdb.[dbo].[sysjobs] j2 on j.name = j2.name
where
--(j.enabled = 1 or j.enabled is null)
(j.name is null or j2.name is null)
--order by j.name asc
)
Begin
RAISERROR('Node 1 SQL Jobs not synced to Node 2.', 16, 1)
End