SQL Server Database Save Point for Recovery

By: Jim Evans | Updated: 2022-11-23 | Comments (2) | Related: > Restore

Problem

Often as part of SQL Server database code deployment, a pre-step will call for a database backup to occur to establish a restore point. This step may take a long time for large databases, adding significant time to the deployment window. What are the options for creating a database Save Point before running a database deployment?

Solution

In this tip, I will present options for creating a SQL Server database save point, thereby establishing a recovery point that can be completed before database deployments in case a rollback is needed. When planning for a database deployment, it is essential to consider and plan for a rollback of the deployment should any major issues occur, that cannot be easily resolved. I'll highlight the pros and cons and provide code examples of each recovery method covered. Here are the save point options that I will cover.

Options:

Full database backup
Differential backup
Database snapshot
Transaction log backup
Mark transaction

SQL Server Full Backups

The full backup is the most extreme method and will take the longest. Though a full backup is a snapshot of the time the backup starts, often folks wait for the backup to complete to ensure the backup is successful. Full database backups are highly successful as long as the backup has adequate disk space! Also, you can do a regular full backup restarting the backup chain or do a copy-only backup, which does not affect the backup chain. A copy-only backup is a good option when using a third-party backup solution where the restore may not be easily obtainable. A full backup is a good option for small databases. However, for large databases, a full database backup may significantly add to the deployment window and is often overkill, especially if the actual deployment takes only a few minutes.

Example: Full Database Backup Save Point

This example will show how to do a copy-only backup for the save point. An example of recovering the database by restoring the copy-only backup is provided.

--1. FULL Database Backup Prior to Database Deployment
BACKUP DATABASE [AdventureWorks]
TO DISK = N'C:\Backup\AdventureWorks_Copy_Only.bak'
WITH INIT, COPY_ONLY, NAME = N'AdventureWorks-Full Copy Only Database Backup', STATS = 10;
GO

Rollback Full Backup Save Point

How to revert with a Full Backup:

--2. If rollback of the Database is needed
RESTORE DATABASE [AdventureWorks]
FROM DISK = N'C:\Backup\AdventureWorks_Copy_Only.bak'
WITH FILE = 1, RECOVERY, STATS = 10;
--Note WITH Recovery
GO

SQL Server Differential Backup

A differential backup is another option. The advantage of using a differential is that the backup should take less time than a full backup, as the differential only backs up all changes since the last full backup. The backup time and size increase as you get further from the full backup. An advantage of a differential backup is that it does not break the backup chain. Differential backups are faster and smaller than full backups making this a good option. This works if the database is in full, simple, or bulk-logged recovery model. However, the restore process requires restoring the last full backup and then the differential backup.

Example: Differential Database Backup Save Point

Note: This example does not use a copy-only backup as it cannot be combined with a differential backup restore.

--1a. FULL Database Backup Prior to Database Deployment
BACKUP DATABASE [AdventureWorks]
TO DISK = N'C:\Backup\AdventureWorks.bak'
WITH INIT, NAME = N'AdventureWorks-Full Database Backup', STATS = 10;
GO
 
--1b. DIFFERENTIAL: Run before Database Deployment
BACKUP DATABASE [AdventureWorks]
TO DISK = N'C:\Backup\AdventureWorks_Diff.bak'
WITH DIFFERENTIAL, FORMAT, NAME = N'AdventureWorks-DIFF Database Backup', STATS = 10
GO

Rollback Differential Save Point

Reverting the deployment with a differential backup requires a restore from the full backup, then restoring the differential backup with recovery.

--2a. Rollback: If rollback of the Database is needed, first restore the most recent FULL backup and then restore the Differential!
RESTORE DATABASE [AdventureWorks]
FROM DISK = N'C:\Backup\AdventureWorks.bak'
WITH NORECOVERY, REPLACE, STATS = 10;
--Note WITH NO Recovery!
GO
 
--2a. Rollback: Restore the Differential with Recovery
RESTORE DATABASE [AdventureWorks]
FROM DISK = N'C:\Backup\AdventureWorks.bak'
WITH FILE = 1, RECOVERY, STATS = 10;
--Note WITH Recovery!
GO

SQL Server Database Snapshot

Database snapshots are a good option for a temporary save point before a database deployment. A snapshot is a capture of the database as it was at the point in time of the snapshot creation. Using a database snapshot will give you the ability to undo your changes. A database snapshot is initially very small but takes up more space as the database changes. The maximum size that the snapshot can grow to is the size of the original database at the time of the snapshot. Because of this, it is not good for long-term retention. Another benefit of a snapshot is that you can query it immediately to see data and SQL objects as they were at the time of the snapshot.

Example: Database Snapshot Save Point

Creating a snapshot requires specifying the logical name of every file of the source database. Here is the database snapshot create code:

--1. Create a Database Snapshot
CREATE DATABASE AdventureWorks_SnapShot20221001 ON
(NAME = AdventureWorks2014_Data, FILENAME = 'C:\SQLDB\AdventureWorks_SnapShot20221001.ss')
AS SNAPSHOT OF AdventureWorks;
GO

Rollback Snapshot Save Point

How to revert from a database snapshot:

--2. Rollback to the Snapshot prior to the code change
USE master;
RESTORE DATABASE AdventureWorks from
DATABASE_SNAPSHOT = 'AdventureWorks_SnapShot20221001';
GO

Next, drop the snapshot when it is no longer needed.

--3. Must be sure to Delete the Snapshot.
DROP DATABASE AdventureWorks_SnapShot20221001;
GO

SQL Server Transaction Log Backup Point in Time

Another option is using a transaction log backup as your recovery point. One option is to note the deployment start time. This start time will be the database recovery point used to recover the database if a rollback is required. Another option is to issue a new transaction log backup noting the backup file name so it can be referenced later if a recovery is needed. To recover, a previous full database backup would be required in addition to all log files through the noted or new transaction log backup file taken before the deployment. This is not an option if the recovery model is simple. Also, if you have an existing third-party backup solution that backs up to a special storage device, going outside of that solution could make recovery more complex as the recovery may need to include your transaction log backup.

Example: Database Transaction Log Backup Save Point

USE [master]
BACKUP LOG [AdventureWorks] 
TO DISK = N'C:\Backup\AdventureWorks_LogBackup_2022-10-23_16-31-57.bak' 
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks_LogBackup_2022-10-23_16-31-57', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 5;
GO

Rollback Transaction Log Backup Save Point

How to revert from a database transaction log backup:

RESTORE DATABASE [AdventureWorks] 
FROM DISK = N'C:\Backup\AdventureWorks.bak' 
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5;
GO
RESTORE LOG [AdventureWorks] 
FROM DISK = N'C:\Backup\AdventureWorks_LogBackup_2022-10-23.bak' 
WITH FILE = 1, NOUNLOAD, STATS = 5, STOPAT = N'2022-10-23T16:30:00';
GO

Marked Transactions in SQL Server

Marked transactions are a seldom used but effective technique. It can be used when database deployments span multiple databases. This technique requires that the database is in full or bulk-logged recovery and routine full backups and transaction log backups are taken. At the beginning of the deployment, you would create a marked transaction in the database or databases involved in the deployment. A marked transaction uses standard transaction control logic with additional arguments: BEGIN TRAN [Marked Tran Name] WITH MARK 'Description'. Marked transactions can be integrated into the deployment process. For a large deployment, you may use several marked transactions throughout the deployment, creating multiple recovery points. This could enable you to recover or undo a portion of the deployment should an issue occur. In SQL Server, marked transactions are logged to "dbo.logmarkhistory" in the MSDB system database. This method does not require any special backups, instead relies on the standard backup sequence already in place.

Example: Marked Transaction Log Save Point

To set up this example, we will create a new full database backup on the AdventureWorks database. The database should be in Full Recovery mode. We will also take a transaction log backup to start the backup chain.

--1. FULL Database Backup
BACKUP DATABASE [AdventureWorks]
TO DISK = N'C:\Backup\AdventureWorks.bak'
WITH INIT, NAME = N'AdventureWorks-Full Database Backup', STATS = 10;
GO
 
--2. Next Trans Log backup
BACKUP LOG [AdventureWorks] 
TO DISK = N'C:\Backup\AdventureWorks_LogBackup1.trn' 
WITH FORMAT, INIT, NAME = N'AdventureWorks-Log Backup1';
GO

Next, create a marked transaction before running the database deployment. The marked transaction for this example is named "MyDeploymentMarkedTransaction". I'm creating a new table using a ‘Select Into Table' statement in this example. Marked transaction requires a valid logged transaction like creating a SQL object or a data manipulation statement.

--3. Generate a Marked Transaction Just Prior to the deployment or as a first step in the Deployment
Use [AdventureWorks];
GO
 
--Generate Marked Transaction
BEGIN TRAN MyDeploymentMarkedTrans WITH MARK 'My Deployment Marked Transaction Getdate().';
 
--Run a loggable transaction!
SELECT GETDATE() as 'Date' into MarkTable;
 
--Commit the Marked Transaction
COMMIT TRANSACTION MyDeploymentMarkedTrans;
GO

After creating the marked transaction, we would continue with the database deployment. In this example, I will not deploy any other code changes. However, we will check the Microsoft table in the MSDB system database that logs all marked transactions. The table is "dbo.logmarkhistory". Below, I show how to query "dbo.logmarkhistory" to show marked transactions. I also show that the table created in the marked transaction exists.

--4 Deploy Database Changes Here
 
--5. Show Marked Transaction and the MarkTable
SELECT * FROM msdb.dbo.logmarkhistory WHERE mark_name = 'MyDeploymentMarkedTrans'
GO
USE [AdventureWorks];
GO
SELECT * FROM MarkTable;
GO

Rollback Marked Transaction Log Save Point

In the next step, I will show how to recover or roll back the changes made during the database deployment by restoring the database just before the marked transaction. To do this, we must first do a new transaction log backup. This backup will capture the marked transaction. Then we restore the full database backup, and differential. If any transaction log backups are taken, then they are restored. This includes the restore of the last transaction log backup that contains the marked transaction. When restoring the last transaction file, we must include in the restore statement a reference to the marked transaction by including "STOPBEFOREMARK = N'MyDeploymentMarkedTrans'". Follow that with Restore Database with Recovery option.

--6. Recover to the Marked Transaction
--A. Do a Log backup to capture the Marked Transaction
USE [master]
BACKUP LOG [AdventureWorks] TO DISK = N'C:\Backup\AdventureWorks_LogBackup2.trn' WITH INIT
--,CONTINUE_AFTER_ERROR
,  NAME = N'AdventureWorks_LogBackup2 After Deploy'
GO
 
--B. Restore the Database and Logs, stopping before the Marked Transaction with No Recovery
USE [master]
RESTORE DATABASE [AdventureWorks] FROM DISK = N'C:\Backup\AdventureWorks.bak' WITH  FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
RESTORE LOG [AdventureWorks] FROM DISK = N'C:\Backup\AdventureWorks_LogBackup1.trn' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AdventureWorks] FROM DISK = N'C:\Backup\AdventureWorks_LogBackup2.trn' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
,STOPBEFOREMARK = N'MyDeploymentMarkedTrans';
GO
 
--C. Last, Recover the Database
RESTORE DATABASE [AdventureWorks] WITH RECOVERY;
GO

Finally, run a query confirming that the database no longer contains the table created in the marked transaction:

--7. Confirm the MarkTable created in the marked transaction does not exist.
Use [AdventureWorks];
GO
SELECT * FROM MarkTable;
GO

Conclusion

Other methods for creating a database code deployment save points for recovery include manually creating undo scripts or using other tools such as DACPAC, etc. Also, the database restores can be done to a new database name, and it can be used to compare and manually restore database objects and tables to the pre-deployment state. However, this tip should provide options that fit your specific database deployment needs. The rollback database step should always be part of a good database deployment plan.

Next Steps

Read more on Types of SQL Server Backups
Learn about SQL Server BACKUP DATABASE
Read about SQL Server Recovery Models
Learn about SQL Database Snapshots for Reporting, Auditing and Data Recovery
Read more on How to revert a Database Snapshot to recover a SQL Server database
Learn about Marked Transactions: Recover a SQL Server database prior to database changes (mssqltips.com)

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-11-23

Save Points for SQL Server Database Deployments as a Recovery Option