what is the main difference between transaction log and backup the tale log backup

Question

what is the main difference between transaction log and backup the tale log backup

dastagiri16

Hall of Fame

Points: 3544
More actions
May 30, 2011 at 9:52 am

#237180

hi,
could you plz clarify
1.Main difference between transaction log and backup the tale log backup.
2.I take backupe tail log backup my database is in restore mode how can i get back to normal mode ?
Regards:
Dastagiri.D

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

1) Please expand your question. Are you asking about the difference between the actual log file (.ldf) and a tail log backup? If so, in what way, because it makes no sense

2) RESTORE DATABASE <dbName> WITH RECOVERY

Note that taking a log backup WITH NORECOVERY is usually only done just prior to starting a restore.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

1) I suspect you mean the difference between a transaction log backup & tail log backup ?

Tail Log Information From Microsoft - http://msdn.microsoft.com/en-us/library/bb283410.aspx:

"In most cases, under the full or bulk-logged recovery models, SQL Server 2005 and later versions require that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.

When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.

SQL Server 2005 and later versions usually require that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact. However, not all restore scenarios require a tail-log backup. You do not have to have a tail-log backup if the recovery point is included in an earlier log backup, or if you are moving or replacing (overwriting) the database and do not need to restore it to a point of time after the most recent backup. Also, if the log files are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost. For more information, see "Restoring Without Using a Tail-Log Backup," later in this topic. "

Gaurang-Patel SSCrazy Points: 2109 More actions · Answer 3

dastagiri16 (5/30/2011)
hi,
could you plz clarify
1.Main difference between transaction log and backup the tale log backup.
2.I take backupe tail log backup my database is in restore mode how can i get back to normal mode ?
Regards:
Dastagiri.D

First let me answer your first question.

1.In most cases, under the full or bulk-logged recovery models, SQL Server 2005 and later versions require that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.

Reference: http://msdn.microsoft.com/en-us/library/ms179314.aspx

2.As you said you take tail log back up and it database is in restore mode

i think you might end up with restoring state as you might have taken backup log

by following SQL code

BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

Now you want to take your database to original (normal ) mode

Please Run following statement,you will get your database in Recovery mode.

Restore DATABASE dbname with Recovery

http://www.sqlstairways.com