How to Restore SQL Server Database from Backup?

Database backups in SQL Server are important as they help restore data in case of database corruption, virus attack, server issue, or any disaster. For example, if a virus damages the database, you can use the last created backup to restore the database. In this article, we will see how to restore SQL Server database from backup.

Restore SQL Database from Full Backup using SSMS

A full backup contains the entire database. Follow the below steps to restore the database from full backup in SQL Server using SQL Server Management Studio (SSMS).

• In the SQL Server Management Studio (SSMS), go to the Object Explorer, select the Databases node, right-click on it, and select the Restore Database option.

• Select the Device option on the General page and then press the Browse button.

• In Select backup devices, press the Add button.
• Select your backup file and press OK.

• If you need to relocate the files, go to the Files page and select the Relocate all files to folder option.

• On the Options page, select Overwrite the existing database (if you want to replace the previous one).
• If your database is under replication, you may need to select the Preserve the replication setting.
• You can also select the restrict access to the restored database option. This option is useful if you don’t want anyone to access the database while you are still working on the restoration.
• The Tail-Log will back up the tail log and try to restore it.
• Optionally, you can select Close existing connection to destination database. Then, click OK.

Restore SQL Server Database from Backup using T-SQL

Alternatively, you can use the T-SQL script to restore the database from backup. Run the following T-SQL code to restore the database backup:

USE [master]
BACKUP LOG [stellardb] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\stellardb_LogBackup_2024-03-05_09-38-48.bak' WITH NOFORMAT, NOINIT, NAME = N'stellardb_LogBackup_2024-03-05_09-38-48', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [stellardb] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\stellardb.bak' WITH FILE = 1, NOUNLOAD, STATS = 5

GO

The above code will back up the tail log and then restore the database from the backup file.

What to do if the backup is damaged or corrupted?

Sometimes, you fail to restore the database. So, it is recommended to check your backup and test it before restoring it. The following command can help you to verify if your backup works:
RESTORE DATABASE StellarDB
FROM DISK = N'C:\Backups\stellardb.bak'
WITH VERIFYONLY;

If it is damaged, there is a way to recover your backup. The best option is to repair your corrupt backup file with a third-party software, such as Stellar Repair for MS SQL. There are 3 editions of this software. The Technician and the Toolkit editions include the option to repair backup database.
The first thing you need to do is install the Stellar Repair for MS SQL Technician software.
Secondly, select the Extract from MS SQL Backup option.

Thirdly, select the backup file. You have two options to select the backup file:
• If you know your backup file location, use the browse option and select your .bak file.
• If you do not know your backup file location, you can use the Find button to search for the backup file.

Once the backup is selected, press the scan button.

There are two options for scanning:
• The Standard Scan, which is the default option and the recommended one. It is the faster option to scan the file.
• The Advanced Scan, which is a slower option but it can be used in case the Standard Scan fails to recover the data.

Select the backup set to repair and press Next.

After repair, it will display all your database objects.

Select the database objects you want to recover and press the Save icon.

You can save the data in New database and have a new database with the data restores. In addition, you can restore the data in a current Live Database or export the data to Other Formats, like Excel, CSV, and HTML.

Conclusion

If the database gets corrupted, you can always recover the database using the most current backup. You can follow the above-mentioned ways to restore the SQL Server database from backup. However, make sure that your backup is not corrupt. To check this, you can use the verifyonly option. If the backup is corrupt, you can use Stellar Repair for MS SQL - Technician to recover your data from the backup file with complete integrity.