SQL database files (MDF/NDF) are prone to corruption, integrity issues, and consistencies errors. If your database is corrupt or damaged, the easiest option is to restore the database from backup (.bak) file. There are different ways to restore the SQL database backup (.BAK) file. Below, we will discuss these ways to restore the SQL database from .BAK file.
Methods to Restore SQL Server Database from .BAK File
Here are some methods you can use to restore SQL Server database from backup.
Method 1 – Restore SQL Database from Backup using SSMS
SQL Server Management Studio (SSMS) is an effective tool with integrated environment for managing SQL databases. It provides a user-friendly graphical user interface to perform various functions. You can use the SSMS to restore SQL database from the .BAK file. Follow these simple steps:
Step 1: Open SSMS and connect to the instance of SQL Server (in which your database is saved).
Step 2: Go to Object Explorer and click the Server Name to expand the Server tree.
Step 3: Navigate to Databases and open the database you want to restore in SQL Server.
Step 4: Right-click the Database and then click Restore Database.
Step 5: The Restore Database window is displayed. On General page, under Source section, select any of these options:
a. Choose the Database option and then select the database you want to restore from the dropdown list.
b. Choose the Device option and then click the ellipses (...) to find your backup file.
- From the 'Select backup devices' window, choose File as backup media, and then click Add.
- Locate and select the .BAK file you want to restore and then click OK.
- Click OK to return to the 'Restore Database' window.
Step 6: The name of the database to be restored is displayed in the Database field under Destination section. Information of the selected backup file will be added in the Backup sets to restore section.
Step 7: Select the advanced options from the Options page.
a. Select Options under Select a Page.
b. On the Options page, do the following:
- Under the Restore options section, select the 'Overwrite the existing database (WITH REPLACE)' option.
- Under the Recovery State section, choose 'Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored (RESTORE WITH RECOVERY)'.
Step 9: Click OK when ‘The restore of database completed successfully' message pops up.
Method 2: Restore SQL Database from Backup using T-SQL
Alternatively, you can use T-SQL queries to restore database from the .bak file. Here’s how:
Step 1: Start SSMS and then connect to your instance of SQL Server.
Step 2: Click the New Query option.
Step 3: In the Query Editor window, run the following RESTORE command for restoring the entire database from the .bak file:
GO
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\testing.bak'
WITH NOFORMAT, NOINIT,
NAME = N'testing-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
GO
Method 3 – Restore SQL Database with Windows PowerShell
You can also use the Restore Sql Database command in Windows PowerShell to restore the SQL Server database. It supports all types of backup restore in SQL, including full database restore, transaction log restore, and database file restore. Here’s the syntax to restore SQL database from BAK file using the Windows PowerShell:
Restore-SqlDatabase -ServerInstance [server-instance] -Database [database-name] -BackupFile [backup-file] -ReplaceDatabase
What If your SQL Database Backup File is Corrupted?
In case the backup is not available or the backup file is corrupted, there is not much you can do using the native tools. In such a situation, you need to rely on third-party software, like Stellar Repair for MS SQL-Technician. The software contains a utility, named Stellar Backup Extractor for MS SQL, specifically designed to recover data from corrupt database files. It can recover all the objects from database, including tables, indexes, collation, and even recover deleted records. It then save the recovered data in a new database (MDF) file. The tool supports all SQL backup types, including Full Backup, Differential Backup, and Transaction Log Backup. It can also repair corrupt database (MDF/NDF) files without any file size limitations. The tool is compatible with MS SQL 2022, 2019, and lower versions.
Read this: How to restore the SQL Server database with Stellar Repair for MS SQL Software?
Conclusion
In case of corruption or any consistency issues in the SQL database file, you can restore the backup file. Above, we have discussed different methods to restore the SQL database from the backup (.bak) file. If the backup file is damaged or corrupted, then you can use Stellar Repair for MS SQL Technician. This advanced tool can extract data from corrupt SQL database or backup file with complete integrity. You can free download this tool to scan the corrupt database file and preview the recoverable data.
FAQ:
Can I restore a .BAK file taken from a newer version of SQL Server to an older version?
No, you can’t restore .BAK file created in a new version of SQL server to an older version. If you want to do the same, you need to install a server with the same version and then perform the restoration process.
What should I do before restoring database from the .BAK file?
Before you proceed with the backup restoration process, consider the below things:
- Close all the active connections to prevent the restore process from failing.
- Backups that are created on recent SQL Server versions cannot be restored to earlier versions of SQL Server.
- Create a backup of the active transaction log.
- When restoring a database from another server instance, you will need to manage metadata to make the database available on another server.
How can I use T-SQL to restore SQL Server database from .BAK file?
You can use the below command to restore SQL database file from backup file using the T-SQL:
RESTORE DATABASE admin
FROM DISK = 'Z:\SQLServerBackups\admin.bak' ;
RESTORE DATABASE stellardbHow can PowerShell be used to restore SQL Server database from .BAK file?
You need to run the Restore Sql Database command in Windows PowerShell to restore the SQL Server database from backup.
What if the backup file is corrupted or outdated?
If your backup file is corrupted or outdated, then the only option is to use a professional tool, like Stellar Repair for MS SQL Technician. It can extract data from corrupt MS SQL backup (.bak) file and save it in a new database (MDF) file.
4 min read
4 min read
4 min read
