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 has become corrupted or data has been lost due to a hardware failure, user error, or other disaster. Recovery mode restores allow you to roll back transactions to before the failure occurred.

When to Use Recovery Mode for Restores

There are a few key scenarios where restoring a SQL Server database in recovery mode is recommended:

  • Hardware failure – If a drive fails or data becomes corrupted, restoring to a point before the failure allows you to recover the database.
  • Accidental changes – If changes like mass deletes or updates are made accidentally, reverting to an earlier restore point can undo the changes.
  • Software corruption – Bugs, viruses, or other software issues can sometimes corrupt data. Recovery mode can restore before the corruption.
  • User errors – Developers or DBAs may make mistakes that impact the data. Recovery restores provide a safety net.
  • Attack/malware – In the event of an attack or malware incident, restores can roll back to before the attack occurred.

The key is restoring to a point before the issue occurred to undo any problematic transactions or data changes.

Steps to Restore a Database in Recovery Mode

Follow these key steps to perform a SQL Server database restore in recovery mode:

  1. Identify restore point – Choose a specific date/time to restore to before the failure or corruption occurred.
  2. Take database offline – Set the database being restored to offline mode in SQL Server.
  3. Restore database – Issue a RESTORE DATABASE command, specifying the restore point, recovery mode, files to restore, etc.
  4. Recover data – Use the RECOVERY command to roll back transactions to the restore point.
  5. Bring database online – Set the database back to online mode so it’s usable again.
  6. Test restore – Validate data has been recovered to the desired restore point.

Now let’s look at each of these steps in more detail:

1. Identify Restore Point

First, you need to identify the date and time to restore to. This should be a point before the failure, corruption, or accidental changes occurred. Often the restore point is picked by identifying:

  • The last known good full backup – Restore to the latest full backup before the issue.
  • Point of failure – Pinpoint when the hardware failed or changes happened.
  • Malware attack time – When a virus or malware was detected.

Picking an appropriate restore point is crucial for recovering the maximum amount of data.

2. Take Database Offline

Before starting the restore process, you should take the database offline within SQL Server. This prevents any further changes from occurring while recovering:

ALTER DATABASE <DatabaseName> SET OFFLINE;

With the database in offline mode, you can proceed with the restore steps.

3. Restore Database

The actual database restore is initiated using the T-SQL RESTORE DATABASE command. This will roll back the data files, log files, and other database components:

RESTORE DATABASE <DatabaseName> 
FROM <BackupDevice>
WITH RECOVERY,
MOVE 'DataFile' TO 'C:\RestoreLocation\Data.mdf',  
MOVE 'LogFile' TO 'C:\RestoreLocation\Log.ldf';

Key points about the restore command:

  • Specify database name to restore – After DATABASE clause.
  • The restore source – Backup device, which can be a file, tape, URL, etc.
  • RECOVERY option – This initiates recovery mode.
  • FILE/LOG locations – New locations for data and log files.

This will roll back all data file and log file changes up to the specified restore point.

4. Recover to Point in Time

After the restore is complete, transactions need to be rolled back to the specified recovery point using the RECOVERY command:

RESTORE DATABASE <DatabaseName> WITH RECOVERY TO '2021-11-14 12:00';

This syntax points to a specific date/time to recover to, ensuring all transactions after that point are reversed.

5. Bring Database Back Online

With the transactions rolled back during the recovery process, the database can be brought back online:

  
ALTER DATABASE <DatabaseName> SET ONLINE;

Users and applications can now reconnect and have access to the recovered data from the specified restore point.

6. Validate Restore

Finally, perform validation checks to ensure your data has been properly restored:

  • Check row counts match expectations in critical tables.
  • Sample data to verify proper values.
  • Test applications to ensure proper behavior.
  • Have users verify key scenarios work correctly.

Address any issues before confirming the recovery efforts were successful.

Recovery Model Impact

The recovery model configured for your database impacts how much data you can recover during this process:

Full Recovery Model

  • Backs up transaction log frequently.
  • Can restore to a specific point in time.
  • Ideal for recovery situations.

Bulk-Logged Model

  • Backs up logs less frequently.
  • Restores may only go back to last log backup.

Simple Model

  • No log backups, only data file backups.
  • Can only restore to end of last backup.

So if you want to maximize recoverability via log restores, use the full recovery model.

When Recovery Fails

In some cases, the database recovery may fail or can only be partially restored to the desired point. Some common reasons include:

  • Not enough valid backups – Missing full or log backups from desired restore point.
  • Corrupted backup files – Backup files that can’t be read will block restores.
  • Bad restore point – Specified point is not valid or unsupported.
  • Transaction log issues – Problems applying backed up transaction logs.
  • Recovery failure – Unknown errors during the recovery process.

If recovery fails, you may need to restore to a later point in time that has reliable backup data. Or restore to the end of the last known good backup set.

Automating Recovery Tasks

It’s possible to script out and automate many of the recovery steps, which is useful for streamlining the restore process. Here are some examples of automating common tasks:

PowerShell Scripts

Script out PowerShell routines to perform steps like taking databases offline/online and executing restore commands.

Command Files

txt files with T-SQL commands can be utilized to automate complex recovery sequences.

SSIS Packages

Create SSIS packages that execute recovery routines and data validation checks.

Maintenance Plans

Built-in maintenance plans in SQL Server can perform some recovery steps.

Recovery Testing

It’s important to practice restoring a database into recovery mode before you face a real outage or disaster scenario. Periodically try restoring to a prior point in time to ensure your backup files and process is working. This helps avoid surprises during an actual failure.

Some best practices for test restores include:

  • Use a copy or backup of current production database.
  • Restore to a point 1-2 days ago.
  • Follow full recovery procedures.
  • Validate test environment after restore.
  • Note any issues or failures.

Conclusion

Restoring a SQL Server database in recovery mode allows you to roll back to a specific point in time before a failure or corruption. Following proper backup approaches, using the right recovery model, scripting tasks, and testing regularly will ensure you can reliably recover your database when issues occur.

Leave a Comment