1. A Tail log backup is the last Transaction log backup that you make prior to restoring a database.
I2 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.
Take Full backup of database.
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'
15. No restore the FULL backup by using WITH NORECOVERY.
restore database SQLDB from disk='d:\backups\SQLDB.bak'
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.