Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL 2008 Tail log

Posted on 2014-09-16
12
Medium Priority
?
177 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 53

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 53

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V 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 53

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 53

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 53

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 53

Accepted Solution

by:
Vitor Montalvão earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

580 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