How do I restore SQL Server database in recovery mode?

by
How do I restore SQL Server database in recovery mode

Restoring a SQL Server database in recovery mode allows you to recover a database to a specific point in time. This can be useful if your database becomes corrupted or if you need to revert changes made after a certain point. Recovery mode restores allow you to roll the database back to before the corruption or errors occurred.

When is recovery mode restore required?

There are a few situations where restoring a SQL Server database into recovery mode is required:

  • Database corruption – If pages in a database file become corrupted, restoring to an earlier point can recover the database to before the corruption occurred.
  • Reverting changes – If unwanted changes are made to a database, reverting to an earlier restore point rolls back those changes.
  • Point in time recovery – Restoring to a specific point in time, such as right before a batch of bad transactions, can undo that work.

Recovery mode restores are helpful when you need to reverse changes or undo database corruption issues.

How does restore in recovery mode work?

When you restore a SQL Server database into recovery mode, the database is left in an intermediate state. This paused state allows you to recover to a specific point in time before the database is brought fully online.

Here is an overview of how a recovery mode restore works:

  1. A database backup file is restored using the RESTORE DATABASE command with the RECOVERY option.
  2. SQL Server replays logged transactions up to the specified point in time or transaction marker.
  3. The database is left in a restoring state, with undo and redo actions paused.
  4. Using another RESTORE LOG statement, you can recover to a precise moment.
  5. The database is then recovered and brought fully online.

The key is that after the initial restore, the database is left in a paused state prior to being recovered. This gives you a window to specified a precise point for recovery.

Step-by-step restore example

Here is an example walkthrough of restoring a database into recovery mode and recovering to a specific point in time:

  1. Take a full database backup: 
    BACKUP DATABASE ExampleDB 
TO DISK = 'C:\Backup\ExampleDB.bak' 
WITH INIT;
  2. Restore the full database backup using the RECOVERY option: 
    RESTORE DATABASE ExampleDB
FROM DISK = 'C:\Backup\ExampleDB.bak'
WITH RECOVERY;
  3. The database is now in a restoring state, with rollforward paused.
  4. Review current recovery point using the command: 
    RESTORE DATABASE ExampleDB
WITH RECOVERY
  5. The log_scan_start and log_scan_stop times are shown, representing current recovery point.
  6. To recover to an earlier point in time, use another RESTORE LOG statement: 
      
RESTORE LOG ExampleDB
FROM DISK = 'C:\Backup\ExampleDB_LogBackup.bak'
WITH RECOVERY, STOPAT = '2022-06-16T13:00:00'
  7. The database is now recovered to the specified point in time.
  8. Bring database fully online: 
    RESTORE DATABASE ExampleDB WITH RECOVERY
  9. Database is now restored to the recovery point.

This example demonstrates the process of restoring a database into recovery mode, reviewing the current recovery point, and recovering to an earlier point in time.

Recovery of specific data range

In some cases, you may want to restore and recover only a subset of data from a specific time range. SQL Server provides additional options to recover a range of data.

Here is an example restoring a range of data from transaction log backups:

-- Restore database to point in time  
RESTORE DATABASE ExampleDB
FROM DISK = 'C:\Backup\ExampleDB.bak'
WITH RECOVERY, STOPAT = '2022-06-01 00:00:00'

-- Recover portion of log between two points 
RESTORE LOG ExampleDB  
FROM DISK = 'C:\Backup\ExampleDB_LogBackup.bak'
   WITH RECOVERY,  
   STOPAT = '2022-06-16T19:00:00',
   STOPBEFORE ='2022-06-16T12:00:00';

-- Bring database online
RESTORE DATABASE ExampleDB WITH RECOVERY

This restores the database to June 1st, and then only applies the portion of the transaction log between 7PM and 12PM on June 16th. This allows you to restore a subset of data from a specific window of time.

Determine point-in-time to recover to

When restoring a database into recovery mode, you need to determine the appropriate point in time to recover to. Here are some tips for finding the right recovery point:

  • Identify when the database corruption or unwanted changes occurred and choose a time right before that.
  • Check application logs or audit logs to find relevant events to recover prior to.
  • Review database transaction logs to identify suspicious or unwanted transactions to roll back.
  • Communicate with application owners to determine an appropriate recovery point based on business needs.
  • Validate the recovery point you choose by testing to confirm the database is recovered properly.

Taking these steps will help you identify and validate the appropriate point in time to recover to.

Recovery model considerations

The recovery model set on the SQL Server database affects what restore options are available:

  • Full – All restore options supported. This allows point-in-time restore.
  • Bulk-logged – Point-in-time recovery supported, but less data available in logs.
  • Simple – Only full database backups available, no point-in-time restore.

To use recovery mode, the database must be in the full or bulk-logged recovery model. Check the current recovery model using:

SELECT name, recovery_model_desc
FROM sys.databases

The model can be changed to full with:

ALTER DATABASE ExampleDB
SET RECOVERY FULL;

Restore to standalone or copy of database

When restoring a database into recovery mode, best practice is first to restore a copy of the database. This preserves the original production database in its current state. The steps to restore a copy are:

  1. Restore a full backup of the database with a new name: 
     
RESTORE DATABASE ExampleDB_RestoreCopy
FROM DISK = 'C:\Backup\ExampleDB.bak'
  2. Restore backups of logs to the copy database.
  3. Put the copy database into recovery mode.
  4. Specify the desired point-in-time to recover to.
  5. Bring the restored copy database online.

Once validated, the recovered copy can replace the original production database. Taking these steps reduces impact to the existing production database during restore.

Restore database to new location

In some cases you may want to restore the database to a different location or SQL Server instance than the original. To do this:

  1. Detach the original database: 
    USE master;
GO
ALTER DATABASE ExampleDB SET SINGLE_USER;
GO
EXEC sp_detach_db 'ExampleDB';
  2. Copy the database files (.mdf and .ldf) to the new SQL Server location.
  3. Attach the database files at the new location: 
    CREATE DATABASE ExampleDB
ON (FILENAME = 'C:\SQLServer\Data\ExampleDB.mdf'),
(FILENAME = 'C:\SQLServer\Data\ExampleDB.ldf')
FOR ATTACH;
  4. Restore logs and perform point-in-time recovery on the new instance.

This will allow the database to be restored to a different server or location than the original database.

Additional options for restores

There are some additional options that can be used with recovery mode restores:

  • CHECKSUM – Have SQL Server verify checksums on the database data pages during restore.
  • CONTINUE_AFTER_ERROR – Ignore errors and attempt to continue restoring.
  • REPLACE – Replaces the existing database instead of appending.
  • RESTRICTED_USER – Have database usable only by members of db_owner, dbcreator, or sysadmin roles.
  • STANDBY – Leaves database readable but not writeable after restore.

These can be added to the restore command to further control the restore behavior.

Restore database with move option

The WITH MOVE option can be used to restore database files to different locations. This example restores data and log files to F: drives:

RESTORE DATABASE ExampleDB
FROM DISK = 'C:\Backup\ExampleDB.bak'
WITH RECOVERY,  
MOVE 'ExampleDB_Data' TO 'F:\SQLServer\Data\ExampleDB.mdf',  
MOVE 'ExampleDB_Log' TO 'F:\SQLServer\Data\ExampleDB.ldf';

The MOVE option redirects the data and log file restore locations. This can be useful if drives running out of free space.

Monitor ongoing restore progress

To monitor the progress of a restore operation in SQL Server, use the following query:

SELECT
    session_id as SPID,
    command,
    percent_complete,
    total_elapsed_time
FROM sys.dm_exec_requests
WHERE command LIKE '%RESTORE%'

This will retrieve restore sessions, their status, and total execution time. It can be run repeatedly to check the ongoing progress of a restore.

You can also check the start and stop time for an active restore using:

SELECT 
    TOP 1
    error_number,
    message 
FROM msdb.dbo.backupset
WHERE backup_set_id IN
    (SELECT distinct backup_set_id 
     FROM msdb.dbo.restorehistory 
     WHERE destination_database_name='ExampleDB'
     ORDER BY restore_date DESC)

These queries allow monitoring of long running restore operations in SQL Server.

Conclusion

Restoring a SQL Server database into recovery mode involves restoring from backups to a specified point in time before transactions are rolled forward. This provides a mechanism to recover from unwanted changes or corruption issues.

Key steps include:

  • Restore full database backup with RECOVERY
  • Database enters restore log phase in paused state
  • Use additional RESTORE LOG commands to specify recovery point
  • Review transaction logs and identify recovery time
  • Recovery can be a subset of data within a time range
  • Restore database copy instead of original
  • Monitor restore operation progress

Following these steps allows precise recovery of a SQL Server database to a point in time prior to when unwanted changes occurred. This is a valuable technique for recovering database consistency and data.

Leave a Comment