For an instructor lead, in-depth look at learning SQL click below.
In today’s article, we’ll be diving into the practical approach of how to create read-only copies of your database using SQL Server Database Snapshots. We’ll comprehensively learn what a SQL Server Database Snapshot is and how it can be beneficial for maintaining your databases.
What is SQL Server Database Snapshot?
A SQL Server Database Snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation. It’s a perfect tool for safeguarding data or for reporting purposes as it provides you with a consistent view of data that won’t change over time.
Creating a Database Snapshot
Creating an SQL Server Database snapshot isn’t a complicated process. Below is an example of how you can create a snapshot of your database. It’s as simple as creating a new database with an AS SNAPSHOT OF clause, specifying the source database.
|
1 2 3 4 5 6 7 8 9 |
CREATE DATABASE SalesDB_Snap ON ( NAME = SalesDB, FILENAME ='C:\Program Files\Microsoft SQL Server\...\SalesDB_Snap.ss' ) AS SNAPSHOT OF SalesDB; |
In this example, a snapshot of the SalesDB database is created and the new snapshot database is named SalesDB_Snap. The FILENAME specifies the physical file that will store the snapshot data.
Querying a Database Snapshot
Querying a database snapshot is the same as querying a standard database. Below is an example of a SELECT statement querying a snapshot database:
|
1 2 3 |
SELECT * FROM SalesDB_Snap.dbo.Orders; |
This query will return all records in the Orders table from the snapshot database, SalesDB_Snap.
Reverting to a Database Snapshot
Another handy feature SQL Server offers is the ability to revert to a snapshot at any time. This feature provides a fast and simple rollback mechanism. Here is an example:
|
1 2 3 4 |
RESTORE DATABASE SalesDB FROM DATABASE_SNAPSHOT = 'SalesDB_Snap'; |
This command reverts the SalesDB database to the state it was in at the creation of the SalesDB_Snap snapshot.
Conclusion
In conclusion, SQL Server Database Snapshots provide a valuable tool for creating read-only, static views of a database, ideal for both data protection and reporting. With their easy creation, querying, and reverting mechanism, they indeed live upto the expectations.
Remember, it’s always a good practice to experiment with such features in a development environment before using them in production. It ensures that you’ve a solid understanding of both the benefits and any potential issues or limitations.