backup_restore_example.md

-- Author : Kin -- e.g https://dba.stackexchange.com/a/284353/8783

create database testkin
sp_helpdb testkin

-- change db to full recovery

alter database testkin SET RECOVERY FULL

-- take full backup

backup database testkin to disk = '/var/opt/mssql/testkin_full.bak' -- create table

create table tbl01 (fname char(1))

-- take diff backup

backup database testkin to disk = '/var/opt/mssql/testkin_diff_01.diff' with differential

-- insert some data

insert into tbl01 (fname) values ('k')

-- take diff 02 backup

backup database testkin to disk = '/var/opt/mssql/testkin_diff_02.diff' with differential

-- insert some more data

insert into tbl01 (fname) VALUES ('i')

-- take log backup

backup log testkin to disk = '/var/opt/mssql/testkin_log_01.trn'

-- insert some more data

insert into tbl01 (fname) VALUES ('n')

-- take log backup

backup log testkin to disk = '/var/opt/mssql/testkin_log_02.trn'

-- insert some more data

insert into tbl01 (fname) VALUES ('s')

-- take diff backup 03

backup database testkin to disk = '/var/opt/mssql/testkin_diff_03.diff' with differential

-- insert some data

insert into tbl01 (fname) VALUES ('z')

-- log backup

backup log testkin to disk = '/var/opt/mssql/testkin_log_03.trn'

------ restore (use sp_restoregene to give you correct order scripts)

1. Use ONLY log backups to restore the database (FULL + LOGS)

sp_restoregene @database = 'testkin'
        , @ExcludeDiffAndLogBackups = 3 -- -- Default 0, 0=full+diff+ log, 1=full only, 2-full+diff, 3=full+log, 4=diff+log
        , @WithReplace = 1 -- will replace kintest db

Above will generate

alter database testkin set offline with ROLLBACK IMMEDIATE
RESTORE DATABASE [testkin]  FROM DISK = N'/var/opt/mssql/testkin_full.bak'  WITH REPLACE, FILE = 1, NORECOVERY, STATS=10  
RESTORE LOG [testkin]  FROM DISK = N'/var/opt/mssql/testkin_log_01.trn' WITH NORECOVERY,  FILE = 1 ,STOPAT = '2021-01-31 02:42:11'
RESTORE LOG [testkin]  FROM DISK = N'/var/opt/mssql/testkin_log_02.trn' WITH NORECOVERY,  FILE = 1 ,STOPAT = '2021-01-31 02:42:11'
RESTORE LOG [testkin]  FROM DISK = N'/var/opt/mssql/testkin_log_03.trn' WITH NORECOVERY,  FILE = 1 ,STOPAT = '2021-01-31 02:42:11'

2. Use Full backup + last Diff + log backup

sp_restoregene @database = 'testkin'
        , @ExcludeDiffAndLogBackups = 0 -- -- Default 0, 0=full+diff+ log, 1=full only, 2-full+diff, 3=full+log, 4=diff+log
        , @WithReplace = 1 -- will replace kintest db

this will generate below script :

alter database testkin set offline with ROLLBACK IMMEDIATE
RESTORE DATABASE [testkin]  FROM DISK = N'/var/opt/mssql/testkin_full.bak'  WITH REPLACE, FILE = 1, NORECOVERY, STATS=10  
RESTORE DATABASE [testkin]  FROM DISK = N'/var/opt/mssql/testkin_diff_03.diff'  WITH FILE = 1, NORECOVERY, STATS=10 
RESTORE LOG [testkin]  FROM DISK = N'/var/opt/mssql/testkin_log_03.trn' WITH NORECOVERY,  FILE = 1 ,STOPAT = '2021-01-31 02:42:27'