|
From your question it sounds like you're copying the MDF and LDF files from your primary database to your backup database. I'd guess that you're doing this on a nightly basis. You can achieve similar or better functionality using these approaches:
| Approach |
Latency |
| Restore a nightly backup |
Overnight |
| Log shipping |
5 to 60 minutes |
| Transactional replication |
Very quick. A couple minutes |
By latency, I mean the time difference between when an update is posted to the primary database until it is posted to the backup database. The less latency the more similar the two databases are.
The first approach, restore a nightly backup, is similar to the database file copy that you use now. By switching to restoring a backup you can avoid taking SQL Server down.
The second approach, log shipping, depends on making transaction log backup files from your primary database at frequent intervals and then applying them to the backup database. Log shipping is part of the SQL Server 2000 Enterprise Edition. However, it can be achieved in Standard edition through some script writing.
Transactional replication pumps updates from the primary database to the backup database at a very high frequency. It can keep the backup database almost in synchronization with the primary database. However, it uses the most resources.
|
