For an instructor lead, in-depth look at learning SQL click below.
The SQL Server Database Snapshot feature was introduced in SQL 2005 to provide an efficient, Transactionally Consistent Report and a way to revert data. With this feature, you can create a transactionally consistent copy of your database as it existed at the moment of the snapshot’s creation.
What is a Database Snapshot?
A database snapshot is a read-only, static view of a SQL Server database (the source database). The snapshot’s database files are sparse files that borrow space from the source database. As a result, a snapshot always has a size of 0MB until you update the data on your source database.
Creating a Database Snapshot
SQL Server code to create a new database snapshot is simple and straightforward. Here’s an example:
|
1 2 3 4 5 6 7 |
CREATE DATABASE SalesDB_Snapshot ON (NAME = SalesDB , FILENAME = 'C:\MSSQL\DATA\SalesDB_Snapshot.ss') AS SNAPSHOT OF SalesDB; |
This code creates a new database snapshot named SalesDB_Snapshot of the database SalesDB. The file SalesDB_Snapshot.ss will be stored in the directory C:\MSSQL\DATA\.
Working with Multiple Database Snapshots
It is possible to have multiple snapshots of a single database. Each snapshot will be transactionally consistent with the source database as of the moment of the snapshot’s creation. Here’s how you can create multiple snapshots:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE DATABASE SalesDB_Snapshot_0700 ON (NAME = SalesDB , FILENAME = 'C:\MSSQL\DATA\SalesDB_Snapshot_0700.ss') AS SNAPSHOT OF SalesDB; CREATE DATABASE SalesDB_Snapshot_1200 ON (NAME = SalesDB , FILENAME = 'C:\MSSQL\DATA\SalesDB_Snapshot_1200.ss') AS SNAPSHOT OF SalesDB; |
This will create two snapshots, SalesDB_Snapshot_0700 and SalesDB_Snapshot_1200, with each snapshot representing the state of the database at the time the snapshot was created.
Conclusion
Database snapshots allow you to create a transactionally consistent copy of your database in SQL Server. Though they are simple to create, be mindful of disk space. The snapshot grows with the changes made to your source database, and can take up more space than you might expect!