Migrate and Upgrade SQL Instance (2014/2016) to 2019 version with 1 click

USE [master]
GO
sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'xp_cmdshell', 1
reconfigure
GO
sp_configure 'Ole Automation Procedures', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
go
DECLARE @Source_Instance SYSNAME = '<InstanceName>', @BackupDirectory NVARCHAR(200) = '<BackupDirectory>'  -- must have enough space for all dbs backups in @Source_Instance 
DECLARE @CertPassword NVARCHAR(100) = '<Password>'
DECLARE @PrintOnly BIT = 1          -- 0 = execute, 1 = print
DECLARE @SQL NVARCHAR(MAX), @ProcExists BIT, @PrintStatement NVARCHAR(200)

SET NOCOUNT ON;

/* STEP 1: CREATE EMPTY Databases (will replace with real ones in last step) **********************************************************************************************/DECLARE @DefaultData nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', @DefaultData output

DECLARE @DefaultLog nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', @DefaultLog output


-- Check if Linked Server available
IF NOT EXISTS(SELECT TOP 1 srvname FROM sys.sysservers WHERE srvname = @Source_Instance)
BEGIN
SET @SQL = 'EXEC master.dbo.sp_addlinkedserver @server = N''' + @Source_Instance + ''', @srvproduct=N''SQL Server''
EXEC master.dbo.sp_serveroption @server=N''' + @Source_Instance + ''', @optname=N''data access'', @optvalue=N''true''
EXEC master.dbo.sp_serveroption @server=N''' + @Source_Instance + ''', @optname=N''rpc'', @optvalue=N''true''
EXEC master.dbo.sp_serveroption @server=N''' + @Source_Instance + ''', @optname=N''rpc out'', @optvalue=N''true''
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N''' + @Source_Instance + ''', @locallogin = NULL , @useself = N''True'''

BEGIN TRY
EXEC(@SQL)
END TRY
BEGIN CATCH  
PRINT 'Couldn''t create a linked server to the Source instance [' + @Source_Instance + ']!';
THROW
END CATCH
END

SET @SQL = 'SELECT [name] FROM [' + @Source_Instance + '].master.sys.sysdatabases WHERE dbid > 4' -- get all users databases

DECLARE @Databases TABLE ([ID] INT Identity (1,1) NOT NULL, DBName SYSNAME)

BEGIN TRY
INSERT INTO @Databases (DBName)
EXEC(@SQL)
END TRY
BEGIN CATCH  
PRINT 'Error retriving the list of databases from source Instance!';
THROW
END CATCH

DECLARE @RowNum INT = 1, @Database SYSNAME
WHILE @RowNum < (SELECT MAX([ID]) + 1 FROM @Databases)
BEGIN
SELECT @Database = DBName FROM @Databases WHERE [ID] = @RowNum
SET @SQL = 'CREATE DATABASE [' + @Database + '] ON  PRIMARY 
( NAME = N''' + @Database + ''', FILENAME = N''' + @DefaultData + '' + @Database + '.mdf'' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N''' + @Database + '_log'', FILENAME = N''' + @DefaultLog + '' + @Database + '_log.ldf'' , SIZE = 8192KB , FILEGROWTH = 65536KB )
ALTER DATABASE [' + @Database + '] SET COMPATIBILITY_LEVEL = 130'
SET @PrintStatement = ''
BEGIN TRY
  IF @PrintOnly = 1
PRINT @SQL
  ELSE
    BEGIN
  IF EXISTS(SELECT TOP 1 [name] FROM master.sys.sysdatabases WHERE [name] = @Database) 
    SET @PrintStatement = 'The database [' +  @Database + '] already exists!'
  ELSE
    EXEC(@SQL)
END
END TRY
BEGIN CATCH  
PRINT 'Error creating database [' + @Database + '] from source Instance!';
THROW
END CATCH
IF @PrintStatement = '' AND @PrintOnly = 0 SET @PrintStatement = 'Successfully created database [' + @Database + ']!'
PRINT @PrintStatement
SET @RowNum = @RowNum + 1
END

SET @SQL = ''
PRINT 'Successfully created EMPTY Databases!'

/* STEP 2 - CREATE LOGINs WITH SERVER ROLESPERMISSIONS  *************************************************/
DECLARE @SqlLogins TABLE ([RowNum] INT, CreateStatement NVARCHAR(2000))
DECLARE @CreateStatement NVARCHAR(2000)

SET @SQL = 'SELECT @ProcExists = CAST(1 AS BIT) 
FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Source_Instance + ';Trusted_Connection=yes;'',  
''SELECT [name] FROM sys.sysobjects WHERE [name] = ''''sp_hexadecimal'''' AND xtype = ''''P''''; '') AS a;'
EXEC sp_executesql @SQL, N'@ProcExists BIT  OUTPUT', @ProcExists OUTPUT
IF ISNULL(@ProcExists, 0) <> 1

BEGIN
SET @SQL = 'N''CREATE PROCEDURE [dbo].[sp_hexadecimal]
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ''''0x''''
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ''''0123456789ABCDEF''''
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue'''

SET @SQL = 'EXEC [' + @Source_Instance + '].master.sys.sp_executesql ' + @SQL
BEGIN TRY
EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
PRINT 'Error creating stored proc [sp_hexadecimal]!';
THROW
END CATCH
END
SET @ProcExists = NULL
SET @SQL = 'SELECT @ProcExists = CAST(1 AS BIT) 
FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Source_Instance + ';Trusted_Connection=yes;'',  
''SELECT [name] FROM sys.sysobjects WHERE [name] = ''''sp_help_revlogin_copy'''' AND xtype = ''''P''''; '') AS a;'
EXEC sp_executesql @SQL, N'@ProcExists BIT  OUTPUT', @ProcExists OUTPUT
IF ISNULL(@ProcExists, 0) = 1
BEGIN
SET @SQL = 'N''DROP PROCEDURE [dbo].[sp_help_revlogin_copy]'''
SET @SQL = 'EXEC [' + @Source_Instance + '].master.sys.sp_executesql ' + @SQL
BEGIN TRY
EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
PRINT 'Error dropping stored proc [sp_help_revlogin_copy]!';
THROW
END CATCH
END

SET @SQL = 'N''CREATE PROCEDURE [dbo].[sp_help_revlogin_copy] 
AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
DECLARE @ResultTable TABLE ([RowNum] INT Identity (1,1) NOT NULL, Statement NVARCHAR(2000))
 
SET NOCOUNT ON;

DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( ''''S'''', ''''G'''', ''''U'''' ) AND p.name <> ''''sa''''
AND CHARINDEX(''''#'''', p.name, 1) = 0 AND CHARINDEX(''''NT AUTHORITY'''', p.name, 1) = 0
AND CHARINDEX(''''NT SERVICE'''', p.name, 1) = 0

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT ''''No login(s) found.''''
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF (@type IN ( ''''G'''', ''''U''''))
BEGIN -- NT authenticated account/group
SET @tmpstr = ''''IF NOT EXISTS(SELECT TOP 1 [name] FROM sys.syslogins WHERE [name] = '''''''''''' + @name + '''''''''''') 
CREATE LOGIN '''' + QUOTENAME( @name ) + '''' FROM WINDOWS WITH DEFAULT_DATABASE = ['''' + @defaultdb + '''']''''
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ''''PasswordHash'''' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ''''ON'''' WHEN 0 THEN ''''OFF'''' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ''''ON'''' WHEN 0 THEN ''''OFF'''' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
SET @tmpstr = ''''IF NOT EXISTS(SELECT TOP 1 [name] FROM sys.syslogins WHERE [name] = '''''''''''' + @name + '''''''''''')
CREATE LOGIN '''' + QUOTENAME( @name ) + '''' WITH PASSWORD = '''' + @PWD_string + '''' HASHED, SID = '''' + @SID_string + '''', DEFAULT_DATABASE = ['''' + @defaultdb + '''']''''

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + '''', CHECK_POLICY = '''' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + '''', CHECK_EXPIRATION = '''' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + ''''; DENY CONNECT SQL TO '''' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + ''''; REVOKE CONNECT SQL TO '''' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + ''''; ALTER LOGIN '''' + QUOTENAME( @name ) + '''' DISABLE''''
END
INSERT INTO @ResultTable (Statement)
SELECT @tmpstr
END

INSERT INTO @ResultTable (Statement)
SELECT 
''''EXEC master..sp_addsrvrolemember @loginame = N'''''''''''' + SL.[name] + '''''''''''', @rolename = N'''''''''''' + SR.[name] + ''''''''''''
'''' AS [Role]
FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.[type] IN (''''S'''',''''G'''',''''U'''', ''''R'''') AND SL.[name] = @name

INSERT INTO @ResultTable (Statement)
SELECT 
CASE WHEN SrvPerm.state_desc <> ''''GRANT_WITH_GRANT_OPTION'''' 
THEN SrvPerm.state_desc 
ELSE ''''GRANT'''' 
END
+ '''' '''' + SrvPerm.permission_name + '''' TO ['''' + SP.[name] + '''']'''' + 
CASE WHEN SrvPerm.state_desc <> ''''GRANT_WITH_GRANT_OPTION'''' 
THEN '''''''' 
ELSE '''' WITH GRANT OPTION'''' 
END collate database_default AS [Permission] 
FROM sys.server_permissions AS SrvPerm 
JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE   SP.[type] IN ( ''''S'''', ''''U'''', ''''G'''' ) AND SP.[name] = @name

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
SELECT * FROM @ResultTable'''

SET @SQL = 'EXEC [' + @Source_Instance + '].master.sys.sp_executesql ' + @SQL
BEGIN TRY
EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
PRINT 'Error creating stored proc [sp_help_revlogin_copy]!';
THROW
END CATCH

SET @SQL = 'EXEC [' + @Source_Instance + '].master.dbo.sp_help_revlogin_copy'

BEGIN TRY
INSERT INTO @SqlLogins ([RowNum], CreateStatement)
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error executing stored proc [' + @Source_Instance + '].master.dbo.sp_help_revlogin_copy!';
THROW
END CATCH

SET @RowNum = 1
WHILE @RowNum < (SELECT MAX([RowNum]) + 1 FROM @SqlLogins)
BEGIN
SELECT @CreateStatement = CreateStatement FROM @SqlLogins WHERE [RowNum] = @RowNum
IF @PrintOnly = 1
PRINT @CreateStatement
ELSE
BEGIN TRY
EXEC(@CreateStatement)
END TRY
BEGIN CATCH
PRINT 'Error creating login [' + @CreateStatement + ']!';
--THROW   keep going!
END CATCH

SET @RowNum = @RowNum + 1
END
SET @SQL = ''
PRINT 'Successfully transferred logins!'

/* STEP 3 - LINKED SERVERS *************************************************************************************************/
SET @SQL = '
;WITH cte AS (
SELECT  a.server_id,
        a.[name],
        a.product,
        a.[provider],
        a.[data_source],
a.[provider_string],
        CAST(a.[is_collation_compatible] as int) as [collation compatible],
        CAST(a.[is_data_access_enabled] as int) as [data access],
        CAST(a.[is_distributor] as int) as [dist],
        CAST(a.[is_publisher] as int) as [pub],
        CAST(a.[is_remote_login_enabled] as int) as [rpc],
        CAST(a.[is_rpc_out_enabled] as int) as [rpc out],
        CAST(a.[is_subscriber] as int) as [sub],
        CAST(a.[connect_timeout] as int) as [connect timeout],
        CAST(a.[collation_name] as int) as [collation name],
        CAST(a.[lazy_schema_validation] as int) as [lazy schema validation],
        CAST(a.[query_timeout] as int) as [query timeout],
        CAST(a.[uses_remote_collation] as int) as [use remote collation],
        CAST(a.[is_remote_proc_transaction_promotion_enabled] as int) as [remote proc transaction promotion],
        c.[name] as locallogin,
        b.remote_name,
        b.uses_self_credential, 
        b.local_principal_id
FROM [' + @Source_Instance + '].master.sys.servers a
LEFT OUTER JOIN [' + @Source_Instance + '].master.sys.linked_logins b ON b.server_id = a.server_id
LEFT OUTER JOIN [' + @Source_Instance + '].master.sys.server_principals c ON c.principal_id = b.local_principal_id
LEFT JOIN master.sys.servers a2 ON a.[name] = a2.[name] AND a2.is_linked = 1
WHERE a.is_linked = 1 AND a2.is_linked IS NULL)
, unp AS (

SELECT  server_id,
        [name],
        product,
        [provider],
        [data_source],
[provider_string],
        CASE WHEN remote_name IS NULL THEN ''NULL'' ELSE ''N'''''' + remote_name +'''''''' END as rmtuser,
        CASE WHEN uses_self_credential = 0 THEN ''false'' ELSE ''true'' END as useself,
        CASE WHEN local_principal_id = 0 THEN ''NULL'' ELSE ''N'''''' + locallogin +'''''''' END as locallogin,
        Prop as PropertyName,
        CASE WHEN Props = 0 THEN ''false'' ELSE ''true'' END as PropertyValue
FROM (
    SELECT  server_id,
            [name],
            product,
            [provider],
            [data_source],
[provider_string],
            locallogin,
            remote_name,
            uses_self_credential,
            local_principal_id,
            [collation compatible],
            [data access],
            [dist],
            [pub],
            [rpc],
            [rpc out],
            [sub],
            [connect timeout],
            [collation name],
            [lazy schema validation],
            [query timeout],
            [use remote collation],
            [remote proc transaction promotion]
    FROM cte
) as p
UNPIVOT (
    Props FOR Prop IN (
            [collation compatible],
            [data access],
            [dist],
            [pub],
            [rpc],
            [rpc out],
            [sub],
            [connect timeout],
            [collation name],
            [lazy schema validation],
            [query timeout],
            [use remote collation],
            [remote proc transaction promotion]
    )
) as unpvt
)

SELECT DISTINCT 
''EXEC master.dbo.sp_addlinkedserver @server = N'''''' + name + '''''', @srvproduct=N'''''' + CASE WHEN provider = N''SQLNCLI'' THEN N''SQL Server'' ELSE product END + '''''''' + 
CASE WHEN product <> ''SQL Server'' AND provider <> N''SQLNCLI'' THEN '', @provider=N'''''' + [provider] + '''''', @provstr=N'''''' + ISNULL([provider_string], '''') + '''''''' ELSE '';'' END
+ CHAR(10) +
''EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'''''' + name + '''''',@useself=N''''''+useself+'''''',@locallogin=''+locallogin+'',@rmtuser='' + rmtuser +'',@rmtpassword=''''########'''''' + CHAR(10)
Col1
FROM unp
UNION ALL
SELECT ''EXEC master.dbo.sp_serveroption @server=N''''''+name+'''''', @optname=N''''''+PropertyName + '''''', @optvalue=N'''''' + CASE WHEN PropertyName IN (''connect timeout'', ''query timeout'') THEN ''0'' ELSE PropertyValue END +'''''''' + CHAR(10)
Col1
FROM unp'

DECLARE @TableOut TABLE ([RowNum] INT IDENTITY (1,1), CreateStatement nvarchar(1000))
INSERT INTO @TableOut (CreateStatement)
EXECUTE sp_executesql @SQL

SET @RowNum = 1
WHILE @RowNum < (SELECT MAX([RowNum]) + 1 FROM @TableOut)
BEGIN
SELECT @CreateStatement = CreateStatement FROM @TableOut WHERE [RowNum] = @RowNum
IF @PrintOnly = 1
PRINT @CreateStatement
ELSE
BEGIN TRY
EXEC(@CreateStatement)
END TRY
BEGIN CATCH
PRINT 'Error creating Linked Servers!';
THROW
END CATCH
SET @RowNum = @RowNum + 1
END
PRINT 'Linked servers created successully! Update passwords for the ones using remote login!'

/* STEP 4 - Copy Server options  ******************************************************************************************************************/SET @SQL = 'EXECUTE [' + @Source_Instance + '].master.sys.sp_configure ''show advanced options'', 1'
DECLARE @OptionsSource TABLE ([name] NVARCHAR(200), minimum INT, maximum INT, config_value INT, run_value INT)

  BEGIN
    BEGIN TRY
  EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error changing ''show advanced options'' to 1!';
THROW
END CATCH
SET @SQL = 'EXEC [' + @Source_Instance + '].master.sys.sp_executesql N''RECONFIGURE'';'
EXEC sp_executesql @SQL
SET @SQL = 'EXEC [' + @Source_Instance + '].master.sys.sp_configure; '
BEGIN TRY
INSERT INTO @OptionsSource
EXECUTE (@SQL)
END TRY
BEGIN CATCH
PRINT 'Error reading server options from the Source Instance [' + @Source_Instance + ']!';
THROW
END CATCH
DECLARE @OptionsDest TABLE ([name] NVARCHAR(200), minimum INT, maximum INT,config_value INT, run_value INT)
INSERT INTO @OptionsDest
EXECUTE master.sys.sp_configure;

DECLARE @Options TABLE ([RowNum] INT IDENTITY (1,1), CreateStatement nvarchar(1000))
INSERT INTO @Options
SELECT 'EXECUTE master.sys.sp_configure ''' + a.[name] + ''',' + CAST(s.config_value AS VARCHAR) 
FROM @OptionsDest a INNER JOIN @OptionsSource s ON s.[name] = a.[name] 
WHERE s.config_value <> a.config_value AND a.[name] NOT IN ('xp_cmdshell', 'Ole Automation Procedures', 'Ad Hoc Distributed Queries')

SET @RowNum = 1
WHILE @RowNum < (SELECT MAX([RowNum]) + 1 FROM @Options)
BEGIN
SELECT @CreateStatement = CreateStatement FROM @Options WHERE [RowNum] = @RowNum
IF @PrintOnly = 1
PRINT @CreateStatement
ELSE
BEGIN TRY
EXEC(@CreateStatement)
END TRY
BEGIN CATCH
PRINT 'Error setting server option [' + @CreateStatement + ']!';
THROW
END CATCH
SET @RowNum = @RowNum + 1
END
RECONFIGURE;
  END
PRINT 'Successfully copied Server options'
SET @SQL = ''

/* STEP 5 - Copy Credentials  ******************************************************************************************************************/DECLARE @CopyCredentials TABLE (RowNum INT IDENTITY (1,1), RestoreScript NVARCHAR(1000))
DECLARE @Proxies TABLE (RowNum INT IDENTITY (1,1), [proxy_id] INT, [name] SYSNAME, [credential_identity] SYSNAME, [enabled] TINYINT, [description] NVARCHAR(1024), [user_sid] VARBINARY(85), [credential_id] INT, [credential_identity_exists] INT)
DECLARE @Proxies2 TABLE (RowNum INT IDENTITY (1,1), [subsystem_id] INT, [subsystem_name] SYSNAME, [proxy_id] INT, [proxy_name] SYSNAME)

-- Get the credentials from sys.credentials, the password is unknown
SET @SQL = 'SELECT ''CREATE CREDENTIAL ['' + c.[name] + ''] WITH IDENTITY = '''''' + c.[credential_identity] + '''''', SECRET = ''''' + @CertPassword + '''''''
FROM [' + @Source_Instance + '].[master].[sys].[credentials] c
LEFT JOIN [master].[sys].[credentials] c2 ON c.[name] = c2.[name]
WHERE c2.[name] IS NULL
ORDER BY c.[name]'

BEGIN TRY
INSERT INTO @CopyCredentials (RestoreScript)
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error reading Credentials on Instance [' + @Source_Instance + ']!';
THROW
END CATCH

SET @SQL = ''
SET @RowNum = 1
WHILE @RowNum < (SELECT MAX([RowNum]) + 1 FROM @CopyCredentials)
BEGIN
SELECT @CreateStatement = RestoreScript FROM @CopyCredentials WHERE RowNum = @RowNum

IF @PrintOnly = 1
PRINT @CreateStatement
ELSE
BEGIN TRY
EXECUTE sp_executesql @CreateStatement
END TRY
BEGIN CATCH
PRINT 'Error creating Credentials [' + @CreateStatement + ']!';
THROW
END CATCH
SET @RowNum = @RowNum + 1
END

-- Get the proxies from sp_help_proxy and sys.credentials
SET @SQL = 'EXEC [' + @Source_Instance + '].msdb..sp_help_proxy'
BEGIN TRY
INSERT INTO @Proxies ([proxy_id], [name], [credential_identity], [enabled], [description], [user_sid], [credential_id], [credential_identity_exists])
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error reading Credentials on Instance [' + @Source_Instance + ']!';
THROW
END CATCH

SET @CreateStatement = ''
SET @RowNum = 1
WHILE @RowNum < (SELECT MAX([RowNum]) + 1 FROM @Proxies)
BEGIN
SELECT @CreateStatement = 'EXEC msdb.dbo.sp_add_proxy @proxy_name='''+[i].[name]+''', @enabled='+CAST([i].[enabled] AS VARCHAR)+', @description='+
(CASE WHEN [i].[description] IS NULL THEN 'NULL' ELSE '''' + [i].[description] + '''' END) + ', @credential_name=''' + [c].[name]+''''
FROM @Proxies [i]
INNER JOIN [master].[sys].[credentials] [c] ON [c].[credential_id] = [i].[credential_id]
LEFT JOIN [msdb].[dbo].[sysproxies] sp ON sp.[name] = [i].[name]
WHERE [i].RowNum = @RowNum AND sp.[name] IS NULL

IF @PrintOnly = 1
PRINT @CreateStatement
ELSE
BEGIN TRY
EXECUTE sp_executesql @CreateStatement
END TRY
BEGIN CATCH
PRINT 'Error creating Proxy [' + @CreateStatement + ']!';
THROW
END CATCH
SET @RowNum = @RowNum + 1
END

-- Get the proxy authorizations from sp_enum_proxy_for_subsystem

SET @SQL = 'EXEC [' + @Source_Instance + '].msdb..sp_enum_proxy_for_subsystem'

BEGIN TRY
INSERT INTO @Proxies2 ([subsystem_id], [subsystem_name], [proxy_id], [proxy_name])
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error reading Proxies Subsystem on Instance [' + @Source_Instance + ']!';
THROW
END CATCH

SET @CreateStatement = ''
SET @RowNum = 1
WHILE @RowNum < (SELECT MAX([RowNum]) + 1 FROM @Proxies2)
BEGIN
SELECT @CreateStatement = 'EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N''' + [proxy_name] + ''', @subsystem_id = ' + CAST([i].[subsystem_id] AS VARCHAR)
FROM @Proxies2 [i]
LEFT JOIN [msdb].[dbo].[sysproxysubsystem] sp ON sp.[subsystem_id] = [i].[subsystem_id]
WHERE [i].RowNum = @RowNum AND sp.[subsystem_id] IS NULL

IF @PrintOnly = 1
PRINT @CreateStatement
ELSE
BEGIN TRY
EXECUTE sp_executesql @CreateStatement
END TRY
BEGIN CATCH
PRINT 'Error creating Proxy Subsystem [' + @CreateStatement + ']!';
THROW
END CATCH
SET @RowNum = @RowNum + 1
END

  /* STEP 6 - Copy agent Jobs  *******************************************************************************************************************/
SET @SQL = 'IF EXISTS (SELECT TOP 1 1 FROM [' + @Source_Instance + '].msdb.dbo.sysoperators WHERE [enabled] = 1)
SELECT '''' + [name] + '''' Operator, 
''EXEC msdb.dbo.sp_add_operator @name=N'''''' + [name] + '''''', 
@enabled=1, 
@weekday_pager_start_time= '' + CAST(weekday_pager_start_time as VARCHAR) + '', 
@weekday_pager_end_time='' + CAST(weekday_pager_end_time as VARCHAR) + '', 
@saturday_pager_start_time='' + CAST(saturday_pager_start_time as VARCHAR) + '', 
@saturday_pager_end_time='' + CAST(saturday_pager_end_time as VARCHAR) + '', 
@sunday_pager_start_time='' + CAST(sunday_pager_start_time as VARCHAR) + '', 
@sunday_pager_end_time='' + CAST(sunday_pager_end_time as VARCHAR) + '', 
@pager_days='' + CAST(pager_days as VARCHAR) + '', 
@email_address=N'''''' + email_address + '''''', 
@category_name=N''''[Uncategorized]'''''' [Statement]
FROM [' + @Source_Instance + '].msdb.dbo.sysoperators WHERE [enabled] = 1'

DECLARE @Operators TABLE ([RowNum] INT IDENTITY (1,1), [Operator] sysname, CreateStatement nvarchar(1000))
DECLARE @Operator sysname

INSERT INTO @Operators ([Operator], CreateStatement)
EXEC(@SQL)

SET @RowNum = 1
WHILE @RowNum < (SELECT MAX([RowNum]) + 1 FROM @Operators)
BEGIN
SELECT @CreateStatement = CreateStatement, @Operator = [Operator] FROM @Operators WHERE [RowNum] = @RowNum
IF @PrintOnly = 1
PRINT @CreateStatement
ELSE
  BEGIN
IF NOT EXISTS(SELECT TOP 1 1 FROM msdb.dbo.sysoperators WHERE [name] = @Operator)
EXECUTE sp_executesql @CreateStatement
  END
SET @RowNum = @RowNum + 1
END

DECLARE @File  varchar(300) = 'C:TempCreate_SQLAgentJobSript.ps1'  -- local folder on sql instance
DECLARE @Text  varchar(8000) = '
$ServerNameList = "' + @Source_Instance + '"

#Load the SQL Server SMO Assemly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

#For each server in the array do the following..
foreach($ServerName in $ServerNameList)
{
Try
{
$objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
    Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline
    $objSQLConnection.Open() | Out-Null
    Write-Host "Success."
$objSQLConnection.Close()
}
Catch
{
Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
$errText =  $Error[0].ToString()
    if ($errText.Contains("network-related"))
{Write-Host "Connection Error. Check server name, port, firewall."}

Write-Host $errText
continue
}

#IF the output folder does not exist then create it
$OutputFolder = "c:TEMP"
$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}

#Create a new SMO instance for this $ServerName
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName

#Script out each SQL Server Agent Job for the server
$srv.JobServer.Jobs | foreach {$_.Script() + "GO`r`n"} | out-file "C:Tempjobs.sql"

}'
DECLARE @OLE            INT 
DECLARE @FileID         INT

EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT 
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @File, 8, 1 
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Text
EXECUTE sp_OADestroy @FileID 
EXECUTE sp_OADestroy @OLE 

DECLARE @SqlPowerShell AS VARCHAR(200)
SET @SqlPowerShell = 'powershell.exe -ExecutionPolicy Bypass -File "' + @File + '"'  

EXEC xp_cmdshell @SqlPowerShell

SET @SqlPowerShell = 'sqlcmd -S ' + @@SERVERNAME + ' -i C:Tempjobs.sql'

IF @PrintOnly = 0
EXEC xp_cmdshell @SqlPowerShell

PRINT 'Successfully copied agent Jobs!'

/* STEP 7 - Copy DB Mail  ******************************************************************************************************************/
SET @SQL = 'DECLARE @Mail TABLE ([name] sysname, minimum INT, maximum INT, config_value INT, run_value INT)

INSERT INTO @Mail
EXEC sp_configure ''Database Mail XPs''

IF (SELECT TOP 1 config_value FROM @Mail) = 1
  BEGIN
    DECLARE @ProfileName sysname, @Account sysname
WHILE EXISTS(SELECT TOP 1 * FROM msdb.dbo.sysmail_profile)
  BEGIN
SELECT TOP 1 @ProfileName = [name] FROM msdb.dbo.sysmail_profile
EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name=@ProfileName, @force_delete=True
  END

WHILE EXISTS(SELECT TOP 1 * FROM  msdb.dbo.sysmail_account)
  BEGIN
    SELECT TOP 1 @Account = [name] FROM msdb.dbo.sysmail_account
EXEC msdb.dbo.sysmail_delete_account_sp @account_name=@Account
  END

DECLARE @profile_name sysname='''', @account_name sysname='''', @SMTP_servername sysname, @email_address NVARCHAR(128), @display_name NVARCHAR(128), @replyto NVARCHAR(128), @sequence_number INT = 0;
WHILE EXISTS(SELECT TOP 1 1 FROM [' + @Source_Instance + '].msdb.dbo.[sysmail_account] WHERE @account_name <> [name] )
  BEGIN
SELECT TOP 1 @account_name = [name], @email_address = email_address, @display_name = display_name, @replyto = [replyto_address] FROM [' + @Source_Instance + '].[msdb].[dbo].[sysmail_account]
SELECT TOP 1 @SMTP_servername = servername FROM [' + @Source_Instance + '].[msdb].[dbo].[sysmail_server]
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@email_address = @email_address,
@display_name = @display_name,
@replyto_address = @replyto,
@mailserver_name = @SMTP_servername;
  END
WHILE EXISTS(SELECT TOP 1 1 FROM [' + @Source_Instance + '].msdb.dbo.sysmail_profile WHERE @profile_name <> [name] )
  BEGIN
SELECT TOP 1 @profile_name = [name] FROM [' + @Source_Instance + '].msdb.dbo.sysmail_profile WHERE @profile_name <> [name] 
SELECT TOP 1 @account_name = a.[name]
FROM [' + @Source_Instance + '].[msdb].[dbo].[sysmail_profileaccount] pa
INNER JOIN [' + @Source_Instance + '].[msdb].[dbo].[sysmail_account] a ON a.account_id = pa.account_id
INNER JOIN [' + @Source_Instance + '].[msdb].[dbo].[sysmail_profile] p ON p.profile_id = pa.profile_id
WHERE p.[name] = @profile_name

EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = @profile_name ;
-- Associate the account with the profile.
SELECT @sequence_number = ISNULL(MAX(sequence_number), 0) + 1 FROM [msdb].[dbo].[sysmail_profileaccount]
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = @sequence_number ;
  END
  END'

BEGIN TRY  
EXECUTE sp_executesql @SQL
END TRY
BEGIN CATCH
PRINT 'Error setting up DB Mail!';
THROW
END CATCH

PRINT 'Successfully configured DB Mail!'

/* STEP 8 - Copy Certificates  ******************************************************************************************************************/DECLARE @CertName sysname, @CertDesc NVARCHAR(300), @ActiveNode [NVARCHAR](128), @cmd NVARCHAR(600) 
DECLARE @Certs TABLE (RowNum INT IDENTITY (1,1), CertName sysname, issuer_name NVARCHAR(300))
DECLARE @OLEfolder INT, @OLEsource VARCHAR(255), @OLEdescription VARCHAR(255), @init INT, @OLEfilesytemobject INT, @NewFolder NVARCHAR(1000), @FileExists INT 

IF NOT EXISTS(SELECT TOP 1 1 FROM master.sys.symmetric_keys WHERE [name] = '##MS_DatabaseMasterKey##')
BEGIN
SET @SQL = 'CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''' + @CertPassword + ''''
EXEC(@SQL)   
END

SET @SQL = 'SELECT s.[name], s.[issuer_name] 
FROM [' + @Source_Instance + '].master.sys.certificates s 
LEFT JOIN master.sys.certificates d ON d.[name] = s.[name] 
WHERE LEFT(s.[name], 1) <> ''#'' AND d.[name] IS NULL'

BEGIN TRY
INSERT INTO @Certs (CertName, issuer_name)
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error reading Certificates from Instance [' + @Source_Instance + ']!';
THROW
END CATCH

------ Finding Active Node ------
BEGIN TRY
EXEC [master]..[xp_regread]  @rootkey = 'HKEY_LOCAL_MACHINE'
        ,@RegistryKeyPath = 'SYSTEMCurrentControlSetControlComputerNameComputerName'
        ,@value_name = 'ComputerName'
        ,@value = @ActiveNode OUTPUT
END TRY
BEGIN CATCH
PRINT 'Error reading Active Node name!';
THROW
END CATCH

SET @NewFolder = '' +@ActiveNode + 'C$Temp'
EXEC @init=sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT 

IF @init <> 0 
BEGIN 
    EXEC sp_OAGetErrorInfo @OLEfilesytemobject 
    RETURN 
END 

EXEC @init=sp_OAMethod @OLEfilesytemobject, 'FolderExists', @OLEfolder OUT, @NewFolder 

IF @OLEfolder=0 
    BEGIN 
    EXEC @init=sp_OAMethod @OLEfilesytemobject, 'CreateFolder', @OLEfolder OUT, @NewFolder 
END 
-- in case of error, raise it  
IF @init <> 0 
    BEGIN 
  BEGIN TRY
        EXEC sp_OAGetErrorInfo @OLEfilesytemobject, @OLEsource OUT, @OLEdescription OUT 
  END TRY
  BEGIN CATCH
PRINT 'Error creating folder [' + @NewFolder + ']!';
PRINT @OLEdescription;
THROW  
  END CATCH
    END 
EXECUTE @init = sp_OADestroy @OLEfilesytemobject 

SET @RowNum = 1
WHILE @RowNum < (SELECT MAX([RowNum]) + 1 FROM @Certs)
BEGIN
SELECT @CertName = CertName, @CertDesc = issuer_name FROM @Certs WHERE [RowNum] = @RowNum
-- backup Certificate, provide the password
SET @SQL = 'EXEC [' + @Source_Instance + '].master.sys.sp_executesql N''BACKUP CERTIFICATE ' + @CertName + ' 
TO FILE=''''' + @NewFolder + @CertName + '.crt''''
with private key (file = ''''' + @NewFolder + @CertName + '.key''''
, encryption By Password = ''''' + @CertPassword + ''')'''
IF @PrintOnly = 1
PRINT @SQL
ELSE
  BEGIN
    SET @cmd = @NewFolder + @CertName + '.crt'
    exec xp_fileExist @cmd, @FileExists OUTPUT
IF @FileExists = 1
BEGIN
SET @cmd = 'xp_cmdshell ''del "' + @NewFolder + @CertName + '.crt"''';
EXEC (@cmd)
END
SET @FileExists = 0
    SET @cmd = @NewFolder + @CertName + '.key'
    exec xp_fileExist @cmd, @FileExists OUTPUT
IF @FileExists = 1
BEGIN
SET @cmd = 'xp_cmdshell ''del "' + @NewFolder + @CertName + '.key"''';
EXEC (@cmd)
END
BEGIN TRY
EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
PRINT 'Error backing up Certificate [' + @SQL + ']!';
THROW
END CATCH
  END

SET @SQL = 'CREATE CERTIFICATE ' + @CertName + ' FROM FILE =''C:Temp' + @CertName + '.crt''   
WITH PRIVATE KEY (FILE = ''C:Temp' + @CertName + '.key''
    ,DECRYPTION BY PASSWORD = ''' + @CertPassword + ''')'
IF @PrintOnly = 1
PRINT @SQL
ELSE
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT 'Error creating Certificate [' + @SQL + ']!';
THROW
END CATCH
SET @RowNum = @RowNum + 1
END
SET @SQL = ''
PRINT 'Successfully copied Certificates!'


/* STEP 9 - Restore user databases  ******************************************************************************************************************/DECLARE @RestoreDatabases TABLE (RowNum INT IDENTITY (1,1), RestoreScript NVARCHAR(2000), ChangeOwnerScript NVARCHAR(1000), [database_name] sysname)
DECLARE @RestoreScript NVARCHAR(2000), @ChangeOwnerScript NVARCHAR(1000), @LastBackUpTime DATETIME, @DatabaseName sysname, @MoveFiles NVARCHAR(2000)--, @ServiceAccount sysname
DECLARE @DatabasesList TABLE (RowNum INT IDENTITY (1,1), DatabaseName sysname, LastBackUpTime DATETIME)
DECLARE @ForceFreshBackups BIT = 1  -- 1 = Force, 0 = use latest

-- check latest backup dates
SET @SQL = 'SELECT sdb.[name] AS DatabaseName,
COALESCE(MAX(bus.backup_finish_date), GETDATE()-10) AS LastBackUpTime
FROM [' + @Source_Instance + '].master.sys.databases sdb
LEFT JOIN [' + @Source_Instance + '].msdb.dbo.backupset bus ON bus.database_name = sdb.[name]
WHERE sdb.[name] NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
AND sdb.database_id NOT IN (SELECT database_id FROM [' + @Source_Instance + '].master.sys.dm_hadr_database_replica_states WHERE is_primary_replica = 0 AND database_state IS NOT NULL)  -- exclude mirrored databases
GROUP BY sdb.[name]'

BEGIN TRY
INSERT INTO @DatabasesList (DatabaseName, LastBackUpTime)
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error reading backup history for dbs on Instance [' + @Source_Instance + ']!';
THROW
END CATCH

SET @SQL = ''
SET @RowNum = 1
WHILE @RowNum < (SELECT MAX([RowNum]) + 1 FROM @DatabasesList)
BEGIN
SELECT @LastBackUpTime = LastBackUpTime, @DatabaseName = DatabaseName FROM @DatabasesList WHERE [RowNum] = @RowNum
IF @LastBackUpTime < GETDATE() - 1 OR @ForceFreshBackups = 1
  BEGIN
SET @SQL = 'EXEC [' + @Source_Instance + '].master.sys.sp_executesql 
N''BACKUP DATABASE [' + @DatabaseName + '] TO DISK = ''''' + @BackupDirectory + @DatabaseName + '.BAK'''' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION'''

IF @PrintOnly = 1
PRINT @SQL
ELSE
EXECUTE sp_executesql @SQL
  END
SET @RowNum = @RowNum + 1
END

--SELECT @ServiceAccount = service_account
--FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server (%'

DECLARE @ParmDefinition NVARCHAR(300)
SET @ParmDefinition = N'@ColValueOUT nvarchar(2000) OUTPUT';

SET @SQL = 'SELECT ''RESTORE DATABASE ['' + bs.[database_name] + ''] FROM  DISK = N'''''' +
    bmf.physical_device_name + '''''' WITH  FILE = 1, REPLACE, RECOVERY'' RestoreScript,
''ALTER AUTHORIZATION ON DATABASE::['' + bs.[database_name] + ''] TO [sa]'' ChangeOwnerScript, bs.[database_name] 
FROM [' + @Source_Instance + '].msdb.dbo.backupmediafamily bmf
INNER JOIN [' + @Source_Instance + '].msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
INNER JOIN [' + @Source_Instance + '].master.sys.sysdatabases sd ON sd.[name] = bs.[database_name]
WHERE (
bs.backup_set_id IN (
SELECT MAX(ba.backup_set_id)
FROM [' + @Source_Instance + '].msdb.dbo.backupset ba
WHERE ba.[type] = ''D''
GROUP BY ba.[database_name]
)        
)
AND bs.[database_name] NOT IN (''master'', ''model'', ''msdb'')
AND sd.dbid NOT IN (SELECT database_id FROM [' + @Source_Instance + '].master.sys.dm_hadr_database_replica_states WHERE is_primary_replica = 0 AND database_state IS NOT NULL)
ORDER BY bs.[database_name]'


BEGIN TRY
INSERT INTO @RestoreDatabases (RestoreScript, ChangeOwnerScript, [database_name])
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error reading Databases to restore from Instance [' + @Source_Instance + ']!';
THROW
END CATCH

DECLARE @NewDataPhysicalName nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', @NewDataPhysicalName output
SET @NewDataPhysicalName = @NewDataPhysicalName + ''
DECLARE @NewLogPhysicalName nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', @NewLogPhysicalName output
SET @NewLogPhysicalName = @NewLogPhysicalName + ''

SET @RowNum = 1
WHILE @RowNum < (SELECT MAX([RowNum]) + 1 FROM @RestoreDatabases)
BEGIN
SELECT @RestoreScript = RestoreScript, @ChangeOwnerScript = ChangeOwnerScript, @DatabaseName = [database_name] FROM @RestoreDatabases WHERE [RowNum] = @RowNum
SET @SQL = 'SELECT @ColValueOUT = STUFF((SELECT '', MOVE '''''' + [name] + '''''' TO '''''' + 
CASE type_desc WHEN ''ROWS'' THEN ''' + @NewDataPhysicalName + ''' ELSE ''' + @NewLogPhysicalName + ''' END + 
REPLACE(RIGHT([physical_name],CHARINDEX('''',REVERSE([physical_name])) - 1), ''' + @DatabaseName + ''',''' + @DatabaseName + ''') + ''''''''
                      FROM OPENDATASOURCE(''SQLNCLI'',''Data Source=' + @Source_Instance + ';Integrated Security=SSPI'').[' + @DatabaseName + '].sys.database_files
                         FOR XML PATH('''')
                      ), 1, 2, '''')'

BEGIN TRY
EXEC sp_executesql @SQL, @ParmDefinition, @ColValueOUT=@MoveFiles OUTPUT
END TRY
BEGIN CATCH
PRINT 'Error creating Restore Script for Database [' + @DatabaseName + ']!';
THROW
END CATCH

SET @RestoreScript = @RestoreScript + ', ' + @MoveFiles 
IF @PrintOnly = 1
PRINT @RestoreScript
ELSE
  BEGIN
BEGIN TRY
EXEC sp_executesql @RestoreScript
EXEC sp_executesql @ChangeOwnerScript
END TRY
BEGIN CATCH
PRINT 'Error restoring database [' + @RestoreScript + ']!';
THROW
END CATCH
  END
SET @RowNum = @RowNum + 1
END


-- Fix orphan users
BEGIN TRY
EXEC master..[sp_MSforeachdb]
'USE ?
DECLARE @SQL VARCHAR(200)
DECLARE curSQL CURSOR
FOR SELECT ''EXEC sp_change_users_login @Action=''''UPDATE_ONE'''', @UserNamePattern='''''' + name + '''''', @LoginName='''''' + name + ''''''''
FROM sysusers WHERE issqluser = 1 AND [name] NOT LIKE ''#%''
AND name NOT IN (''guest'', ''dbo'', ''sys'', ''INFORMATION_SCHEMA'', ''MS_DataCollectorInternalUser'')

OPEN curSQL
FETCH curSQL INTO @SQL

WHILE @@FETCH_STATUS = 0
  BEGIN
EXEC (@SQL)
FETCH curSQL INTO @SQL
  END

CLOSE curSQL
DEALLOCATE curSQL'
END TRY
BEGIN CATCH
-- do nothing
END CATCH



-- Finish. Disable SQL Agent on migrated from Instance.
SET @SQL = 'EXECUTE [' + @Source_Instance + '].master.sys.sp_configure ''Agent XPs'', 0'

BEGIN TRY
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT 'Error changing ''Agent XPs'' to 0 on [' + @Source_Instance + ']!';
THROW
END CATCH
SET @SQL = 'EXEC [' + @Source_Instance + '].master.sys.sp_executesql N''RECONFIGURE'';'
EXEC sp_executesql @SQL

EXEC master.dbo.sp_dropserver @server= @Source_Instance

GO
sp_configure 'xp_cmdshell', 0
reconfigure


/* 
Delete Aliases to the Old instance manually if any! 
Update port numbers on new instances to match migrated from.

After renaming the instance, run: 
DECLARE @ServerName NVARCHAR(200)
SELECT @ServerName = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(200))
EXEC sp_addserver @ServerName, local
and Restart sql instance
*/