For an instructor lead, in-depth look at learning SQL click below.
In today’s data-driven industries, safeguarding your data is pivotal. Enabling quick recovery and minimizing data loss in the event of an error is crucial and hence, the need for creating point-in-time copies of your databases, known as database snapshots.
SQL Server Snapshots is a feature that provides a read-only, static view of a SQL Server database (the source database). The snapshot persists until it’s explicitly dropped by the DBA. In this blog, I will walk you through how to create SQL Server database snapshots while providing practical SQL code examples.
Prerequisites
To create a database snapshot, the SQL Server must be running at least the Standard edition of SQL Server 2005. Additionally, the user needs CREATE DATABASE, CREATE DATABASE DDL EVENT, or ALTER permission on the server.
Creating a Database Snapshot
Database snapshots are created using the CREATE DATABASE clause with the AS SNAPSHOT OF argument, specifying the source database we need to take the snapshot of.
|
1 2 3 4 5 6 7 |
CREATE DATABASE SalesDB_Snapshot ON ( NAME = SalesData, FILENAME = 'C:\MSSQL\Data\SalesDB_Snapshot.ss') AS SNAPSHOT OF SalesDB; |
In the above example, SalesDB_Snapshot is the name of the snapshot, SalesData is the logical name of the source database, and ‘C:\MSSQL\Data\SalesDB_Snapshot.ss’ is the physical location and the filename of the snapshot.
Viewing a Database Snapshot
To view the snapshot, you can write a simple SELECT command. Remember, snapshots are read-only. We’re selecting from the snapshot as if it were the original database.
|
1 2 3 4 5 |
USE SalesDB_Snapshot; GO SELECT * FROM Customers; |
Dropping a Database Snapshot
To delete a snapshot, we use the DROP DATABASE command, as follows:
|
1 2 3 |
DROP DATABASE SalesDB_Snapshot; |
Conclusion
SQL Server Database Snapshots present a straightforward way to create point-in-time copies of your databases, enabling their fast recovery when the need arises. However, it’s useful to remember that they are adjunct to a good backup strategy, not a replacement. Happy databasing!