Critical steps users should perform immediately after they think a disaster has occurred

Even though it seems as the most practical and simple solution, restoring the database after the disaster can be a two-fold step-back. One, it will most likely recover the data and restore the database to the state/time when the backup was created, but all database changes after the backup will be completely lost. And two, restoring the database will prevent recovery processes with third party tools, since the original MDF and LDF files and the information within will be lost once the restore process finishes. In this article, we’ll discuss adequate steps required to be prepared for a successful recovery using 3^rd party SQL Server database recovery tools

The most important step which must be executed immediately once a disaster is detected is to ensure that all the database data that can be used for recovery is protected and safely stored for safekeeping, so that all recovery resources can be used to ensure that the recovery chance is as high as possible after the recovery process has been decided. The How to choose and perform the most appropriate recovery solution after the data loss article offers the advice on how to decide on the best recovery solution for most common disaster scenarios, and in this article, we’ll focus on the pre-emptive steps that precede the recovery process and should be executed as soon as possible.

Recovery model

One of the most important resources for a database recovery is transaction log file and transaction log backups. Since SQL Server stores information on all database transactions in its online .ldf file (transaction log file), it is generally recommended to keep the database in the full recovery model to guarantee that the information in the transaction log is present. If the simple recovery model is used instead, SQL Server will overwrite online transaction log content on a regular base to keep its size at minimum.

Protecting recoverable data

Even in the case of simple recovery model, the online transaction log file may not be immediately overwritten, and the information for recovery can still exist in it. In addition to the .ldf file and transaction log backups, used by the ApexSQL Log, a SQL Server transaction log reader and a recovery tool, to read the transaction history and roll-back changes which have cause a data loss, another important source for the recovery is a database file (.mdf). The .mdf file is used by ApexSQL Recover, a SQL Server recovery tool, during the data recovery process in order to enhance the recovery chance. Even though both tools can be used for specific recovery scenarios, they still utilize different mechanisms, so if the recovery with one tool did not bring expected results, initiating a recovery with the other tool can bring the wanted results. More on the tools mechanisms and capabilities can be checked in the Recovery features comparison: ApexSQL Log vs. ApexSQL Recover article.

Now that we’ve explained that it is important to keep the information inside .mdf and .ldf files as it is, and to save them in the immediate post-recovery state to increase the chance for successful recovery, we can decide on how to proceed with their preservation:

1. The simplest solution to preserve database .mdf and .ldf files is to take the database to the read-only state, which will prevent any processes that could overwrite information in the database files
   1. To do this, start SQL Server management studio and connect to your SQL Server instance
   2. In the Object explorer pane, navigate to the database struck with a disastrous occurrence and click right mouse button to initiate context menu
   3. Choose properties
   4. In the Select a page pane, click on the Options page
   5. Scroll all the way down to the State category, and change the Database read-only value to True

   

   With this, the database and its files are preserved and are now safe. Even though this step can be skipped, it is particularly useful in cases when user wants to create a database or transaction log backups (which can take some time), and wants to ensure nothing is overwritten during the process, or in cases when interrupting and stopping users/applications who currently read the data from the database is not possible.

2. As said above, switching a database to the read-only state can be entirely skipped in cases where the DBA/user can immediately take the database offline. Even though it looks extreme, taking the database offline is important since it is not possible to make copies of database files while the database is online. Creating copies of database files will enable users to immediately bring the database online (ensuring short down-time) so the users can continue to use the database. On the other hand, database files copies can now be used to bring a database replica online on the same or different server, and to work on the recovery away from the active production database.
   1. To bring the database offline, simply navigate to the database in SQL Server Management Studio Object explorer pane and bring up the context menu with the right mouse click on the database
   2. Navigate to Tasks
   3. and choose the Take offline option
   4. Now, navigate to the SQL Server installation folder, and go to the MSSQL\DATA\ folder, and create copies of database .mdf and .ldf files
   5. Bring database process back online by following the same steps as above, only in the final step choose the Bring online option (SSMS will confirm a that the database has been brought online successfully

In addition to the live (active) database files, we should also check for all available database backups and transaction log backups files, and ensure these files are available for recovery process.

Now that we’ve secured database files, we can use the LDF and MDF copies to bring the database online on any other (or even the same) SQL Server instance, and to utilize more time to analyze the disaster, and check for the database/data damage, recovery options, and recovery solution.

If you can’t take the database offline, the next best solution in this case is to minimize the traffic on the database as much as possible and to initiate the most appropriate recovery process immediately

In summary:

• Set database recovery model to Full, to ensure information in the transaction log file is always preserved and available
• Perform regular database backups, both full and transaction log backups
• Store database backups on a separate drive (in comparison to live MDF/LDF files)
• Never panic or lose hope, ensure that preemptive steps are executed no matter how severe the disaster is (which both saves your recovery resources, and buys time), and do not skip the decision on the best recovery approach/solution/tool