Set up SQL Server 2005 Reporting DB from SQL Server 2000

Question

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).

Answer 1

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.