SQL Server 2005 Backup strategy

Question

I manage a web application for a client with the following specs:

• ASP.net 3.5 running on a Virtual Windows 2003 Web Server
• SQL Server Standard hosting the database
• Database current size of 6Gb, with 1Gb/month growth rate
• One single table is responsible for 98% of the size, holds the most critical data for the client
• Log is not kept for this big table, only selects are done in this table
• 50 Gb FTP space avaiable for backup

Considering this scenario, what would be the best strategy for a SQL Backup and what tool would be best suited for this task (commercial applications included, client can pay for the license fee)?

Answer 1

It depends how critical is the data. Here is however how I i'd do it. 1. Run a full backup every day. 2. Run a differential backup every 4 hours. 3. Run a transactional log backup every 15 minutes 4. Keep a copy at the site and move a copy off the site as well as soon as the backup is done.

The database is not too big, and this is easily doable.

Use a third party tool like Redgate SQL Backup and it will automatically compress and encrypt the database backup for you. I have used it extensively and am a big fan.

Additionally if you another site available, and the data is very critical, you might want to think about setting up log shipping as well.

Answer 2

I was supposed to type in my answer to your question but I realized there are lots of far greater resources somewhere like this article in SQLServerCentral.com. You can also find lots of "Best Practices on Backup" like this one.

Answer 3

You might also want to take into consideration how much data you can afford to lose and how long it will take you to restore the database. Your client may decide that they never want to lose more than 15 minutes of data ever, or they may decide that losing up to a days worth of data is okay with them.

Answer 4

Here is the strategy we use for CodePlex.com:

• All SQL servers run with a peer server using SQL mirroring
• Weekly full backup (stored on separate drive from databases)
• Daily differential backup (stored on separate drive from databases)
• Transaction log backup every 5 minutes (stored on separate drive from databases)
• Daily tape backup
• Tape backups taken offsite weekly

Also very important test your backups! Studies have shown that over 30% of untested backup procedures are flawed. Here is our backup testing strategy:

• Every 30 minutes verify the full backup file exists (using scheduled task)
• Every 30 minutes verify the differential backup file exists (using scheduled task)
• Every 30 minutes verify the transaction log backup file exists (using scheduled task)
• Every 30 minutes verify the database mirroring is configured (using scheduled task)
• Every day, do a test restore of the full+differential backup and report the table row counts (using scheduled task)
• Once a month do a test restore of the most recent tape backup and verify the data

Answer 5

This is a VPC? Can you install apps?

http://www.jungledisk.com/

That's what we use - make a sql job that pushes out a backup every day, then use that service to push a copy back to Amazons S3 service. If not maybe you could have a local app that pulls the backup to a machine then pushes it /w S3 webservice, or still using Jungledisk.

This is important! If your app goes down it hurts! Also make sure you backup your deployed app and resources stored there... i.e. uploaded content to your apps storage directory.