SQL Server Error Msg 3036 – The database “%ls” is in warm-standby state (set by executing RESTORE WITH STANDBY).

In this blog post, let’s learn about the error message “3036 – The database “%ls” is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

3036 – The database “%ls” is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed.

Reason for the Error

SQL Server Error Msg 3036 occurs when attempting to perform a backup on a database that is in a “warm-standby” state. This error message is generated because SQL Server prohibits backup operations on a database in a warm-standby state until the entire restore sequence is completed.

A warm-standby state is a recovery model option in SQL Server that allows a database to be restored to a read-only state while still allowing transaction log backups to be performed. This option is typically used for creating a standby server for disaster recovery purposes. The restore with standby option allows you to restore the database to a read-only state so that it can be used for reporting or querying purposes while still applying transaction log backups to the database to keep it in sync with the primary database.

When a database is in a warm-standby state, SQL Server restricts backup operations to prevent any data loss during the restore sequence.

Examples:

To demonstrate this error, we can create a sample database and restore it with the “WITH STANDBY” option as shown below:

-- Create a sample database
CREATE DATABASE TestDB;

-- Take a full backup of the database
BACKUP DATABASE TestDB TO DISK = 'C:\TestDB.bak';

-- Restore the database with standby option
RESTORE DATABASE TestDB WITH STANDBY = 'C:\TestDB_standby.bak';

Now, if you try to take a backup of the database while it’s still in the warm-standby state using the following command:

BACKUP DATABASE TestDB TO DISK = 'C:\TestDB_backup.bak';

You will receive the following error message:

Msg 3036, Level 16, State 1, Line 1 The database “TestDB” is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed.

Solution

To resolve this error, you must wait until the entire restore sequence is completed before attempting to take a backup of the database.

In our example, we can use the following command to apply the transaction log backups and bring the database out of the warm-standby state:

RESTORE DATABASE TestDB WITH RECOVERY;

Once the restore sequence is completed and the database is out of the warm-standby state, you can then take a backup of the database without encountering the Msg 3036 error.

Alternatively, if you no longer require the warm-standby database and want to make it available for use, you can also perform a final restore using the NORECOVERY option to bring the database online and allow backup operations to be performed:

RESTORE DATABASE TestDB WITH NORECOVERY;