General discussion


SQL Server 2000 backup schedules

By ebarsamian ·
We recently upgraded to SQL Server2000 and the backups I inhereted were less than optimal to say the least. It basically is just a full backup every morning and evening and a few on the weekends.

I'm looking for some advice or even a 3rd party tool (if one exists) to analyze/optimize our SQL data backups, to create better, more efficient backups.

Or is a simple, periodic full database backup (full db, differential, & transaction logs) sufficient? And how often should this be done?

Any advice is greatly appreciated, thanks!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Backup Sched

by Tatz12 In reply to SQL Server 2000 backup sc ...

I would suggest backing up the systems database(model,master,msdb) seperate from any production db.

Do full backups to local disk once a night during low activity. Do transactional backups every 15 minutes or what you consider revelant to you business model.

Have a third party software like veritas back up the local directory where your sql backups are stored nightly to tape.

Collapse -

I agree, here's a little more detail

by jbogart23 In reply to Backup Sched

As Tatz12 says you should create two seperate maintenance plans, one for system data bases and one or more for the user databases.

On the User database maintenance plan you'll need to monitor the amount of data being transfered to and from you server and adjust the transaction backup accordingly.

If you have databases that maintain dynamic critical business data, you will need to consult with users and management to come up with a backup period that is suitable for the data. You'll want to create a seperate maintenance plan for these databases.

Maintenance plan creation by Microsoft:

Related Discussions

Related Forums