Friday, April 29, 2011

Taking tail log backup


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.
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.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

SQL Server Error Logs

* Error Logs maintains events raised by SQL Server database engine or Agent. * Error Logs are main source for troubleshooting SQL Server...

SQL Server DBA Training

SQL Server DBA Training
SQL Server DBA