Database Snapshot SQL Server 2000

We have a large database that receives records concerning several hundred thousand persons per year. For a multitude of reasons I won't get into when information is e开发者_开发问答ntered into the system for a specific person it is often the case that the individual entering the data will be unable to verify whether or not this person is already in the database. Due to legal reqirements, we have to strive towards each individual in our database having a unique identifier (and no individual should have two or more.) Because of data collection issues it'll often be the case that one individual will be assigned many different unique identifiers.

We have various automated and manual processes that mostly clean up the database on a set schedule and merge unique identifiers for persons who have had muliple assigned to them.

Where we're having problems is we are also legally required to generate reports at year end. We have a set of year-end reports we always generate, however it is also the case that every year several dozen ad hoc reports will be requested by decision makers. Where things get troublesome is because of the continuous merging of unique identifiers, our data is not static. So any reports generated at year end will be based on the data as it existed the last day of the year, 3 weeks later if a decision maker requests a report, whatever we give them can (and will) often conflict direcly with our legally required year end reports. Sometimes we'll merge up to 30,000 identifiers in a month which can greatly change the results of any query.

It is understood/accepted that our database is not static, but we are being asked to come up with a method for generating ad hoc reports based off of a static snapshot of the database. So if a report is requested on 1/25 it will be based off the exact same dataset as our year end reports.

After doing some research I'm familiar with database snapshots, but we have a SQL Server 2000 database and we have little ability to get that changed in the short-to-medium term and database snapshots are a new feature in the 2005 edition. So my question would be what is the best way to create a queryable snapshot of a database in SQL Server 2000?

Can you simply take a backup of the database on 12/31 and restore it under a different name?

You either need to take a snapshot and work off it (to another db or external file-based system, like Access or Excel) or, if there's enough date information stored, work from your live copy using the date value to distinguish previously reported data from new.

You're better off working from a snapshot because the date approach won't always work. Ideally, you'd export your live database at the end of the year somewhere (anywhere, really) else.