Category: Log-Shipping

I had a requirement to script a repeatable SQL 2014 ent to SQL 2016 std migration. This was to be for up to 200 databases and therefore needed to be automated.

I chose to encapsulate a blend of TSQL and Powershell in a non-scheduled SQL Job. And as we were going UP a version but DOWN an edition, I felt log-shipping would be the best option.

I idea was to run the job a week or so before hand. Then at the time of the migration (a weekend), just 15 minutes of data (per database) would need to traverse the network.

The SETUP job had 9 steps :-
1. Create a control table
2. Backup (because you never know)
3. Decrypt
4. Move Logins and Fix Orphans
5. Shrink the log-file
6. Log-Ship: Initial Full backup to remote-server
7. Log-Shipping: Initial Restores on Remote in recovery mode.
8. Log-Shipping: Create BACKUP jobs locally
9. Log-Shipping: Create COPY and RESTORE jobs remotely.

Step-1

-- 1.ControlTable.sql

USE msdb;
GO

IF OBJECT_ID('[msdb].[dbo].[LSList]') IS NOT NULL
    DROP TABLE [msdb].[dbo].[LSList];
GO

CREATE TABLE [msdb].[dbo].[LSList] ([database] NVARCHAR(255) NOT NULL,
                                    backup_directory NVARCHAR(255) NOT NULL,
                                    backup_share NVARCHAR(255) NOT NULL,
                                    backup_destination_directory NVARCHAR(255) NOT NULL,
                                    pre_mig_backup INT NOT NULL,
                                    is_encrypted INT NULL,
                                    LS_backup INT NULL,
                                    start_time_offset INT NOT NULL);

INSERT INTO [msdb].[dbo].[LSList] ([database],
                                   backup_directory,
                                   backup_share,
                                   backup_destination_directory,
                                   pre_mig_backup,
                                   start_time_offset)
VALUES (N'DatabaseName1', N'h:\shipping', N'\\LocalServerName\shipping', N'\\RemoteServerName\shipping', 0, 2);

INSERT INTO [msdb].[dbo].[LSList] ([database],
                                   backup_directory,
                                   backup_share,
                                   backup_destination_directory,
                                   pre_mig_backup,
                                   start_time_offset)
VALUES (N'DatabaseName2', N'h:\shipping', N'\\LocalServerName\shipping', N'\\RemoteServerName\shipping',0, 4);

-- populate encryption flag

UPDATE [msdb].[dbo].[LSList]
    SET is_encrypted = 1 -- yes
    WHERE [database] IN (SELECT db.[name]
                                          FROM sys.databases db
                                          JOIN sys.dm_database_encryption_keys dm
                                          ON db.database_id = dm.database_id );

-- select * FROM [msdb].[dbo].[LSList]

Step-2

-- 2.PreMigBackups.sql

-- select * from [msdb].[dbo].[LSList]
-- update [msdb].[dbo].[LSList] SET pre_mig_backup = 0

DECLARE @Query  NVARCHAR(MAX),
        @dbname VARCHAR(200);

WHILE (SELECT COUNT(*) FROM [msdb].[dbo].[LSList] WHERE pre_mig_backup = 0) > 0
BEGIN
    SET @dbname = (   SELECT TOP 1 [database]
                        FROM [msdb].[dbo].[LSList]
                       WHERE pre_mig_backup = 0);

    SET @Query = N'BACKUP DATABASE [' + @dbname + '] 
	TO  DISK = N''H:\SQL Backup\' + @dbname + '_' + replace(convert(varchar(16), getdate(),126), ':','') + '.bak'' 
	WITH COPY_ONLY, NOFORMAT, INIT,  STATS = 10';

    EXEC sp_executesql @Query;

    UPDATE [msdb].[dbo].[LSList]
    SET pre_mig_backup = 1
    WHERE [database] = @dbname;
END;

Step-3

-- 3.decrypt.sql

DECLARE @Query  NVARCHAR(MAX), @dbname VARCHAR(200);

/* 
 is_encrypted 
 null = no
 1 = yes
 0 = not any more
*/

WHILE (SELECT COUNT(*) FROM [msdb].[dbo].[LSList] WHERE is_encrypted = 1) > 0
BEGIN

    SET @dbname = (SELECT TOP 1 [database] FROM [msdb].[dbo].[LSList] WHERE is_encrypted = 1);

  /* 1 set encryption off */

    SET @Query = N'ALTER DATABASE [' + @dbname + N'] SET ENCRYPTION OFF;';
    EXEC sp_executesql @Query;

  /* 2 pause until decrypted */

    WHILE (  SELECT dm.encryption_state
                    FROM sys.databases db
                    LEFT JOIN sys.dm_database_encryption_keys dm
                       ON db.database_id = dm.database_id
                    WHERE [name] = @dbname)  1
    BEGIN
        WAITFOR DELAY '00:00:10';
    END;

  /*3 drop key */

    SET @Query = 'USE [' + @dbname + ']; DROP DATABASE ENCRYPTION KEY';
    EXEC sp_executesql @Query;

  /* 4 log changes then move on */

    UPDATE [msdb].[dbo].[LSList]
       SET is_encrypted = 0
     WHERE [database] = @dbname;

END;

-- Stop MLB

-- DECLARE @Query  NVARCHAR(MAX),
--        @dbname VARCHAR(200);

IF OBJECT_ID('tempdb..#tlist') IS NOT NULL
    DROP TABLE #tlist;
SELECT [database]
  INTO #tlist
  FROM [msdb].[dbo].[LSList];

WHILE (SELECT COUNT(*) FROM #tlist) > 0
BEGIN

    SET @dbname = (SELECT TOP 1 [database] FROM #tlist);

    SET @Query = N'EXEC [msdb].[smart_admin].[sp_set_db_backup]
					@database_name = [' + @dbname + N'],
					@enable_backup = 0'; -- off

    EXEC sp_executesql @Query;

    DELETE FROM #tlist
     WHERE [database] = @dbname;

END;

Step-4

Powershell.exe "Export-DbaLogin -SqlInstance LocalServerName -Append  -Path C:\temp\LocalServerName-logins.sql"

Powershell.exe "Export-DbaUser -SqlInstance LocalServerName -Append  -Path C:\temp\LocalServerName-users.sql"

Powershell.exe "Copy-DbaLogin -Source LocalServerName -Destination RemoteServerName -ExcludeSystemLogins"

Step-5

Powershell.exe "Repair-DbaOrphanUser -SqlInstance RemoteServerName"