Database Snapshot SQL Server 2000 <p>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 entered 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.</p> <p>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.</p> <p>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.</p> <p>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.</p> <p>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?</p>