How to revert database changes using SQL Server Database Snapshot

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.