Windows Thread, Backup Exec SQL in Technical; A full SQL backup doesn't backup and truncate logs however from what I understand these logs are only needed if ...
26th April 2011, 01:45 AM #1
Backup Exec SQL
A full SQL backup doesn't backup and truncate logs however from what I understand these logs are only needed if you want to restore to a particular point in time. Correct?
If that is the case then will a full daily backup with a single log file backup daily suffice? How do you guys do it?
26th April 2011, 04:23 AM #2
- Rep Power
Generally we setup our SQL servers like the following:
Log File Drive
These are all virtual disks configured on different LUNs (which have different RAID arrays) depending on which it is. We run a full backup daily and transaction log backups every hour. We then backup the data on those drives using Avamar to a location off site.
The idea being with the daily backup and hourly log files is we only have to manage 1 full backup and then we can use the log file backups to restore up to the last hour it backed up before it went down. We use Avamar to backup all of the drives to an alternate location incase of disaster (we are in a mountain region that frequently has fires, snow, plenty of senior citizen drivers (lol) and everything else lol).
Having all your bases covered makes you sleep well at night
Last edited by qcomer; 26th April 2011 at 04:26 AM.
26th April 2011, 09:10 AM #3
I assume you are using a full recovery model. Therefore, your thought is correct. Simply doing a full backup won't mean log truncation will occur. You have to backup the log files. If you're not concerned about point in time recovery then you can backup the logs to disk and keep for x days which will then delete the logs. Then continue with your normal backups in additon to your transaction logs backups for x days and deletion after x days.
26th April 2011, 02:54 PM #4
So in this case, the 'full' backup of the database will be sufficient to restore to the time the full backup was taken? The log files won't be needed really unless you're going back to a particular point in time?
26th April 2011, 02:59 PM #5
correct. Full will only let you recover from your last full successful backup.
26th April 2011, 03:45 PM #6
So with regards to SQL in general, all the transaction log does is keep a track of the changes that are made to the actual database file?
26th April 2011, 03:47 PM #7
Correct. Any transaction get's written to logs then is replayed into the DB. So after your full back on 12pm on Mon, you dont need the logs to recover up to 12pm Mon. Between Mon 12pm and Tue 12pm, all information is in the logs and written to the DB. On Tue at 12pm you have another full backup. Now you can recover as pf Mon 12pm or Tues 12pm.
If you wanted to recover say Mon at 3pm and disregard all other transactions then you cant.
Also, depends how critical your DB is to you. Say you wanted to recover up to Monday 5pm on a Tue, as you realised transactions after 5pm transactions were wrong. You could only roll back to 12pm Mon and you will lose all the other transaction up to 5pm Mon and would have to make those again (no transaction log backups).
26th April 2011, 03:49 PM #8
Makes sense now, thanks Sukh! :-)
26th April 2011, 04:31 PM #9
- Rep Power
May I ask what your DB is hosting? Our is our SIS system (student information system) and also our transportation system (bus) so we like to be able to restore atleast up until the hour. If any of that data goes missing beyond that it can really cause havoc and be a nightmare.
Our help desk software runs on SQL also but we dont do nearly that intensive of a disaster recovery strategy on it since it is not as mission critical.
26th April 2011, 05:24 PM #10
The biggest database is 1.5GB in size so in theory I see no reason why we cannot run a full backup multiple times each day? We backup to external media and my concern with transaction log backups is keeping track of which cartridge has what.
By joe90bass in forum Windows
Last Post: 5th May 2010, 02:49 PM
Last Post: 12th April 2010, 10:21 AM
By Grommit in forum Windows
Last Post: 6th January 2010, 11:59 AM
By albertwt in forum Thin Client and Virtual Machines
Last Post: 3rd November 2009, 01:05 AM
By LordGrey in forum How do you do....it?
Last Post: 27th September 2006, 05:44 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)