Solved

SQL 2008 Tail log

Posted on 2014-09-16
12
165 Views
Last Modified: 2014-09-18
Is there any different between a tail log and transaction log backup in SQL server ?  Are they the same thing in SQL server.

For disaster recovery,
1. restore SQL database and leave it as recovery state
2. restore SQL transaction log
3. restore SQL tail log and leave database as norecovery state.

Can I say #2 & 3# are same the SQL transaction log , using the same log backup procedure ? Tks
0
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40327560
The tail log backup is only the last transaction log backup and in terms of restore process doesn't differs from a normal transactional log restore.

The main difference is when you have a damaged database, you can't run a normal transactional log backup so the option is to perform a tail log backup.
0
 

Author Comment

by:AXISHK
ID: 40327569
Thanks. How can I do it ? Can I perform it through GUI or through command  ? Tks
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40327577
Through command is always possible:
BACKUP LOG DatabaseName
   TO BackupSet
   WITH NO_TRUNCATE;

Open in new window


If the database is damaged I don't know how Management Studio behaves so I can't answer if it's possible via GUI in these cases. For sure it's possible if the database is available.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:AXISHK
ID: 40327582
Is it the setting in the attached file ? But in case the database is crashed, can I still use it.

By selecting the option, the database will be in restoring state. So, it will make the database inaccessible and I normally use it when the database can be recovered, correct ?

Great thanks.
SQL-Backup.png
0
 

Author Comment

by:AXISHK
ID: 40327590
"WITH NO_TRUNCATE"  - is it equivalent to the option highlighted in the attachment ? Tks
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40327594
I'm not familiar with these GUI options. Normally I use T-SQL command. Can't even see a relation with the NO_TRUNCATE option.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40327600
Ok, just checked MSDN.
15.In the Transaction log section, check Back up the tail of the log, and leave database in the restoring state.

This is equivalent to specifying the following BACKUP Transact-SQL statement:
BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

Important
At restore time, the Restore Database dialog box displays the type of a tail-log backup as Transaction Log (Copy Only).
0
 

Author Comment

by:AXISHK
ID: 40328167
So, there are two option to backup a tail log, is the following correct ? Tks

For tail log backup
--------------------------
BACKUP LOG AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Log.bck' WITH NO_TRUNCATE   [or]
BACKUP LOG AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Log.bck' WITH NORECOVERY


For normal log backup
--------------------------------
BACKUP LOG AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Log.bck' WITH TRUNCATE
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40328200
NORECOVERY should always be used for a tail log backup unless the backup is damaged. In this situation you should use NO_TRUNCATE option.
TRUNCATE option it's for normal transaction log backups.
0
 

Author Comment

by:AXISHK
ID: 40329450
For normal log backup,
BACKUP LOG AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Log.bck';

Will it truncate the log ? Or should I explicitly truncate the log with argument "WITH NO_TRUNCATE  "

The following command should be issued when database is corrupted and leave the log file accessible only. Otherwise, I can just use the above statement for recovery, correct ?

BACKUP LOG <database_name> TO <backup_device>
WITH NORECOVERY, NO_TRUNCATE;
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40329783
Yes, the TRUNCATE option is the default if none are set.
Also yes for using NORECOVERY together with NO_TRUNCATE.
0
 

Author Closing Comment

by:AXISHK
ID: 40329839
Great Thanks for your patient :)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question