Restore as a script

I am restoring a backup set in sql server 2014. I notice that when I send it to a script, it creates a line for:

BACKUP LOG [X] to Disk....
as well as the

Is the backup log really necessary? what does it do?

Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Steve WalesSenior Database AdministratorCommented:
Your transaction log is a record of all the changes in your database (assuming you're in Full Recovery mode).

Backup log allows you to capture all changes up to the point in time that the backup log completes.

So, let's say you backup at 12:00 and it runs till 1:00.  You then do your backup log and capture any transaction that were in flight or made to parts of the database already backed up etc.

You would then have a complete point in time recovery.

You should be scheduling log file backups frequently during the day anyway.  What's the maximum data loss you're comfortable with?  30 minutes?  Then backup your log every half hour.   Under most circumstances you could still backup the tail of the log and recover to the point in time of failure anyway, but should you lose the drive that your log file is on, you can only recover to the point in time of your last log backup.
Scott PletcherSenior DBACommented:
The log backup right before the RESTORE -- a so-called "tail-log" backup -- allows you to recover the db fully as it was immediately before you restored.  If you don't need to recover the previous data in that db, you don't need to do a log backup.
Anoo S PillaiCommented:
Three points are relevant here

1) why that log backup is taken ?
I think you have generated the script from SQL server Management Studio. Please have a look on the attached screen shot taken from SQL Server 2012 SSMS. The check box "Take tail log backup before restore" is causing the "Backup log" in the script

2) Is backup log is really necessary ?
But It may be useful in some scenarios. Say for example - you are restoring over an existing database. Once the restore is over, the existing database is overwritten from the backup. Assume that at a later point in time , for some reason , you want to get a copy of the original database which has been overwritten by the restore. In this case the original database can be restored to (till ) the latest backup available with you. If you are taking a log back up before overwriting the database, you would be in a position to get all the committed changes till the time of the restore. If that log back up is not taken, you would be getting the transactions committed till last backup.

3) What does it do ?
It would be taking a log backup immediately before the RESTORE operation. ( Indicates , all the transactions after the last backup would be available in this latest log backup and you won't be loosing any committed transactions )

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
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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

From novice to tech pro — start learning today.