Database Snapshot In SQL Server

• If we want to restore our database rather than SQL backup file, we can go with database snapshot. It’s a quick way to restore a database.
  
  
• If any user wants to do any kind of manipulation with the data on temporary basis, we can create a database snapshot of the database and let user to use this snapshot instead of the original database. This seems beneficial because the user can perform their manipulation without making any changes in original database. Once done with the activity, user can delete the snapshot.
  
  
• Database Snapshot can be used for reporting purpose.

Creating Database Snapshot

Execute the following code on database CSharpCorner on server to create Database Snapshot. You can refer to my previous article for database creation and table creation script.
After executing above code, SQL Server create .SS file at specified location as shown below.



Refresh the Database Snapshot folder in Object Explorer and you’ll see you created snapshot as in the following:



You can also create multiple snapshots for a single database as shown below.



Now if we make changes in source database, it’ll reflect from the next snapshot we create for the database. Let’s check it by adding a new table to it.
Table is created in our source database, let’s refresh all those snapshots to check whether changes have been made or not.





As we can see in above snapshots, after creatina table in source table, changesare only reflected in snapshot4 database snapshot. In other snapshots, we didn’t see any changes.

Also, if you see, both the files (.MDF and .SS) are of same size i.e. 4 MB. Let’s check both the files properties and see the difference.

CSharpCorner database file (.MDF) file detail.



CSharpCorner Database Snapshot (.SS) file detail.



As we can see, both files are of size 4 MB. But .MDF file is occupying 4 MB space on disk and on the other hand database snapshot file is only taking 128 KB of space on disk.

As we already explained, that SQL Server doesn’t allocate space on disk for snapshot file instead it uses sparefile.

Now, let’s see data of all the tables in datafile and in our newly created snapshot file.
When you’ll execute above 2 queries, you’ll get same output.

Now let’s delete all tables from sourcedatabase with help of the following query.
We’ve deleted all the tables from our source database as shown below.



Now we’ll restore our database from snapshot and we’ll try to bring back those deleted tables.

Restore from Database Snapshot:

As we don’t have any SQL backup file, we’ll try to restore our database from database snapshot only. Execute the following query to begin the restore for database CSharpCornerDB.
On execution, database completes its restore successfully and now if you refresh the database, all three tables are restored successfully as shown below.




Conclusion

This was the article based on Database snapshot, where we saw what snapshot is and under which scenario we can use the snapshot feature. We also createda snapshot of a database and checked how much space they occupy on the disk. Also, we created multiple snapshots for a single database and we updated the source database to view whether changes reflects in old snapshots or not.

I hope this article helped you understand the concept of snapshot in SQL Server. If you have any queries please let me know, I’ll try my best to resolve those.

Please provide your valuable feedback and comments that enable me to provide a better article the next time. Until then keep learning and sharing.