SQL 2008 Tail log

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
AXISHKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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
AXISHKAuthor Commented:
Thanks. How can I do it ? Can I perform it through GUI or through command  ? Tks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

AXISHKAuthor Commented:
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
AXISHKAuthor Commented:
"WITH NO_TRUNCATE"  - is it equivalent to the option highlighted in the attachment ? Tks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
AXISHKAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
AXISHKAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, the TRUNCATE option is the default if none are set.
Also yes for using NORECOVERY together with NO_TRUNCATE.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AXISHKAuthor Commented:
Great Thanks for your patient :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.