Site-to-site migration and rollback

/* Cutover and rollback, simulated on one local instance with AdventureWorks.
   Local test only.
   Two copies of AdventureWorks play the two servers:
     Local_AW  = the local (on-premises) server        Remote_AW = the remote server in cloud
   What it shows: after the cutover, Local needs ONLY Remote's log backups to come back up to date.
   Remote's FULL and DIFF backups of the weekend are taken but never used for the normal rollback.
   Sections 6-10 then test the DIFFs: what happens if one is tried on the frozen copy, and the fallback
   when the frozen copy is lost (rebuild from Remote's FULL + DIFF + logs).
   Sequence:
     1. Local, normal days:  FULL, rows, LOG, rows, LOG
     2. Friday cutover:      row, tail-log WITH NORECOVERY on Local (script 90)  -> Local_AW RESTORING (frozen)
                             Remote_AW built from Local's FULL + LOGs + tail-log, recovered (45 + 91)
     3. Weekend on Remote:   rows, FULL_1 (step 8), LOG_1, DIFF_1, rows, LOG_2, FULL_2 (the 05:00 one), rows, DIFF_2, LOG_3, rows
     4. Monday rollback:     tail-log WITH NORECOVERY on Remote (script 93)  -> Remote_AW RESTORING
                             on Local_AW: Remote's LOGs + Remote's tail-log only, then RECOVERY (script 95)
     5. Check:               Local_AW has every row written on Local and on Remote
     6. What SQL Server recorded for Remote's backups: which FULL each DIFF is based on
     7. Test A: a DIFF tried on the frozen copy        expected Msg 3136; the copy stays RESTORING and the logs still apply
     8. Test B: FULL_2 + DIFF_1 (the wrong pair)       expected Msg 3136: DIFF_1's base is FULL_1
     9. Test C: FULL_2 + DIFF_2 + LOG_3 + tail-log     expected to WORK: the fallback with the latest FULL and DIFF
    10. Test D: FULL_1 + DIFF_1 + LOG_1..3 + tail-log  expected to WORK: Msg 4326 for LOG_1 (too early), FULL_2 does not break the chain
   Tests A-D restore into a third database, Rebuild_AW, so Local_AW and Remote_AW are left as section 5 left them.
   Tests A and B fail on purpose: read the Messages tab, the red lines there are the expected ones.
   Run the whole script (F5). The source database (@src) is only backed up (COPY_ONLY), not changed.
   Files go to <instance default backup folder>\AW_rollback_sim\ (SQL Server 2019 or later for that property). */
/* ---------- 0. Setup: Local_AW as a copy of AdventureWorks ---------- */
-- File names. The MOVE clause of every restore is built from the backup file itself (its file list in msdb),
-- so it uses whatever logical names that backup carries, not the source database's.
-- Local_AW's logical files are renamed at setup to AW_sim_data_<file_id> / AW_sim_log_<file_id>: nothing keeps
-- the source's names. Remote_AW and Rebuild_AW keep the logical names of the backup they come from (as BIAS
-- on EC2 keeps the names it had on Wyton); their physical files are named <database>_<file_id>.mdf / .ldf.
USE master;
IF OBJECT_ID(N'tempdb..#move_for') IS NOT NULL
    DROP PROCEDURE #move_for;
GO
CREATE PROCEDURE #move_for
    @backup NVARCHAR(400),
    @target sysname,
    @move NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @data NVARCHAR(400) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(400)),
            @log NVARCHAR(400) = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS NVARCHAR(400)),
            @set INT;
    SELECT TOP (1)
           @set = s.backup_set_id
    FROM msdb.dbo.backupset AS s
        JOIN msdb.dbo.backupmediafamily AS m
            ON m.media_set_id = s.media_set_id
    WHERE m.physical_device_name = @backup
    ORDER BY s.backup_set_id DESC;
    IF @set IS NULL
        THROW 50000, N'#move_for: no backup history in msdb for that file.', 1;
    SELECT @move
        = STRING_AGG(
                        CAST(N'MOVE N''' + REPLACE(f.logical_name, N'''', N'''''') + N''' TO N'''
                             + CASE
                                   WHEN f.file_type = 'L' THEN
                                       @log
                                   ELSE
                                       @data
                               END + @target + N'_' + CAST(f.file_number AS NVARCHAR(10))
                             + CASE
                                   WHEN f.file_type = 'L' THEN
                                       N'.ldf'
                                   ELSE
                                       N'.mdf'
                               END + N'''' AS NVARCHAR(MAX)),
                        N', '
                    )
    FROM msdb.dbo.backupfile AS f
    WHERE f.backup_set_id = @set
          AND f.file_type IN ( 'D', 'L' );
END;
GO
SET NOCOUNT ON;
DECLARE @src sysname = N'AdventureWorks'; -- change if your copy has another name
IF DB_ID(@src) IS NULL
    THROW 50000, N'Set @src to the name of your AdventureWorks database.', 1;
IF DB_ID(N'Local_AW') IS NOT NULL
   OR DB_ID(N'Remote_AW') IS NOT NULL
   OR DB_ID(N'Rebuild_AW') IS NOT NULL
    THROW 50000, N'Local_AW, Remote_AW or Rebuild_AW already exists: run the clean-up at the end first.', 1;
DECLARE @dir NVARCHAR(400) = CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS NVARCHAR(400));
IF RIGHT(@dir, 1) <> N'\'
    SET @dir += N'\';
SET @dir += N'AW_rollback_sim\';
EXEC master.sys.xp_create_subdir @dir;
IF OBJECT_ID(N'tempdb..#sim') IS NOT NULL
    DROP TABLE #sim;
CREATE TABLE #sim
(
    k VARCHAR(20) PRIMARY KEY,
    v NVARCHAR(MAX)
);
INSERT #sim
VALUES
('dir', @dir);
DECLARE @sql NVARCHAR(MAX),
        @move NVARCHAR(MAX);
SET @sql
    = N'BACKUP DATABASE ' + QUOTENAME(@src) + N' TO DISK = N''' + @dir
      + N'seed.bak'' WITH COPY_ONLY, INIT, FORMAT, CHECKSUM;';
EXEC (@sql);
DECLARE @seed NVARCHAR(400) = @dir + N'seed.bak';
EXEC #move_for @backup = @seed,
               @target = N'Local_AW',
               @move = @move OUTPUT;
SET @sql = N'RESTORE DATABASE Local_AW FROM DISK = N''' + @seed + N''' WITH RECOVERY, ' + @move + N';';
EXEC (@sql);
-- rename Local_AW's logical files, so no copy carries the source's names
SELECT @sql
    = STRING_AGG(
                    CAST(N'ALTER DATABASE Local_AW MODIFY FILE (NAME = N''' + REPLACE(name, N'''', N'''''')
                         + N''', NEWNAME = N''AW_sim_' + CASE
                                                             WHEN type = 1 THEN
                                                                 N'log_'
                                                             ELSE
                                                                 N'data_'
                                                         END + CAST(file_id AS NVARCHAR(10)) + N''');' AS NVARCHAR(MAX)),
                    N' '
                )
FROM sys.master_files
WHERE database_id = DB_ID(N'Local_AW')
      AND type IN ( 0, 1 );
EXEC (@sql);
ALTER DATABASE Local_AW SET RECOVERY FULL;
EXEC (N'USE Local_AW; CREATE TABLE dbo.Movement (id int IDENTITY(1,1) PRIMARY KEY, note varchar(80) NOT NULL, written_on varchar(10) NOT NULL, at datetime2(3) NOT NULL DEFAULT SYSDATETIME());');
GO
/* ---------- 1. Local, normal days: FULL, rows, LOG, rows, LOG ---------- */
DECLARE @dir NVARCHAR(400) =
        (
            SELECT v FROM #sim WHERE k = 'dir'
        ),
        @sql NVARCHAR(MAX);
SET @sql = N'BACKUP DATABASE Local_AW TO DISK = N''' + @dir + N'LOC_FULL.bak'' WITH INIT, FORMAT, CHECKSUM;';
EXEC (@sql);
INSERT Local_AW.dbo.Movement
(
    note,
    written_on
)
VALUES
('Local: normal day 1', 'Local'),
('Local: normal day 2', 'Local');
SET @sql = N'BACKUP LOG Local_AW TO DISK = N''' + @dir + N'LOC_LOG_1.trn'' WITH INIT, FORMAT, CHECKSUM;';
EXEC (@sql);
INSERT Local_AW.dbo.Movement
(
    note,
    written_on
)
VALUES
('Local: normal day 3', 'Local');
SET @sql = N'BACKUP LOG Local_AW TO DISK = N''' + @dir + N'LOC_LOG_2.trn'' WITH INIT, FORMAT, CHECKSUM;';
EXEC (@sql);
GO
/* ---------- 2. Friday cutover: freeze Local, build Remote from Local's backups ---------- */
DECLARE @dir NVARCHAR(400) =
        (
            SELECT v FROM #sim WHERE k = 'dir'
        ),
        @sql NVARCHAR(MAX);
INSERT Local_AW.dbo.Movement
(
    note,
    written_on
)
VALUES
('Local: last transaction before the tail-log', 'Local');
-- script 90: tail-log WITH NORECOVERY -> Local_AW stays RESTORING, frozen
SET @sql
    = N'ALTER DATABASE Local_AW SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ' + N'BACKUP LOG Local_AW TO DISK = N'''
      + @dir + N'LOC_LOG_TAIL.trn'' WITH NORECOVERY, INIT, FORMAT, CHECKSUM;';
EXEC (@sql);
-- scripts 45 + 91 on Remote: FULL + every log + tail-log, then recover
DECLARE @move NVARCHAR(MAX),
        @bk NVARCHAR(400) = @dir + N'LOC_FULL.bak';
EXEC #move_for @backup = @bk,
               @target = N'Remote_AW',
               @move = @move OUTPUT;
SET @sql = N'RESTORE DATABASE Remote_AW FROM DISK = N''' + @bk + N''' WITH NORECOVERY, ' + @move + N';';
EXEC (@sql);
SET @sql = N'RESTORE LOG Remote_AW FROM DISK = N''' + @dir + N'LOC_LOG_1.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Remote_AW FROM DISK = N''' + @dir + N'LOC_LOG_2.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Remote_AW FROM DISK = N''' + @dir + N'LOC_LOG_TAIL.trn'' WITH NORECOVERY;';
EXEC (@sql);
RESTORE DATABASE Remote_AW WITH RECOVERY;
ALTER DATABASE Remote_AW SET MULTI_USER;
SELECT name,
       state_desc,
       user_access_desc
FROM sys.databases
WHERE name IN ( N'Local_AW', N'Remote_AW' ); -- Local_AW RESTORING, Remote_AW ONLINE
GO
/* ---------- 3. The weekend on Remote: rows, and the backups Remote takes (FULL, DIFF, LOG) ---------- */
DECLARE @dir NVARCHAR(400) =
        (
            SELECT v FROM #sim WHERE k = 'dir'
        ),
        @sql NVARCHAR(MAX);
INSERT Remote_AW.dbo.Movement
(
    note,
    written_on
)
VALUES
('Remote: Friday night test', 'Remote');
SET @sql = N'BACKUP DATABASE Remote_AW TO DISK = N''' + @dir + N'REM_FULL_1.bak'' WITH INIT, FORMAT, CHECKSUM;';
EXEC (@sql); -- CUTOVER step 8
INSERT Remote_AW.dbo.Movement
(
    note,
    written_on
)
VALUES
('Remote: Saturday', 'Remote');
SET @sql = N'BACKUP LOG Remote_AW TO DISK = N''' + @dir + N'REM_LOG_1.trn'' WITH INIT, FORMAT, CHECKSUM;';
EXEC (@sql);
SET @sql
    = N'BACKUP DATABASE Remote_AW TO DISK = N''' + @dir
      + N'REM_DIFF_1.bak'' WITH DIFFERENTIAL, INIT, FORMAT, CHECKSUM;';
EXEC (@sql);
INSERT Remote_AW.dbo.Movement
(
    note,
    written_on
)
VALUES
('Remote: Sunday, go-live', 'Remote');
SET @sql = N'BACKUP LOG Remote_AW TO DISK = N''' + @dir + N'REM_LOG_2.trn'' WITH INIT, FORMAT, CHECKSUM;';
EXEC (@sql);
SET @sql = N'BACKUP DATABASE Remote_AW TO DISK = N''' + @dir + N'REM_FULL_2.bak'' WITH INIT, FORMAT, CHECKSUM;';
EXEC (@sql); -- the 05:00 FULL
INSERT Remote_AW.dbo.Movement
(
    note,
    written_on
)
VALUES
('Remote: Monday morning business', 'Remote');
SET @sql
    = N'BACKUP DATABASE Remote_AW TO DISK = N''' + @dir
      + N'REM_DIFF_2.bak'' WITH DIFFERENTIAL, INIT, FORMAT, CHECKSUM;';
EXEC (@sql); -- a DIFF on top of the 05:00 FULL
SET @sql = N'BACKUP LOG Remote_AW TO DISK = N''' + @dir + N'REM_LOG_3.trn'' WITH INIT, FORMAT, CHECKSUM;';
EXEC (@sql);
INSERT Remote_AW.dbo.Movement
(
    note,
    written_on
)
VALUES
('Remote: last transaction before the rollback', 'Remote');
GO
/* ---------- 4. Monday rollback: freeze Remote, give Local only Remote's logs ---------- */
DECLARE @dir NVARCHAR(400) =
        (
            SELECT v FROM #sim WHERE k = 'dir'
        ),
        @sql NVARCHAR(MAX);
-- what Remote has, to compare with afterwards
IF OBJECT_ID(N'tempdb..#remote_rows') IS NOT NULL
    DROP TABLE #remote_rows;
SELECT id,
       note,
       written_on
INTO #remote_rows
FROM Remote_AW.dbo.Movement;
-- script 93: tail-log WITH NORECOVERY on Remote -> Remote_AW RESTORING
SET @sql
    = N'ALTER DATABASE Remote_AW SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ' + N'BACKUP LOG Remote_AW TO DISK = N'''
      + @dir + N'REM_LOG_TAIL.trn'' WITH NORECOVERY, INIT, FORMAT, CHECKSUM;';
EXEC (@sql);
-- script 95 on Local: Remote's LOGs only (REM_FULL_1, REM_DIFF_1, REM_FULL_2, REM_DIFF_2 are NOT used), then recover
SET @sql = N'RESTORE LOG Local_AW FROM DISK = N''' + @dir + N'REM_LOG_1.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Local_AW FROM DISK = N''' + @dir + N'REM_LOG_2.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Local_AW FROM DISK = N''' + @dir + N'REM_LOG_3.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Local_AW FROM DISK = N''' + @dir + N'REM_LOG_TAIL.trn'' WITH NORECOVERY;';
EXEC (@sql);
RESTORE DATABASE Local_AW WITH RECOVERY;
ALTER DATABASE Local_AW SET MULTI_USER;
GO
/* ---------- 5. Check ---------- */
SELECT name,
       state_desc,
       user_access_desc
FROM sys.databases
WHERE name IN ( N'Local_AW', N'Remote_AW' ); -- Local_AW ONLINE, Remote_AW RESTORING
SELECT id,
       note,
       written_on
FROM Local_AW.dbo.Movement
ORDER BY id; -- 4 rows written on Local, 5 on Remote
SELECT CASE
           WHEN NOT EXISTS
                    (
                        SELECT id,
                               note,
                               written_on
                        FROM #remote_rows
                        EXCEPT
                        SELECT id,
                               note,
                               written_on
                        FROM Local_AW.dbo.Movement
                    )
                AND NOT EXISTS
                        (
                            SELECT id,
                                   note,
                                   written_on
                            FROM Local_AW.dbo.Movement
                            EXCEPT
                            SELECT id,
                                   note,
                                   written_on
                            FROM #remote_rows
                        ) THEN
               'IDENTICAL: Local has everything Remote had, from logs only'
           ELSE
               'DIFFERENT'
       END AS result;
GO
/* ---------- 6. What SQL Server recorded: each backup's LSN range and which FULL each DIFF is based on ---------- */
DECLARE @dir NVARCHAR(400) =
        (
            SELECT v FROM #sim WHERE k = 'dir'
        );
WITH files
AS (SELECT file_name
    FROM
    (
        VALUES
            (N'LOC_FULL.bak'),
            (N'LOC_LOG_1.trn'),
            (N'LOC_LOG_2.trn'),
            (N'LOC_LOG_TAIL.trn'),
            (N'REM_FULL_1.bak'),
            (N'REM_LOG_1.trn'),
            (N'REM_DIFF_1.bak'),
            (N'REM_LOG_2.trn'),
            (N'REM_FULL_2.bak'),
            (N'REM_DIFF_2.bak'),
            (N'REM_LOG_3.trn'),
            (N'REM_LOG_TAIL.trn')
    ) AS v (file_name) ),
     h
AS (SELECT f.file_name,
           x.*
    FROM files AS f
        CROSS APPLY
    (
        SELECT TOP (1)
               s.backup_set_id,
               s.database_name,
               s.type,
               s.backup_set_uuid,
               s.differential_base_guid,
               s.first_lsn,
               s.last_lsn,
               s.database_backup_lsn
        FROM msdb.dbo.backupset AS s
            JOIN msdb.dbo.backupmediafamily AS m
                ON m.media_set_id = s.media_set_id
        WHERE m.physical_device_name = @dir + f.file_name
        ORDER BY s.backup_set_id DESC
    ) AS x )
SELECT h.file_name,
       h.database_name,
       CASE h.type
           WHEN 'D' THEN
               'FULL'
           WHEN 'I' THEN
               'DIFF'
           WHEN 'L' THEN
               'LOG'
       END AS kind,
       h.first_lsn,
       h.last_lsn,
       h.database_backup_lsn,
       base.file_name AS diff_based_on -- REM_DIFF_1 -> REM_FULL_1, REM_DIFF_2 -> REM_FULL_2
FROM h
    LEFT JOIN h AS base
        ON h.type = 'I'
           AND base.type = 'D'
           AND base.backup_set_uuid = h.differential_base_guid
ORDER BY h.backup_set_id;
-- the row check used by tests A, C and D: Rebuild_AW against what Remote had before its tail-log
DELETE #sim
WHERE k = 'cmp';
INSERT #sim
VALUES
('cmp',
 N'SELECT ''{test}'' AS test,
       CASE WHEN NOT EXISTS (SELECT id, note, written_on FROM #remote_rows EXCEPT SELECT id, note, written_on FROM Rebuild_AW.dbo.Movement)
             AND NOT EXISTS (SELECT id, note, written_on FROM Rebuild_AW.dbo.Movement EXCEPT SELECT id, note, written_on FROM #remote_rows)
            THEN ''IDENTICAL to Remote'' ELSE ''DIFFERENT'' END AS result,
       (SELECT COUNT(*) FROM Rebuild_AW.dbo.Movement) AS rows_found;');
GO
/* ---------- 7. Test A: a DIFF tried on the frozen copy (expected to FAIL), then the logs as normal ---------- */
-- The real-life question: on Sunday someone restores a DIFF from S3 onto Wyton's RESTORING copy by mistake. What happens?
DECLARE @dir NVARCHAR(400) =
        (
            SELECT v FROM #sim WHERE k = 'dir'
        ),
        @move NVARCHAR(MAX),
        @bk NVARCHAR(400),
        @sql NVARCHAR(MAX);
PRINT N'===== Test A: build a copy of the frozen Local (LOC_FULL + LOC_LOG_1, 2, TAIL), as Local_AW was after section 2 =====';
SET @bk = @dir + N'LOC_FULL.bak';
EXEC #move_for @backup = @bk,
               @target = N'Rebuild_AW',
               @move = @move OUTPUT;
SET @sql = N'RESTORE DATABASE Rebuild_AW FROM DISK = N''' + @bk + N''' WITH NORECOVERY, REPLACE, ' + @move + N';';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'LOC_LOG_1.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'LOC_LOG_2.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'LOC_LOG_TAIL.trn'' WITH NORECOVERY;';
EXEC (@sql);
PRINT N'===== Test A: now REM_DIFF_2 on it. Expected: Msg 3136 (its base is REM_FULL_2, the copy was built from LOC_FULL) =====';
SET @sql = N'RESTORE DATABASE Rebuild_AW FROM DISK = N''' + @dir + N'REM_DIFF_2.bak'' WITH NORECOVERY;';
EXEC (@sql);
SELECT 'Test A, after the failed DIFF' AS test,
       name,
       state_desc
FROM sys.databases
WHERE name = N'Rebuild_AW'; -- still RESTORING
PRINT N'===== Test A: Remote''s logs, as in section 4. Expected: they apply, the failed DIFF did no harm =====';
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'REM_LOG_1.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'REM_LOG_2.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'REM_LOG_3.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'REM_LOG_TAIL.trn'' WITH NORECOVERY;';
EXEC (@sql);
RESTORE DATABASE Rebuild_AW WITH RECOVERY;
ALTER DATABASE Rebuild_AW SET MULTI_USER;
SET @sql = REPLACE(
           (
               SELECT v FROM #sim WHERE k = 'cmp'
           ),
           N'{test}',
           N'Test A'
                  );
EXEC (@sql);
GO
/* ---------- 8. Test B: FULL_2 + DIFF_1, the wrong pair (expected to FAIL) ---------- */
DECLARE @dir NVARCHAR(400) =
        (
            SELECT v FROM #sim WHERE k = 'dir'
        ),
        @move NVARCHAR(MAX),
        @bk NVARCHAR(400),
        @sql NVARCHAR(MAX);
PRINT N'===== Test B: REM_FULL_2 + REM_DIFF_1. Expected: Msg 3136 at REM_DIFF_1 (its base is REM_FULL_1) =====';
SET @bk = @dir + N'REM_FULL_2.bak';
EXEC #move_for @backup = @bk,
               @target = N'Rebuild_AW',
               @move = @move OUTPUT;
SET @sql = N'RESTORE DATABASE Rebuild_AW FROM DISK = N''' + @bk + N''' WITH NORECOVERY, REPLACE, ' + @move + N';';
EXEC (@sql);
SET @sql = N'RESTORE DATABASE Rebuild_AW FROM DISK = N''' + @dir + N'REM_DIFF_1.bak'' WITH NORECOVERY;';
EXEC (@sql);
SELECT 'Test B, after the failed DIFF' AS test,
       name,
       state_desc
FROM sys.databases
WHERE name = N'Rebuild_AW'; -- RESTORING, left for test C to replace
GO
/* ---------- 9. Test C: the fallback, latest FULL + its DIFF + the logs after it (expected to WORK) ---------- */
-- The real-life case: Wyton's frozen copy is lost, so it is rebuilt from EC2's backups.
-- REM_LOG_3 starts before REM_DIFF_2 and ends after it: it still applies.
DECLARE @dir NVARCHAR(400) =
        (
            SELECT v FROM #sim WHERE k = 'dir'
        ),
        @move NVARCHAR(MAX),
        @bk NVARCHAR(400),
        @sql NVARCHAR(MAX);
PRINT N'===== Test C: REM_FULL_2 + REM_DIFF_2 + REM_LOG_3 + REM_LOG_TAIL. Expected: works, identical to Remote =====';
SET @bk = @dir + N'REM_FULL_2.bak';
EXEC #move_for @backup = @bk,
               @target = N'Rebuild_AW',
               @move = @move OUTPUT;
SET @sql = N'RESTORE DATABASE Rebuild_AW FROM DISK = N''' + @bk + N''' WITH NORECOVERY, REPLACE, ' + @move + N';';
EXEC (@sql);
SET @sql = N'RESTORE DATABASE Rebuild_AW FROM DISK = N''' + @dir + N'REM_DIFF_2.bak'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'REM_LOG_3.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'REM_LOG_TAIL.trn'' WITH NORECOVERY;';
EXEC (@sql);
RESTORE DATABASE Rebuild_AW WITH RECOVERY;
ALTER DATABASE Rebuild_AW SET MULTI_USER;
SET @sql = REPLACE(
           (
               SELECT v FROM #sim WHERE k = 'cmp'
           ),
           N'{test}',
           N'Test C'
                  );
EXEC (@sql);
GO
/* ---------- 10. Test D: older FULL + its DIFF + every log (expected to WORK, LOG_1 refused as too early) ---------- */
-- Shows that REM_FULL_2 in between does not break the chain from REM_FULL_1, and that a log ending
-- before the DIFF is refused harmlessly (script 95 skips Msg 4326 the same way).
DECLARE @dir NVARCHAR(400) =
        (
            SELECT v FROM #sim WHERE k = 'dir'
        ),
        @move NVARCHAR(MAX),
        @bk NVARCHAR(400),
        @sql NVARCHAR(MAX);
PRINT N'===== Test D: REM_FULL_1 + REM_DIFF_1 + REM_LOG_1, 2, 3, TAIL. Expected: Msg 4326 for REM_LOG_1 (too early), the rest apply =====';
SET @bk = @dir + N'REM_FULL_1.bak';
EXEC #move_for @backup = @bk,
               @target = N'Rebuild_AW',
               @move = @move OUTPUT;
SET @sql = N'RESTORE DATABASE Rebuild_AW FROM DISK = N''' + @bk + N''' WITH NORECOVERY, REPLACE, ' + @move + N';';
EXEC (@sql);
SET @sql = N'RESTORE DATABASE Rebuild_AW FROM DISK = N''' + @dir + N'REM_DIFF_1.bak'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'REM_LOG_1.trn'' WITH NORECOVERY;';
EXEC (@sql); -- Msg 4326 expected
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'REM_LOG_2.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'REM_LOG_3.trn'' WITH NORECOVERY;';
EXEC (@sql);
SET @sql = N'RESTORE LOG Rebuild_AW FROM DISK = N''' + @dir + N'REM_LOG_TAIL.trn'' WITH NORECOVERY;';
EXEC (@sql);
RESTORE DATABASE Rebuild_AW WITH RECOVERY;
ALTER DATABASE Rebuild_AW SET MULTI_USER;
SET @sql = REPLACE(
           (
               SELECT v FROM #sim WHERE k = 'cmp'
           ),
           N'{test}',
           N'Test D'
                  );
EXEC (@sql);
-- file names of the three copies: logical AW_sim_data_n / AW_sim_log_n everywhere, physical <database>_<file_id>
SELECT DB_NAME(database_id) AS database_name,
       type_desc,
       name AS logical_name,
       physical_name
FROM sys.master_files
WHERE database_id IN ( DB_ID(N'Local_AW'), DB_ID(N'Remote_AW'), DB_ID(N'Rebuild_AW'))
ORDER BY database_name,
         file_id;
GO
/* ---------- 11. Clean-up, when you are done (not run by F5: remove the comment marks) ----------
DROP DATABASE [Local_AW];
DROP DATABASE [Remote_AW];      -- RESTORING: DROP works on it as it is
DROP DATABASE [Rebuild_AW];
-- and delete the folder AW_rollback_sim under the default backup folder
*/