Backup Exec SQL

[Edu-IT]

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?

[qcomer]

Generally we setup our SQL servers like the following:

OS Drive

Databases Drive

Log File Drive

Backups 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

Edited April 26, 2011 by qcomer

[sukh]

@Edu-IT

 

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.

 

Sukh

[Edu-IT]

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?

[sukh]

correct. Full will only let you recover from your last full successful backup.

[Edu-IT]

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?

[sukh]

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

 

Sukh

[Edu-IT]

Makes sense now, thanks Sukh! :-)

[qcomer]

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.

[Edu-IT]

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.