views:

119

answers:

1

We recently moved from a simple DB recovery model (with daily full database dumps) on our SQL Server 2000 Standard database to full recovery -- combined with weekly full database backups, daily incremental, and transaction dumps every 10 minutes.

Our previous reporting DB instance (SQL Server 2005) was built from the daily backups which no longer exist. I can re-build the reporting database by loading the weekly dump, leaving it in recovery mode, and then restore the incremental backups. Unfortunately, this is not easily scriptable and doing this by hand sucks.

Taking additional full backups of the 2000 production database will ruin the incrementals (which are desirable for a number of reasons).

Is there a better way to do this? We can't do log shipping since we're only SQL Server 2000 Standard (eventually we'll upgrade to 2K5).

+2  A: 

Depending on how up-to-date your data needs to be, snapshot replication seems like the best fit for you. It's not that difficult to set up and I believe that it's fairly common in scenarios like yours.

GregD
I did transactional because the snapshot process is horrifyingly expensive on SQL Server 2000 (exclusively locked tables oh my)
Matt Rogish