cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Backup question

VeloTime
Level 4
I have a quick SQL Backup question.
When selecting the "Full" option for SQL Backups are the transaction logs backed up as well?
 
This excerpt from the Admin guide seems to imply that it does.
 
Backup Exec includes three methods for backing up databases: Full, Differential, and Full Copy-only (for SQL 2005 only). The full method backs up the entire database including all system tables and filegroups. The differential method backs up only the changes made to the database since the last full backup. The copy method works in the same manner as the full method, except that it does not truncate the SQL transaction logs.

If the copy method acts the same as the Full, except that it does not truncate the logs, that would mean that the Full job does truncate the logs.

This section of the Admin guide seems to imply that they are two different jobs.

To make sure SQL is completely protected, back up the following on a

regular basis:

The system drive that SQL is on.

The Windows registry and System State.

SQL databases or filegroups. You do not need to back up both.

Transaction logs.

This sounds like the trans log should be backed up separately from the databases. Or, is this just a generic description of what tasks should be performed?

I only need to be able to restore a DB to the state is was during the previous nights backup. No need for point on time restores. 

TIA

 

Message Edited by Adam Stern on 05-04-200711:30 AM

Message Edited by Adam Stern on 05-04-200711:31 AM (formats all messed, sorry)

Message Edited by Adam Stern on 05-04-200711:32 AM

2 REPLIES 2

Jared_S_
Level 6
Employee
Hi Adam -

A full backup of SQL does contain the transaction logs at that time. It is recommended to backup the databases and the logs separately. It is recommended to use these procedures depending on your environment:
 
In small environments, consider running a daily full database backup every evening and daily transaction log backups.
 
In mid-sized environments, consider running a weekly full database backup and daily transaction log backups along with daily differential backups except on the day when the full backup is run.
 
In large environments, consider running daily differential database backups, weekly full database backups, and transaction log backups as necessary. Many shops run full backups on a weekly basis, preferring to run differential backups throughout the week to keep backup run time to a minimum. Extremely large environments may need to run filegroup backups in order to split the full backup over several days. Log backups are required to be able to recover a system from a filegroup backup.
 
I hope this helps!
 
- Jared
 

VeloTime
Level 4
Perfect, that's what I thought was the case. I just wanted to double check.
In our environment being able to restore to the state of the previous nights backup is fine.
 
Thanks,