Keeping the database backed up

[DarkMan]

What is the best way to keep a SQL Server database backed up? We are currenlty using a nightly tape backup, but if the drive crashes, we lose up to 24 hours of data. I know there is a thing called replication in SQL Server, but I'm not sure how to use it. Is this something that I can use to keep an up to the minute copy of the database? If so, how do I set this up? Should I put the copy on a second hard drive, or is the tape back up a good solution?

 

[Warcorp]

First off, the best protection in general is to use a Raid 5 array, however even in the event of a device failure or database failure, a raid 5 array will not protect you. Here is something I use to protect myself from database failure. First, a full nightly backup to tape using an sql agent for your backup software. Second, each morning, I do a full database dump, through the SQL enterprise manager as a scheduled task. Then, every 10 minutes, I dump the databases transactions, appending to the previous database dump each time. At the end of the transaction dump, this file is ftp's off site (it can also be copied to another server or it can be dumped to tape). At the time of the nightly backup, this database dump is stored along with the agent backup and is then cleared when the full database dump happens. Doing it this way allows me to make full, restoreable versions of the data, within 10 minutes of failuer, and also prevents an inordinate amount of system time being dedicated to the backup. Replication can also be used, however, it requires another server. One other advantage of using replication is that you can have a replciated server ready to take over for the primary sql server in case of a failure.

 

[DarkMan]

Thanks...