Backup And Restore SQL Database by Recovery & NoRecovery in MS SQL Server

 

Creating the Backup of SQL Database

In order to create a backup, follow these basic steps:

• In the Object Explorer, select the database for which you want to create a backup. Right-click on the selected database, and an option list will appear. Select Tasks and then Back Up.

• As you click on Back Up, a dialogue box will appear. Add a destination where you want you to store the backup file.

• After adding the destination path. Click on the OK button

It is to be noted that the procedure given above is the easiest way to create a backup of the database. However, there are other ways to do the same. One such way is using T-SQL. In the Back Up Database dialogue, click on Script button. It will expand a list. Now it’s your call on how you want to save the script, to new query window, file, clipboard or a job.

The SQL query will be something like this:

BACKUP DATABASE [SysInfoToolDB] TO  DISK = N’d:\sql\SysInfoToolDB.bak’ WITH NOFORMAT, NOINIT,  NAME = N’SysInfoToolDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

After the above query is saved, it can be executed anytime and so it will come in handy in case backup is needed multiple times.

Restore the Database from a Backup File

The entire procedure of restoring the MS SQL Server Database can be carried out in two ways. You can opt to recover the corrupt database using either RECOVERY or NO-RECOVERY according to your need. It should be kept in mind that using the recovery option will roll back the changes that were not committed. This will be quite handy in making the database consistent and so you will be able to safely bring the database online. The no-recovery method, however, omits the rollback for uncommitted changes allowing further restore stages to continue from the restore point. In order to get a complete insight into these two options, refer to the official Microsoft documentation by following the hyperlink below: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191455(v=sql.105)

Follow the steps given below to restore a database from a backup state:

This is the default method by which you can restore the database to a previously saved state.

• Right-click on Databases in the object explorer and select Restore Database.

• Select the backup file of the database that you have previously created in order to restore it.

• In the left panel, Click on Options. Here you can select the options related to restoration, recovery state and server connections.

• Select the recovery state and by default, choose the Restore with Recovery state. In order to use the No-Recovery method, select Restore with NORECOVERY.  Click on the OK button.

This is the simplest way to restore a database to a previously saved state. You can also use the scripting method using T-SQL. In order to do that, in the Restore Database dialogue box, click on the Script button and select where you want to open the script. Click on the OK button.

The SQL query will look something like this:

For Recovery:

RESTORE DATABASE [SysInfoToolDB] FROM  DISK = N’d:\sql\SysInfoToolDB.bak’ WITH  FILE = 1,  NOUNLOAD,  STATS = 5

, RECOVERY

GO

For No Recovery:

RESTORE DATABASE [SysInfoToolDB] FROM  DISK = N’d:\sql\SysInfoToolDB.bak’ WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [SysInfoToolDB] FROM  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SysInfoToolDB_LogBackup_2019-05-16_10-24-25.bak’ WITH  FILE = 1,  NOUNLOAD,  STATS = 5, RECOVERY

Recovery from a Corrupt Database File Without Restoring

Sometimes what can cause qualms to a person is that the database file is corrupted and no backup has ever been created. For this troublesome scenario, SysInfoTools’  MS SQL Database Recovery software can be a silver bullet as this application software can recover the data from a corrupt MS SQL Database File.

Restoring the Database from a Corrupt Backup File

It is probably likely that a user faces hindrances while restoring the database because of corruption in the backup file. And the worst part is, there is no manual method to repair SQL database backup files. In such a situation, you can use SysInfoTools’ SQL Backup Recovery software to restore MDF and NDF databases from SQL Server backup files. Then the MS SQL Database Recovery tool can be used to recover the data from those databases. Also, SQL Backup Recovery can successfully recover the data from compressed backup files.

Recommended to read: How to Restore Master Database in SQL Server