Taking tail log backup

Taking tail log backup

1.  A Tail log backup is the last Transaction log backup that you make prior to restoring a database.

2.  If your db crashes for whatever reason, you have to backup your transaction log so that you can do point in time recovery.

3.  It is possible only

a.   If the recovery model of database is other than SIMPLE.

b.   If the transaction log file was not damaged physically.

c.    If there is FULL backup exists.

Steps: Taking tail log backup of SQLDB database.

1.      

      Take Full backup of database.

use master

go

backup database SQLDB to disk='d:\backups\SQLDB.bak'

2.       Insert some data into any one table of SQLDB database.

3.       Stop server

4.       Go to database files folder. Here

            C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

5.       Move the Data File to another location.

6.       Restart the instance.

7.       Now database goes into suspect mode.

8.       Stop the server.

9.       Copy Transaction Log file into different location. For example d:\Log folder.

10.   Start server.

11.   Create a new database with same name i.e. SQLDB. By deleting existing one.

12.   Copy the old log file and over write the newly created log file of the database by stopping server.

13.   Start server again.

14.   Now take the tail log backup

    backup log SQLDB to disk='d:\backups\sqldb_tail.bak'

    with no_truncate

15.   No restore the FULL backup by using WITH NORECOVERY.

restore database SQLDB from disk='d:\backups\SQLDB.bak'

with norecovery

16.   Restore the Tail Log backup WITH Recovery

restore log SQLDB from disk='d:\backups\sqldb_tail.bak'

17.   Observe the changes made after FULL backup.