SQL server restoration with the RECOVERY & NORECOVERY option

Read time 10 minutes

RECOVERY and NORECOVERY are the two most common types of restore use in SQL Server database recovery. When a user has to restore from a Full backup, they should go with the RECOVERY option. In case of more than one backup file (differential backup, transactional log backup, etc.) NORECOVERY option must be used.

In database recovery, you can restore inaccessible or deleted data back to the SQL server with database backups. Data loss can happen due to corruption, virus attack, hardware failures, etc. This blog will cover how to restore SQL Server from backup. We will use both RECOVERY and NORECOVERY parameters for data recovery. In case the manual methods fail due to severe corruption, we will restore the server with a professional SQL backup recovery tool.

How to Restore SQL Server Database

Users can restore the SQL database using DATABASE RESTORE command with RECOVERY or NORECOVERY options.

Restore Database with RECOVERY

Restore SQL Server with RECOVERY is the default recovery method. You can do it in two ways. Let’s discuss both:

Method 1. Using SQL Server Management Studio (SSMS)

The graphical user interface of the SSMS tool makes the recovery process very simple. Follow these steps to restore SQL Server in no time:

Step 1: Open SSMS and connect to the database.

Step 2: Right-click the Databases option and select Restore Database.

Step 3: Select the database. Then, in the left side pane, click on Options.

Step 4: Select the Recovery state as RESTORE WITH RECOVERY from the dropdown.

Step 5: Click OK to start the SQL Server recovery.

Method 2. With T-SQL Statements

Users who are comfortable with SQL statements can simply run a T-SQL query to recover the database. This method is faster than the previous method but requires the user to be technically skilled in SQL queries. Run the query with the following steps:

Step 1: Open SSMS and click on the New Query button. You can also press Ctrl + N.

Step 2: Run the following query in the tool:

Restore Database with NORECOVERY

Similar to the RECOVERY option we discussed above, the database restoration with NORECOVERY is possible in two different ways. These are:

Method 1: Using SSMS Tool

To recover database with NORECOVERY perform the following steps:

Step 1: Follow the same steps from step 1 to step 3 in Method 1 of Restore Database with RECOVERY above.

Step 2: Then, select the Recovery state as RESTORE WITH NORECOVERY from the dropdown.

Step 3: Finally, click OK.

The NORECOVERY option will leave the database in the ‘restoring state’. This will make the database inaccessible. To fix this, you must run the following command:

Method 2: With T-SQL Query

Skip the multi-step recovery process using the T-SQL query. Run the following command to restore database quickly.

The first two commands will restore database and log files using the NORECOVERY parameter. The last RESTORE command with RECOVERY option makes the database accessible again.

RECOVERY Vs NORECOVERY – What to Choose?

Understand the difference between restoring the database with RECOVERY and with NORECOVERY.

Why are SQL Backups Important?

Regularly backing up the SQL Server data helps in preventing permanent data loss. The backup file allows for quick recovery of corrupt SQL Server. Problems that you can solve with the SQL database file are:

• Hardware Failures: Server crashes, power outage, hard drive failure can cause data loss issue with the SQL Server.
• Software Issues: Buggy software update, incompatibility between software, etc., may result in data loss.
• Human Errors: Accidental data deletion, incorrect server shutdown, or executing wrong SQL statements. These are the basic human errors responsible for lost data.
• Malware Attacks: Online or offline attacks can render your SQL Server useless. Virus attacks can corrupt the database tables or make the data inconsistent.
• Natural Disaster: Earthquake, flood, fire, or other natural calamity at the data center where the SQL server is installed and stored.
• Data Migration: Often organizations have to migrate their data from one location to another. In such situations, backing up the data is crucial to avoid data loss during the data transfer.

Considering the points above, it is crucial to always have a consistent backup file ready. You can backup SQL Server using the SQL Server Management Studio. Let’s see some tips that will help you with backup.

Backup Timing: To avoid data access issues, take the backup when the users are not accessing the information from the database. It is good to schedule backup accordingly.

Backup Type: If your database size is small and it updates only a few times a month. Then, create a Full backup. For larger and constantly changing databases, user must take backups time to time. Therefore, for large database, differential backup may be a better approach.

Backup File Location: A backup file size can range from MBs to GBs. Therefore, make sure enough storage space is available. It’s also a good idea to store backups in a separate location from your main database server. This protects your backups in case something happens to the server itself.

Follow the above guidelines to make error-free backups files. But, what to do if your backup file is corrupt and not suitable for data recovery.

Restore SQL Server from Corrupt Backup file

Manual SQL recovery methods are suitable only for minor corruption issues. They cannot restore the data efficiently if your backup file is severely corrupt. Without the knowledge of SQL database architecture and query syntax, restoring database from a corrupt file is risky. Running incorrect SQL commands will lead to permanent data loss.

So, in severe corruption cases, always use a trusted third-party software. Kernel for SQL Backup Recovery is an advanced tool to recover data from SQL backup files. This .bak file recovery tool can work without the need of transaction log files.

Here is the step-by-step process of recovering corrupt SQL backup file.

Step 1. Install and run the software. A wizard automatically opens up. Clik the three dot button and browse the BAK file. Then choose the scan mode and let the tool automatically detect the version of the database. Click the Next button.

Step 2. Select the file to process, and then click Next.

Step 3. You will get a brief source summary. Click Finish.

Step 4. The tool recovers all of the SQL data from the backup file. Now, you can see its content. Select the data you want to recover and click the Save icon. You can also select the complete .bak file for recovery.

Step 5. There are two destinations where you can keep the data. The first one is live SQL Server, and the other is the offline batch file. For SQL Server, you need to input the credentials.

Note: To save the data as a batch file, choose the Batch File option. Then, click the browse button and select the destination where you want to save the recovered backup file. Click OK to begin the process.

Step 6. A success message shows that data is saved at the specified location. Click OK.

How to design your backup strategy?

When you have finalized that you are going to take the backup of the SQL database, then you need to design your backup strategy based on a variety of factors.

Q. When to schedule the backup process?

Find the appropriate time to run the backup process, when the users are not accessing the information from the database. It is good to schedule backup accordingly.

Q. What type of backup to be performed?

When you have a large database, you have the option to run a partial backup or full backup.
Q. How much space is the backup file going to take for storage?

The backup of a large-sized database will also be large, so it will take a lot of space for storage. So, you need to make space for the large backup or store it at a different offline location.

When you have considered all these questions, then you can plan the backup process with more clarity.

Conclusion

Recover SQL Server database from a FULL backup with RECOVERY. For multiple backup (Differential and Log) files, go with NORECOVERY option followed by RECOVERY. However, if your backup file is corrupt and manual methods fail to restore data. Then, to prevent permanent data loss use Kernel for SQL Backup Recovery. After recovering the SQL Backup file, you can further restore .bak file in SQL Server.