Did you accidentally delete, update or modify the incorrect data in the SQL database? Use SQL Server database snapshot to revert database changes in SQL Server database.
Suppose you have an application release that includes code changes(object changes), data inserts, and updates. Although you have tested these changes in a lower environment, you want to be sure that if any issues happen after making new changes into production, you should be able to revert to the database at the state just before the release.
How would you revert SQL Server database changes? Well! There are different ways you can use to revert these changes.
Rollback scripts: You should always prepare the rollback scripts before making any changes. For example, if you do alter procedure, make sure you script out the existing stored procedure before running the alter statement.
Backups: You can take database backups before release. However, if you have an extensive size database, backup and restore might take longer.
SQL Server Database Snapshots
The Database snapshots in SQL Server provide a full point-in-time view of a source database. Once you create a database snapshot, SQL Server stores all database changes and copies the source data page(original page) to the database snapshot sparse file.
The following example shows that SQL Server copies the original page into the database snapshot file. You can query the snapshot to view the old data as well.
Image Source: Microsoft
Let's explore the database snapshots in SQL Server practically.
My source database: DBBlogger. It logical data file name is [DBBogger]
I created a new table [Emp] and inserted a new record into it for the demo.
Create table Emp ( ID int, [Name] varchar(20) ) Go Insert into Emp values (1,'Ram')
Run the following script to create a database snapshot.
CREATE DATABASE DBBlogger_DATABASESNAPSHOT_202219032133 ON (NAME =DBBogger, FILENAME = 'C:\Temp\DBBlogger_DATABASESNAPSHOT_202219032133.ss') AS SNAPSHOT OF DBBlogger
The script has following values.
DBBlogger_DATABASESNAPSHOT_202219032133: Database snapshot name
Name: it is the logical file name of the source database. If you have multiple data files, you must specify all file names that you can get from SP sp_helpfile.
AS SNAPSHOT OF <DBName> : Specify the source database name
You can expand the database snapshot folder in SSMS and expand the snapshot. It shows objects similar to the source database.
List Source database and its SQL Server database snapshot details
The database snapshot appears as a database, as shown below.
To identify whether it is a SQL Database or database snapshot, execute the following select statement.
SELECT DB_NAME(sd.source_database_id) AS [SourceDatabase], sd.name AS [Snapshot], mf.name AS [Filename], size_on_disk_bytes/1024 AS [size_on_disk (KB)], mf2.size/128 AS [MaximumSize (MB)] FROM sys.master_files mf JOIN sys.databases sd ON mf.database_id = sd.database_id JOIN sys.master_files mf2 ON sd.source_database_id = mf2.database_id AND mf.file_id = mf2.file_id CROSS APPLY sys.dm_io_virtual_file_stats(sd.database_id, mf.file_id) WHERE mf.is_sparse = 1 AND mf2.is_sparse = 0 ORDER BY 1;
It returns the source database, its snapshot name, logical file name, database snapshot file size on disk.
After the snapshot, let's make some changes to the source database
Create a new table mydemotable
Create table mydemotable ( id int ) insert into mydemotable values (1),(2)
Insert record into existing table
insert into Emp values (2,'newrecord')
Update the existing value in Emp table
Update Emp set [name]='myupdatedvalue' where id=1
We can query the source database and snapshot database for the Emp table. As shown below, source DB contains data after the snapshot creation, and the snapshot file shows the old data; it still has one record for the Emp table.
Similarly, if we query the [mydemotable], it gives an error for the snapshot because we created this table after the database snapshot.
Revert to the database snapshot
Due to some reasons, we want to roll back the release. Therefore, the database should be in a state of a database snapshot.
To revert, use the RESTORE DATABASE statement with cause DATABASE_SNAPSHOT, as shown below.
Use Master GO Restore database DBBlogger from DATABASE_SNAPSHOT='DBBlogger_DATABASESNAPSHOT_202219032133' GO
Once you revert to the source database with a database snapshot, you can verify the objects and their values.
Drop SQL Server database snapshot
You should not keep database snapshots for longer due to the following reasons.
It increases IO and SQL Server work to copy the original pages into the sparse (snapshot) file
The sparse file might grow over time, and it might cause disk space issues.
The restoration process might take longer if the sparse file size is enormous.
DROP DATABASE DBBlogger_DATABASESNAPSHOT_202219032133;
Note: You should refer to Database Snapshots (SQL Server) for Limitations on the Source Database and Database Snapshots.