Solved

SQL 2008 Tail log

Posted on 2014-09-16
12
164 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
  • 6
  • 6
12 Comments
 
LVL 48

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 48

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 48

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 48

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 48

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 48

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

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.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

820 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