Once you run the following script, you can manipulate the database file storage location. On this example, I placed these files in the C:SQL Server folder. On my computer, the size of the data file is 1,216KB when the database is initialized and the size after the data is loaded is 7,360KB. Since some data in the source database is already inside, we can now create a snapshot. Use the following script to create a database snapshot.
CREATE DATABASE SnapshotDatabase
ON
(
NAME = 'SourceDatabase_Data',
FILENAME = 'C:'
) AS SNAPSHOT OF SourceDatabase
This syntax for creating snapshots is very similar to that for creating a database. Two main differences: the first difference is the AS SNAPSHOT OF SourceDatabase statement, which indicates on the server instance which database will be used as the source database for the snapshot; the second difference is that in fact, the database snapshot will not generate log files. Because without data manipulation things will only happen on read-only databases, there is no and no log is needed.
This database script creates a sparse file named (the extension of the data file does not require mdf). If you operate this file in WINDOWS Explorer and look at its properties at the same time, you will see that the file is the same size as the source database file; however, the size on the disk is actually close to zero. At this time, the database snapshot does not have its own data.
You can run the script I used above and insert 10,000 rows of data into the SourceDatabase database into the SalesHistory table. At this point, my SourceDatabase database size is 12,480KB, and at the same time, my SourceDatabase database size on disk is now 448KB. At this time, the data pages changed in the SourceDatabase database have been copied to the SnapshotDatabase database, which can explain why its size has been increased on the disk.
Summarize:
Database Snapshots allow you to create read-only databases for reports, and you can restore your source database to your database snapshot if necessary. Similarly, you can create as many database snapshots as you wish based on the purpose of your report.
It is particularly worth noting that these database snapshots will occupy disk space. If there are too many database snapshots, it will quickly fill your disk array, especially in a product environment. If the data is often updated, it will be easier to fill the disk array.
Additionally, using database snapshots will reduce the performance of the database, because copying the data page as a write operation in the database increases the database input/output.
Although there are these small disadvantages, if you can propose a good database snapshot creation solution for reports, this new feature of SQL Server 2005 will be used by more people.