Restoring MS SQL Databases with the Recovery and Norecovery Options in UTF-8 Encoding

The difference between the RECOVERY and NORECOVERY options

Actually, Recovery and Norecovery are two command options in the database restore process. Both of these options require SQL database backups.

If you have only one backup and want to restore directly to it, you can use the RESTORE option. By default, this recovery includes both a rollback and a replay of the logs, without allowing for additional backups to be restored. That is, the database is brought to an operational state with the RESTORE option.

NORECOVERY will move the process forward so that the next operation can be performed. If you have different types of backups (differential, transaction log, etc.), NORECOVERY is probably your best option.

How to Use the Restore with Recovery and NoRecovery Options in MS SQL Database

The “RECOVERY” and “NORECOVERY” options are two manual SQL recovery methods that work for minor SQL corruption issues. Before starting these commands, you must remember that having a database backup is necessary. You must have a backup before starting the command.

Backup MS SQL

Step 1: In SQL Server Management Studio, right-click the database and choose Tasks > Backup.

Step 2: Then, you can back up the database as needed. When backing up, you can specify where to store the database backup.

Now that we know the differences and how to create a backup, let's discuss in detail how to restore MS SQL databases.

Restore using recovery options

You can restore it in SQL Server Management Studio by right-clicking on "Databases" and selecting "Restore Database."

You can also use T-SQL, which will be useful if you want to automate tasks.

**Important Notice:** Make sure to adjust this code before using it to match the correct path and settings for your database environment. ```html RESTORE DATABASE F FROM DISK = 'c:\sql\F.bak' WITH FILE = 1, NOUNLOAD, STATS = 5, RECOVERY GO ``` This HTML code contains an SQL command to restore a database named "F" from a backup file located at 'c:\sql\F.bak'. In Chinese: **恢复数据库F，从磁盘位置 'c:\sql\F.bak' 恢复，使用文件号1，不卸载，每5%显示统计信息，** **并执行恢复操作。** **执行（GO）** Please note that the `` and `` tags in the HTML are used to emphasize the text in a web page or document; they would not appear in the actual SQL script.

Replace "F" with the name of your database. You do not need "RECOVERY," as it is a default option.

Restoring Without Restoring

The NORECOVERY option is useful if you have multiple backups to restore, because the NORECOVERY command leaves the database in a “restoring” state, allowing additional backups to be applied.

You can use SQL Server Management Studio to issue the Norecovery command to restore the database.

Click "Options." Select "No Restore Points," and then click "OK."

Meanwhile, you can use this command:

**RESTORE DATABASE F FROM DISK = N'c:\sql\F.bak'** **WITH NORECOVERY** **GO** This is a SQL statement used to restore a database named F from a specified disk location. The `RESTORE DATABASE` command is used to restore the database, and `FROM DISK` specifies the location of the backup file (in this case, `c:\sql\F.bak`). `WITH NORECOVERY` indicates that after the recovery, the database will not automatically be put into a usable state, possibly requiring further recovery steps. `GO` is a keyword in T-SQL denoting the end of the statement block.

**RESTORE LOG FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\F_LogBackup_2019-8-4_12-24-25.bak'** **WITH RECOVERY** **GO** This is a SQL statement used to restore the log file of a SQL Server database. The "RESTORE LOG" command is used to restore the log backup, and "FROM DISK" specifies the location of the backup file, which in this case is 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\F_LogBackup_2019-8-4_12-24-25.bak'. "WITH RECOVERY" indicates that after the recovery, the database will become available, and all recovery operations will be completed immediately. "GO" is an indicator used in SQL Server Management Studio to execute the statement.

Here, the NORECOVERY command is used in anticipation of more backups. This command will restore the database, but leave it in a restoring state.

MS SQL Database Recovery Tool (Simpler)

If your SQL Server database is corrupted, you can choose other methods to recover data instead of just using backups. If you do not have a backup of the database, you can still perform a recovery of lost data without backup.

The SQL Recovery software tool can assist you in repairing corrupted databases and resolving all types of SQL database repair issues. Furthermore, it can recover database components like tables, triggers, indexes, keys, rules, and stored procedures from SQL databases, along with deleted records. It supports MS SQL Server 2017, 2016, 2014, 2012, 2008, and earlier versions.

Step 1. Download and run the SQL Recovery tool.

Step 2. Click on the two dots (Browse button) to select the target MDF file, or click “Find” and choose the file to be repaired from the list that appears below. Then click “Repair” to start the repair process.

Step 3: Once the scanning is finished, a window will appear to confirm that the analysis was successful. All recovered tables and records will be listed in the left pane under their original table names.

4. Click the “Export” button in the bottom right corner of the screen. You can save the recovered database to a database and SQL script as needed. Then, you'll need to enter the server/instance name and connect to the server. If you choose “Export to Database,” you can create a new database or export to an existing one.