Solved

SQL 2008 Tail log

Posted on 2014-09-16
12
162 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 46

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 46

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
 

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 46

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

 
LVL 46

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 46

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 46

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now