Need some backup advice

mailguy

Need some advice on the best way to backup the database for a very active forum. The site is completely database driven, so losing the DB is out of the question. Hence why I am looking at Red-Gate to create a near real-time copy of the database and provide a way to recover the database as quickly as possible.

I have a second SQL server that is configured the same as the primary SQL server - 8GB, 2 x dual-core processors, blah blah. Both servers are connected via a gigabit network. Right now the database is less than 2GB in size.

I want to create a near real-time copy of the database on a secondary server. I can do this with log shipping job from the primary to the secondary server. The downside to this is should someone delete data from the primary DB, then these changes are shipped to the database copy. I can delay the restore on the secondary server, but the error might not get noticed until it is to late.

So, is the following possible at the same time? Or is one better than the other?

1) Create log shipping job to update STANDBY database on seconday SQL server. This gets my real-time copy of the database in STANDY mode on the secondary server. The log backups setup to run every 10 to 15 minutes to secondary server.

2) Create FULL backup job on the primary database that runs every hour with DIFF backups every 10 mins. These backups files get copied to a 3rd server for safe keeping. So should errors get shipped to the secondary server I still have access to a FULL backup and 5 or 6 DIFF backups.


#1 saves me if the primary server goes belly up on me. Simply activate the STANDBY database and continue. Might lose a few minutes of posts, but better than the alternative.

#2 saves me if something gets shipped from the primary to the secondary server by mistake - ie dropped table or deleted data.

Yes, I am very paranoid about losing data or the database.


Thanks in advance.
Jon

petey

Both options can be ran at the same time using SQL Backup.

You've certainly addressed the scenario where the primary server goes belly up.

On the scenario where table/data deletion occurred, you could consider using one of those log analysis tools that can tell you what was deleted and when. Red Gate sells SQL Log Rescue (http://www.red-gate.com/products/sql_log_rescue/index.htm), which can analyse SQL Backup log backups. Then with this information, you could:

- re-instate the data on the primary server, assuming data integrity isn't compromised by doing so

- restore the full backup, followed by transaction logs up to the time before the deletion, on the standby server. Once you're happy that the database is ok, backup/restore to the primary server, or promote the standby server to be the primary server.

If using log analysis tools is a feasible option, then you may not need to perform differential backups that frequently. While log backups allow you to restore to a specific point in time, differential backups do not offer that option.

If you are using SQL Server 2005, you might want to consider database mirroring, to address the server failure scenario. You might also want to look at DDL triggers, for its ability to log new/dropped/altered tables.