cancel
Showing results for 
Search instead for 
Did you mean: 

Backup SQL 2005 nightly?

wingnut144
Level 4

What is the best mode to do a nightly backup of our SQL 2005 server?  I have a weekly full backup of all our systems, and in that case I have the database backup set to full.

But I don't see a good option for a nightly backup of data, and in the job, I'm getting:

Database xICL is configured to maintain transaction logs.  Transaction log backups are not being performed.  This will result in the log growing to fill all available disk space.  Regular log backups should be scheduled or the database should be changed to the simple recovery mode.

I'm not a DBA, so I'm trying to figure out the best way to handle this without screwing up the database! :)

Suggestions?

Thanks
 

1 REPLY 1

Rob_R
Level 3

Hello,

From your description the database is in Full Recovery mode, which means it maintains a log file which will grow until it is backed up. When a log file is backed up, the committed transactions are removed (truncated.)

Backing up a SQL dB in Full Recovery mode involves two operations. A Full dB backup and the backup of the logs.

The purpose of Full recovery and regular log back ups is to let you recover to a specific point in time. So typically, you would do log back ups during the day, then a Full Backup at night. How often you do log backups determines how granualar you can get for the restore operation. The combination allows you to recover to a point in time.

Your current full back up of SQL does capture the entire database, but it is not backing up the logs, therefore they are not being truncated. So they will grow until they consume the entire disk and everything stops.

You need to know if a point in time recovery is required, or if you merely need to be able to fall back to the most recent full backup.

If you do not need to recover to a point in time, then the dB can be set to the "Simple" recovery mode, which automatically truncates the log on a regular basis, thereby limiting its growth. However, if the log is already several GB in size, you will have to shrink it manually after that change.

So you really need to understand more about the requirements of the particular database and what is expected for recovery.

Hope that helps a little.

Rob