Backing up the database in MS SQL Server is vital to safeguard and recover the data in case of scenarios, like hardware failure, server failure, database corruption, etc. MS SQL Server provides different types of backups, such as differential, transactional, and full backup. A full backup allows you to restore the database in exactly the same form as it was at the time of creating the backup. The differential backup stores only the edits since the last full backup was created, whereas the transaction log backup is an incremental backup that stores all the transaction logs.<\/p>\n
When you restore SQL database<\/a> backup, SQL Server offers two options to control the state of the database after restore. These are:<\/p>\n When you use the RESTORE WITH RECOVERY option, it indicates no more restores are required and the state of database changes to online after the restore operation.<\/p>\n You can select the WITH NORECOVERY option when you want to continue restoring additional backup files, like transactional or differential. It changes the database to restoring state until it\u2019s recovered.<\/p>\n Now, let\u2019s learn how to use the WITH RECOVERY and NORECOVERY options when restoring the database.<\/p>\n You can use the WITH RECOVERY option to restore a database from full backup. It is the default option in the Restore Database window and is used when restoring the last backup or only the backup in a restore sequence. You can restore database WITH RECOVERY option by using SQL Server Management Studio (SSMS) or T-SQL commands.<\/p>\n If you want to restore database without writing code and scripts, then you can use the graphical user interface in SSMS. Here are the steps to restore database WITH RECOVERY using SSMS:<\/p>\n Open SSMS and connect to your SQL Server instance. On the Restore database<\/strong> page, under General<\/strong>, select the database you want to restore and the available backup. If you have a large number of operations that need to be managed or you want to automate the tasks, then you can use T-SQL commands. You can use the below T-SQL command to restore the database with the RECOVERY option.<\/p>\n RESTORE DATABASE [DBName] FROM DISK = ‘C:BackupDB.bak’ WITH RECOVERY;<\/p>\n You can use the NORECOVERY option to restore multiple backup files. For example, if your system fails and you need to restore the SQL Server database to the point just before the failure occurred, then you need a multi-step restore. In this, each backup should be in a sequence, like Full Backup > Differential > Transaction log. Here, you need to select the database in NORECOVERY mode except for the last one. This option changes the state of the database to RESTORING and makes the database inaccessible to the users unless additional backups are restored. You can restore the database with the NORECOVERY option by using SSMS or T-SQL commands.<\/p>\n Here are the steps to restore MS SQL database with the NORECOVERY option by using T-SQL commands:<\/p>\n Step 1:<\/strong> First, you need to restore the Full Backup by using the below command:<\/p>\n RESTORE DATABASE [YourDatabaseName] Step 2:<\/strong> Then, you need to restore the Differential Backup. Use the below command:<\/p>\n RESTORE DATABASE [YourDatabaseName] Step 3:<\/strong> Now, you have to restore the Transaction log backup (last backup WITH RECOVERY). Here\u2019s the command:<\/p>\n RESTORE LOG [YourDatabaseName] You can follow the below steps to restore the database with NORECOVERY option using the SSMS:<\/p>\n In SSMS, go to the Object Explorer<\/strong>, expand databases, and right-click the database node. Sometimes, the restore process can fail due to corruption in the database backup file. If your backup file is corrupted or you’ve not created a backup file, then you can take the help of a professional MS SQL repair tool, like Stellar Repair for MS SQL Technician. It is an advanced SQL repair tool to repair corrupt databases and backup files with complete integrity. The tool can repair backup files of any type — transactional log, full backup, and differential — without any file-size limitations. It can even restore deleted items from the backup database file. The tool is compatible with MS SQL Server version 2022, 2019, and earlier.<\/p>\n Above, we have discussed the stepwise process to restore the SQL database with RECOVERY<\/strong> and NORECOVERY<\/strong> options in MS SQL Server. If you face any error or issue while restoring the backup, then you can use a professional SQL repair tool, like Stellar Repair for MS SQL<\/a> Technician. It can easily restore all the data from corrupt backup (.bak) files and save it in a new database file with complete precision. The tool can help resolve all the errors related to corruption in SQL database and backup (.bak) files.<\/p>","protected":false},"excerpt":{"rendered":" Introduction Backing up the database in MS SQL Server is vital to safeguard and recover the data in case of scenarios, like hardware failure, server failure, database corruption, etc. MS SQL Server provides different types of backups, such as differential, transactional, and full backup. A full backup allows you to … <\/p>\nRESTORE WITH RECOVERY<\/h3>\n
RESTORE WITH NORECOVERY<\/h3>\n
How to Restore MS SQL Server Database with the RECOVERY Option?<\/h2>\n
1. Restore Database with RECOVERY Option using SSMS<\/h3>\n
\nGo to Object Explorer<\/strong>, expand databases, and right-click on the database.
\nClick Tasks > Restore<\/strong>.<\/p>\n
\nNext, on the same page, click Options<\/strong>.
\nIn the Options<\/strong> window, select the recovery state as RESTORE WITH RECOVERY. Click OK<\/strong>.<\/p>\n2. Restore Database with RECOVERY Option using T-SQL Command<\/h3>\n
How to Restore MS SQL Server Database with NORECOVERY Option?<\/h2>\n
1. Using T-SQL Commands<\/h3>\n
\nFROM<\/span> DISK =<\/span> N‘C:PathToYourFullBackup.bak’<\/span>
\nWITH<\/span> NORECOVERY,
\nSTATS =<\/span> 10<\/span>;<\/p>\n
\nFROM<\/span> DISK =<\/span> N‘C:PathToYourDifferentialBackup.bak’<\/span>
\nWITH<\/span> NORECOVERY,
\nSTATS =<\/span> 10<\/span>;<\/p>\n
\nFROM<\/span> DISK =<\/span> N‘C:PathToYourLastTransactionLogBackup.bak’<\/span>
\nWITH<\/span> RECOVERY,
\nSTATS =<\/span> 10<\/span>;<\/p>\n2. Using SQL Server Management Studio (SSMS)<\/h3>\n
\nClick Tasks<\/strong>, select Restore<\/strong>, and click Database<\/strong>.
\nIn the Restore Database<\/strong> page, select the source (i.e. full backup), and the destination. Click OK<\/strong>.
\nNext, add the information about the selected backup file in the option labelled — Backup sets to restore<\/strong>.
\nNext, on the same Restore Database<\/strong> page, click Options<\/strong>.
\nOn the Options<\/strong> page, click RESTORE WITH NORECOVERY<\/strong> in the Recovery state<\/strong> field. Click OK<\/strong>.<\/p>\nWhat if the SQL Database Backup File is Corrupted?<\/h2>\n
Conclusion<\/h2>\n