Network Administration: SQL Server 2000 backups; Things to Consider

So you read my last post, and now you’re sitting there wondering… “What’s the backup strategy for our SQL servers?” Maybe you assumed that the DBA or a SQL developer was responsible for this. Or maybe, like me, you’re assessing a new customer’s infrastructure. Whatever the case might be, your next step is to determine how you want to approach the SQL database backups.

Important Factors to Consider:

1) How much data can I afford to loose, and how critical is the information in the database?
2) What is an acceptable time period to perform maintenance when a disaster happens?
3) How large is the database, and is this a 24x7 database?

If your answers look anything like “all of the data is business critical, we don’t have a maintenance window”. Then you might have a good argument for getting some training, or potentially bringing in a DBA. However, if you’re organization is like many small to mid-sized organizations, you’ll need to figure this out on your own.

Best Practices:

1) Perform regular restores of databases to verify restorability
2) Schedule time for a simulated disaster
3) Document the recovery procedures
4) Review event logs for potential hardware problems
5) Perform a complete database backup weekly or monthly
6) Perform a differential backup nightly
7) Perform transaction log backups every 30 minutes

In a follow-up post, I’ll add some technical details on the backup/restore model, including what options are available, as well as what I recommend.