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

,

The script includes these steps:

 

STEP 1: CREATE EMPTY Databases

STEP 2 - CREATE Logins WITH SERVER ROLESPERMISSIONS

STEP 3 - COPY LINKED SERVERS

STEP 4 - COPY SERVER OPTIONS

STEP 5 - COPY CREDENTIALS

STEP 6 - COPY AGENT JOBS

STEP 7 - COPY DB Mail

STEP 8 - COPY CERTIFICATES

STEP 9 - RESTORE USER DATABASES

Replace placeholders <Password>, <InstanceName>, and <BackupDirectory> with real values.

Set DECLARE @PrintOnly BIT = 0 -- 0 = execute, 1 = print. The script has it 1 = print.

Run the script on your new 2019 Instance with 'sa' rights.

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
*/

Rate

Share

Share

Rate