BACKUP AND RESTORE TAIL LOG

USE master

GO

---------------------------------------------------------

/***** Preparation *****/

---------------------------------------------------------

--BACKUP DATABASE AND LOG

BACKUP DATABASE testdb1 TO DISK = 'C:\DATA\BACKUPSAMPLE_BASE.BAK';

BACKUP LOG testdb1 TO DISK = 'C:\DATA\BackupSample_log1.bak';

BACKUP LOG testdb1 TO DISK = 'C:\DATA\BackupSample_log2.bak';

BACKUP LOG testdb1 TO DISK = 'C:\DATA\BackupSample_log3.bak';

--Some modification after BACKUP.

--It should appear in TAIL LOG.

CREATE TABLE testdb1.dbo.a (id int);

---------------------------------------------------------

/***** A DISASTER happens to the Database File (MDF) ******/

---------------------------------------------------------

--To simulate the disarster, do following:

ALTER DATABASE testdb1 SET OFFLINE

--< NOW DELETE THE MDF FILE IN YOU OS >...

--Now SET Database ONLINE

ALTER DATABASE testdb1 SET ONLINE

    ----Failed because MDF is gone.

--Database status becomes (Recovery Pending) so we can backup Tail Log.

---------------------------------------------------------

/******** BACKUP AND RESTORE TAIL LOG ********/

---------------------------------------------------------

--BACKUP TAIL LOG

BACKUP LOG testdb1

  TO DISK = 'C:\DATA\BackupSample_Taillog.bak'

  WITH INIT, NO_TRUNCATE;

GO

    ----(USE 'WITH NO_TRUNCATE' ONLY WHEN MDF FILE IS DAMAGED)

--Now let's restore our database full backup, and

--all transaction logs including the taillog we just made.

USE master

GO

RESTORE DATABASE testdb1

  FROM DISK = 'C:\DATA\BACKUPSAMPLE_BASE.BAK' WITH NORECOVERY, REPLACE;

RESTORE LOG testdb1 FROM DISK = 'C:\DATA\BackupSample_log1.bak' WITH NORECOVERY;

RESTORE LOG testdb1 FROM DISK = 'C:\DATA\BackupSample_log2.bak' WITH NORECOVERY;

RESTORE LOG testdb1 FROM DISK = 'C:\DATA\BackupSample_log3.bak' WITH NORECOVERY;

RESTORE LOG testdb1 FROM DISK = 'C:\DATA\BackupSample_Taillog.bak' WITH NORECOVERY;

RESTORE DATABASE testdb1 WITH RECOVERY;

----the table [testdb1.dbo.a] is also recovered.

/*-------- REFERENCE -------------------------------------

<How to backup and restore the tail end of a transaction log>

https://www.youtube.com/watch?v=bwuhobz3Z6U

---------------------------------------------------------*/