For an instructor lead, in-depth look at learning SQL click below.
In this blog post, I am going to describe what SQL Server Database Snapshots are, and how they can be incorporated into your data management strategy. By the end, you will know how to create and make use of these snapshots with practical SQL code examples.
What are SQL Server Database Snapshots?
Database snapshots are read-only views of a SQL Server database. These snapshots provide a full, point-in-time view of your data, allowing you to see exactly what the information in your database looked like at the moment of the snapshot. They are an important tool for backup, troubleshooting, reporting, and other tasks where it might be useful to have an entirely static view of data.
Creating a Database Snapshot
Creating a database snapshot is a straightforward process in SQL Server. Here is a basic structure of how to do it:
|
1 2 3 4 5 |
CREATE DATABASE AdventureWorks_Data_Snapshot ON (NAME = AdventureWorks_Data, FILENAME = 'C:\Snapshot\AdventureWorks_Data_Snapshot.ss') AS SNAPSHOT OF AdventureWorks; |
This SQL code will create a snapshot of the AdventureWorks database named AdventureWorks_Data_Snapshot on disk at the specified file location.
Accessing a Database Snapshot
Once created, a snapshot can be accessed like any other database. However, remember it is read-only.
|
1 2 3 4 |
USE AdventureWorks_Data_Snapshot; SELECT * FROM HumanResources.Employee; |
This queries the ‘Employee’ table within the ‘AdventureWorks_Data_Snapshot’ snapshot, returning all columns and rows. The snapshot refers to the state of the database at the time the snapshot was created; so it reflects the structure and data as it was then.
Reverting to a Database Snapshot
One of the cementing reasons to use database snapshots is that SQL Server supports reverting to a snapshot. If an error occurs and you wish to revert your database to the state when a specific snapshot was taken, you can do so with the following command:
|
1 2 3 4 5 |
USE master; RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT = 'AdventureWorks_Data_Snapshot'; |
This will revert the ‘AdventureWorks’ database to the state it was in when the ‘AdventureWorks_Data_Snapshot’ was created.
Deleting a Database Snapshot
Delete a snapshot when it’s no longer needed.
|
1 2 3 |
DROP DATABASE AdventureWorks_Data_Snapshot; |
This command deletes the ‘AdventureWorks_Data_Snapshot’ snapshot from the system.
Conclusion
Snapshot functionality in SQL Server is a powerful tool that allows developers to safeguard data and troubleshoot more effectively. With the ability to revert to previous states and have a read-only bird’s eye view of data, snapshots prove invaluable to developers and testers alike.