Software

Ask something
Discussion Locked

SQL Server 2000 backup schedules

0 helpful
2 replies

by ebarsamian

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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!

2 replies

by tatz12

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.

by jbogart23

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:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_03_378k.asp

This discussion is closed to new replies.