I have been working in environments where users/analysts would like to view the state of the records in a database at a particular point in time or would like to analyse values during a particular day. Usually these kind of requests some time come up after the day in question to be analyzed has passed. In order to handle this requirement I suggested the use of database snapshots to do perform analysis. The database snapshots can be used to test performance of queries without affecting the Primary database. The database snapshots in Sql server. Please use the following link to get a understanding of how the database snapshot works in SQL Server. This feature has come in handy for me to allow the users to perform certain adhoc analysis.
The article in the link below has been written by Dinesh Asanka .
Here is a basic syntax for creating a Database Snapshot:
CREATE DATABASE ssAdventureWorks_dbss2230 ON
( NAME = AdventureWorks_Data, FILENAME =
‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_2230.ss’ )
AS SNAPSHOT OF AdventureWorks;