SQL Backup Plan

[MattK]

Hi,

I need some advice on a backup plan for our MS SQL 2000 server.

We only have one database hosted on it (used by an application called Goldmine, which is a CRM system). As the database is small (less than 500mb), and we can handle losing up to a day's worth of data, we have decided to do a complete DB backup once a day (to file), which is then copied to tape later the same day (along with a bunch of other backups).

My question is regarding transaction log backups: considering we are backing up the entire DB once a day, do we need to backup the transaction log at all? I understand what the transaction log does, but I do not understand how it relates to backups. Will the TLog get cleared every time the daily complete backup runs? If so, do we need to back it up at all? If not, why does it need backing up and how often?

Sorry if the questions are a bit "newbie", I've never been in charge of backing up an SQL server before.

Cheers,
Matt

 

[SQLBill]

The main purpose of backing up the transaction log is to do a 'point-in-time' restore.

Let's say you've been backing up your TLogs. You accidentally run a DELETE command and delete the wrong information at 1 PM. You can RESTORE the full database backup WITH NORECOVERY and then RESTORE the log WITH STOPAT '2003-12-17 12:50 PM'. And that will restore all the data prior to you deleting the data. Then you can go from there and delete the correct data.

But as you say, you don't care about losing data. Well then, in Enterprise Manager right click on the database, select Properties, go to the Options tab and change the Recovery Mode to SIMPLE.

-SQLBill

 

[MattK]

Thanks Bill,

What effect does turning the recovery mode to SIMPLE have? Does it turn off journalling (logging) completely, or just prevents restores from logs?

Cheers,
Matt

Mathew Kilham
Network Administrator
Powerchip Australia Pty Ltd
mkilham(at)powerchipgroup(dot)com

 

[sheong]

You are not able to backup the transaction log if you turn it to SIMPLE mode. Anything happen to ur server, you are only able to restore the last backup information.

If you not sure about SIMPLE mode, please use FULL mode. And try to backup the transaction log as often as possible. In case anything happen, you are able to restore back until Point-In-Time in transaction log.