SQL Server Restoration With the NORECOVERY Option

Aftab Alam Aftab Alam Published On - 24 Apr 2019

When you have multiple SQL backup files, you can restore SQL data with the NORECOVERY option to roll forward the restore process till the recovered database is consistent. This facilitates the complete recovery of data with the help of the RECOVERY option in the end.

Recovery is the process of copying data from the backup file and playing the information from the logged transaction until the recovery point. However, the database is inaccessible to users during this process. But it ensures that data can be restored from multiple backup files. And in the end, recovery is performed with the RECOVERY option.

Importance of Backups

Taking a backup of the database is necessary because it protects you from any tricky situations such as data loss. You can keep the copies of the backup at a safe offsite location and conduct the restore procedure when necessary.

With the assistance of a SQL database backup file, you can recover the data in the following conditions:

  • Database file becomes corrupt.
  • The user accidentally drops or deletes a table.
  • A hardware failure like the storage drive becomes corrupt.

Moreover, a backup file is helpful for administrators while performing tasks like copying the database file to another SQL Server and saving an archive file.

Read Also : How to Backup SQL Server Database?

Database recovery with NORECOVERY

The NORECOVERY option puts the data into a ‘restoring’ state and will allow data from multiple backups to get restored. When the database is in restoring state, then the user cannot access the data.

These are the examples in which you can use the NORECOVERY option –

RESTORE DATABASE DB01 FROM DISK = ‘C:\documents\DB01.BAK’
WITH NORECOVERY
GO
RESTORE LOG DB01 FROM DISK = ‘C:\documents\DB01.TRN’
WITH RECOVERY
GO

The first command restores the database from the DB01 backup file and the second command restores the transaction logs using the recovery option.
You can use the whole database using both RECOVERY and NORECOVERY commands.

RESTORE DATABASE DB01 FROM DISK = ‘C:\documents\DB01.BAK’
WITH NORECOVERY
GO
RESTORE LOG DB01 FROM DISK =’C:\documents\DB01.TRN’
WITH NORECOVERY
GO
RESTORE LOG DB01 FROM DISK =’C:\documents\DB01.TRN’
WITH RECOVERY
GO

The first two command restores backups and the last command restores the transaction log with using the RECOVERY option.
Restore the database using the RECOVERY option.

RESTORE DATABASE DB01 FROM DISK =’C:\documents\DB01.BAK’
WITH RECOVERY
GO

It is the default command to restore the database.

As mentioned earlier, the NORECOVERY command leaves the database in the restoring state. So, restore the database from its restoring state.

RESTORE DATABASE DB01 WITH RECOVERY
GO

The command will make the database online again.

Difference between RECOVERY and NORECOVERY options

  • NORECOVERY rolls the process forward so that the next action is executed. It restores the backups and then rolls the process forward.
    The database remains in the ‘restoring’ state after running the NORECOVERY command, so the user cannot either access or work on the database.
  • RECOVERY is the default recovery command that puts the database in its consistent state. The RECOVERY option puts the database in the operational state.

Manual SQL recovery methods are suitable only for minor corruption issues; they cannot work efficiently for severe corruption of SQL data. So, in severe corruption cases, always use a trusted third-party software which can not only remove the error but also restore the database file. Kernel for SQL Backup Recovery is the most suitable application to recover data from SQL backup files and that too without any transaction log files.

Kernel for SQL Backup Recovery

Here is the step by step process of recovering an SQL Backup file with the help of our software. The tool recovers SQL database files even from corrupt backup files.

Step – 1. Install and run the software. A wizard automatically opens up. Here, you can browse the BAK file from its location. 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 the whole SQL data from the backup file. Now, you can see its content.

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

Note: For saving the data as a batch file, choose the Batch File option and browse the folder to collect the backup data. Then click OK.

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

Conclusion

Whenever you are facing a tricky situation like a corrupt backup file, you may lose your data permanently. So always use Kernel for SQL Backup Recovery which is easy to use and reliable. This SQL backup recovery tool reduces the complexity of manual backup recovery. After recovering the SQL Backup file, you can further restore the MDF file from BAK file for SQL Server.