Azure DWH part 18: Working with SQL Server Analysis Services (2020-05-22)

 SQLServerCentral Newsletter for May 22, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Azure DWH part 18: Working with SQL Server Analysis Services]( - [[Video] Free DBA Training Month: Configuring TempDB, Lock Pages in Memory, and the DAC]( - [Azure Database Migration Guide]( - [From the SQL Server Central Blogs - Using Logic Apps in a Data Factory Execution Framework – Part 1]( - [From the SQL Server Central Blogs - Executing PoSh line by line in VS Code]( Question of the Day - [Counting Some Rows]( Featured Script - [Migrate and Upgrade SQL Instance (2014/2016) to 2019 version with 1 click]( The Voice of the DBA  Daily Coping Tip Reflect on what makes you feel really valued and appreciated I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate.  For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. The Life List Before [The Bucket List](, I heard Ted Leonsis talk on the radio about [his 101 things to do before he died](. He was one of the founders of AOL (remember that?) and after surviving a plane crash, he made his list. It's an incredible list, though some of the items on it are a product of his success in business. I'm not sure I'd ever include being worth $100mm or getting to the three comma club.  I heard him walk in 1999 and at that time I made a list of my own, along with my wife. I didn't really break it down into areas, but just had a list of different things. I also didn't want to wait for a life changing event (or retirement) to try and accomplish things in life. I'm not the only one. Brent Ozar has maintained his [epic life quest](, which he updates regularly, and I noticed Mala had [her own list for 2017](.  Recently as one of [the coping mechanisms for this pandemic](, I saw the advice to make a bucket list. My old list was gone, but I sat down with family and made a new one, of various items. I also thought about important things to me that have already happened, and added some of those, working on my own new list of things to take stock of life. My list is less a career list, as I'm near the end of my own career, but you might include career goals. I suggested my kids to do this as well.  Today I'm wondering how many of you stop to take stock of your lives. You might have a bucket list, or maybe just a few goals, or perhaps you point yourself in a new direction periodically and pursue some aim. I try hard to enjoy the little things, the parts of most days that go by that fill me with happiness and gratitude. I don't always succeed, but I often enjoy small moments in life. A moment of peace, a scene that catches my eye, a good workout, an exciting book, a piece of music.  I have a few sayings that stick with me all the time, but one is from John Lennon's [Beautiful Boy](28Darling_Boy%29). "Life is what happens to you when you're busy making other plans." Life does happen, and many of my plans to awry. That doesn't stop me from making them, or working to make this the best life it can be for me. After all, it's the only one I have. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Azure DWH part 18: Working with SQL Server Analysis Services]( Daniel Calbimonte from SQLServerCentral.com In this new article, we will learn how to create a cube extracting data from ADWH. [[Video] Free DBA Training Month: Configuring TempDB, Lock Pages in Memory, and the DAC]( Steve Jones - SSC Editor from SQLServerCentral While you’re stuck inside wondering if you’ll ever be able to attend a user group meeting again, I’m giving you free access to training materials that your boss probably wouldn’t have been nice enough to buy you anyway. So far this week, we’ve covered DBCC CHECKDB, how to set up Ola Hallengren’s maintenance scripts, and restoring for oops deletes. Today, let’s talk about two common configuration issues. Don’t dilly-dally on these though: these blog posts will be online during May only, and they’ll disappear on June 1. [Azure Database Migration Guide]( Steve Jones - SSC Editor from SQLServerCentral Step-by-step guidance for modernizing your data assets From the SQL Server Central Blogs - [Using Logic Apps in a Data Factory Execution Framework – Part 1]( Meagan Longoria from Data Savvy Data Factory allows parameterization in many parts of our solutions. We can parameterize things such as connection information in linked services as well as blob storage containers and files... From the SQL Server Central Blogs - [Executing PoSh line by line in VS Code]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. I’ve had to relearn this trick multiple times,...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Counting Some Rows I have two tables that are related. In my OrderHeader table, I have this data: [OrderHeader Data] I have this data in the OrderLine table: [OrderLine table data] These two tables are linked with the OrderKey in my Power BI Model. I want to add a column to the OrderHeader table that will keep the total number of rows from OrderLine that match the OrderKey for that row. What function should I use for my new column? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) XEvent Actions What is an action in Extended Events (XEvents)? Answer: A programmatic response or series of responses Explanation: Actions are often seen as the additional fields of data not included in an event. From BOL, this is the definition: "An action is a programmatic response or series of responses to an event. Actions are bound to an event, and each event may have a unique set of actions." Ref: SQL Server Extended Events Packages - [Discuss this question and answer on the forums](  Featured Script [Migrate and Upgrade SQL Instance (2014/2016) to 2019 version with 1 click]( Alexander Safronov from SQLServerCentral The script includes these steps:  STEP 1: CREATE EMPTY Databases STEP 2 - CREATE Logins WITH SERVER ROLES\PERMISSIONS 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 […] 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 */ [More »](  Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Restoring a database backup from SQL 2000 to SQL 2017 (via SQL 2008) - not good?]( - We are upgrading our old SQL 2000 box to SQL 2017. I know you cannot restore a backup from 2000 directly to 2017, so we are first restoring it to a SQL 2008 box, backing it up from there and then restoring that to SQL 2017. When we restore that to SQL 2017 will that […] [Performance regression after upgrade to 2017]( - Hi, We recently upgraded our production DB server from 2008R2 standard edition to 2017 Enterprise edition. we have noticed queries that would run for couple sends are taking long time to run and causing timeouts in applications. I have rebuild indexes and updated the statics. I checked the sql server logs to see if there […] SQL Server 2017 - Development [Convert Date field to YYYYMMDD from YYYY-MM-DD in SSIS]( - Hi, I've got a table containing Dates in YYYY-MM-DD which I need to change to YYYYMMDD in SSIS. I've made some progress after watching this video from Steve Fox. I'm using SSIS & this all works OK until I want to create a Derived Column on a Date field. In the video, some expressions are […] [Remove or Replace unwanted Characters from a table]( - Hi, I have some Tables that I want to transform in SSIS. Any occurences of more than one comma or other Character (|,/ etc..) should be removed leaving just one. This needs to be applied to the whole Table. Can anyone advise the best way to do this in SSIS? Thanks SQL Server 2016 - Development and T-SQL [stored procedure insert into a table -parameter containing the name of the table]( - Hi, I have 10 tables each with 2 columns (Id- identity, Name_xxxx) The name of the second column is different from a table to other. I want to write a stored procedure to insert a record into the table, but with 2 parameters in input (the name of the table and the value) exec sp_InsertIntoTable […] Administration - SQL Server 2014 [Problem with a heap table]( - I inserted in a heap table millions of rows. When finished, i executed select t2.name,t1.name,t3.name,t3.rows from sys.sysobjects as t1 inner join sys.schemas as t2 on t2.schema_id=t1.uid inner join sys.sysindexes as t3 on t3.id=t1.id where t1.xtype='U' and t3.status & 64 = 0 and t3.status & 16777216 = 0 and t3.status & 8388608 = 0 and t2.name='dbo' […] [Duplicate statistics - execution plan disruption]( - Hello, I will need your insight on the content of the execution plans in relation to the statistics. Request 1: do the values ??in the section only contain the statistics actually retained for the plan in question or is it the list of all potentially useful statistics? Request 2: for a particular step, how […] SQL 2012 - General [Timeoffset calculation logic]( - Please help me with the timeoffset logic calculation. I get time offset and start time of a server as input. My requirement is to show the result to the user after 1 hr and 30 mins of start time. I'm unable to do the timeoffset logic calculation based on this data. My DB runs at […] SQL Server 2012 - T-SQL [Arithmetic Overflow Error]( - I am getting an Arithmetic Overflow error on this field, suggestions on a fix?  CAST(ROUND(CAST(COALESCE(r.ActualAllowed * 100.0 / NULLIF(r.Allowed, 0), 0) AS FLOAT), 1) AS VARCHAR(10)) + '%' AS FeeSchedulePercentDiff SQL Server 2019 - Administration [SSAS - Query time when processing - works with 2017 - timesout with 2019]( - Hi, I'm wondering if someone can help us out here as we are at a loss. We have at present a process which works like this; 1. Server A (SQL 2016) kicks off an SSIS package which is locally stored as a .dstx file 2. That package connects to Server B (SQL 2017) which has […] [connecting to named instances from sql server to sql server, firewalls]( - I am setting up a Win2019/Sql2019 server. Setting up the firewalls for the default services are fairly straight forward using the article: All the information is for inbound connections. I'm trying to connect to a named instance through the firewall which is an outbound connection. I can add a specific firewall rule for each […] SQL Server 2019 - Development [Query JSON data]( - I have a field in my DB that has a string of JSON data.  This is an example: [{"id":"36653","invoicenumber":"8224131","billinginvoiceid":"948439"},{"id":"36651","invoicenumber":"8224133","billinginvoiceid":"948437"},{"id":"36652","invoicenumber":"8224134","billinginvoiceid":"948438"},{"id":"36654","invoicenumber":"8224155","billinginvoiceid":"948440"},{"id":"36655","invoicenumber":"8224161","billinginvoiceid":"948442"}] As you can see, there are basically 3 fields: ID, invoicenumber, and billinginvoiceid.  This string can have 1 record or 1000 records, there really isn't a limit.  I need to be able to query that field […] [Invalid column name error]( - I am trying to delete duplicate rows, but I get an error message saying that 'RowNumber' is an invalid column even though I have that column (as an alias). I tried running the SELECT and DELETE statement together and it still didn't work. I know it doesn't exist in the base table because it is […] SSRS 2016 [how to get fixed length file out of SSRS]( - In rsreportserver.config file, how can I set CSV export to have a field delimiter of none So that I can extract a fixed length file? I tried keeping it empty but it gives commas after each field value. Also if possible, please provide the script to get fixed length file and some of the fields […] COVID-19 Pandemic [Daily Coping 15 May 2020]( - Today’s tip is to show your gratitude to people who are helping to make things better. My thoughts:   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -