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