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 @PrintOnly BIT = 0 -- 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 = ''''''''''
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:TESTSQLI" $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##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '55TRd&bB^20';

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 = '''''''')'''
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 = '''')'
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! Disable SQL Agent on Old instance! 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 */