April 18, 2018 at 7:28 am
Hi
Currently we did a full back up of a database every night. The recovery model was set to full. However, I have heard that particularly on a production database it would be set to Full and a Tran log file back up should be done separately. I am just wondering if the Full backup does the log file as well why would I need a separate log back up too. Should I keep the recovery model set to simple and do a full back up every night?
April 18, 2018 at 7:43 am
Yes, you need to back up the transaction log if your database is in Full recovery mode, and not just once a day. The full backup doesn't back up the log as well. The decision on whether to move to Simple depends on your recovery requirements. Please search for Managing Transaction Logs and make sure you understand it - it's an important topic.
John
April 18, 2018 at 7:54 am
Thanks for the quick reply. I had a feeling of that, but then I wonder how they can call it a full backup if it does not include something a s important a s log file. Also, I have been reading in this and I did not see that. So do you have a suggestion as to where I can go for a full explanation.
Thanks
April 18, 2018 at 8:53 am
The main points to consider is how much data is your company willing to loose if there is a failure, and how long is your company willing to wait for you to recover what data you can from backups?
Here's some articles for reference.
from Microsoft:
https://technet.microsoft.com/en-us/library/ms190217(v=sql.105).aspx
from Brent Ozar group:
https://www.brentozar.com/archive/2011/12/letters-that-get-dbas-fired/
from Paul Randal:
https://www.sqlskills.com/blogs/paul/the-accidental-dba-day-6-of-30-backups-understanding-rto-and-rpo/
April 18, 2018 at 9:12 am
Okay thanks, I really appreciate you help here.
Thank You
April 18, 2018 at 12:41 pm
Taking log backups on a database in Full Recovery Mode periodically between Full Backups allows point in time recovery and limits the potential for data loss to the last log backup. On most of our production database we do log backups hourly. This does a couple of things. It limits data loss to that entered since the last log backup and it prevents growth of log files as the VLFs can be "marked" for re-use after the backup. Without log backups in full or Bulk logged SQL will have to expand the log file(s) to add addition VLFs.
April 18, 2018 at 12:48 pm
itmasterw 60042 - Wednesday, April 18, 2018 7:54 AMThanks for the quick reply. I had a feeling of that, but then I wonder how they can call it a full backup if it does not include something a s important a s log file.
It does include the log. It would be impossible to restore the database if it didn't
What it doesn't do is mark the log as reusable, allowing for it to not grow.
What is also doesn't give you is point in time recovery, the ability to restore the DB to any point in time. With the full backup, you can restore the DB to the point it was at when the backup completed. If that's enough, then switch to simple recovery. If it's not, then you need to start taking log backups.
Full backup = Backup of the entire database as of the time that the backup completes
Log backup = record of all the changes that happened to the database between the previous log backup and this one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply