Summary: When doing restoring backup in SQL Servers, you may encounter the error 3201 – Cannot open backup device. This can prevent you from restoring the database backup. restoration which is crucial for a functional database. In this post, we will see why this error occurs and, how to resolve this error. We will also explore different causes and finally, we will find a way to restore the database mention an advanced SQL database repair software that can come in handy if the backup file is corrupt.
How to Fix Error 3201 “Cannot Open Backup Device” during SQL Server Restore?
When restoring a database from a backup in SQL Server, you may encounter a situation where the database restoration process fails with the error 3201. The complete error message reads as:
Msg 3201, Level 16, State 2, Line 2
Cannot open backup device ‘C:\Program\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\stellar.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
The error can occur due to multiple reasons. Below, we will discuss the causes of the error 3201 and the solutions to fix the issue.
SQL Server Error 3201 – Causes and Solutions
Below we will discuss the causes behind the error 3201 in SQL Server and the solutions to fix the issue.
1. Backup File doesn’t Exist
The most common major reason behind the error 3201 is that the backup file does not exist.
Solution: In such a case, you need to The first thing that we can do is to check if the backup files exists or not. To do so, In open File Explorer, go to the path mentioned by in the error message, and check if the backup file exists.
If the backup file does not exist, you will need to find it and provide the correct path or copy the backup file to the path of your preference.
2. Network Failure
It might happen that the backup is stored in a shared folder, which is stored on another server. If the network fails for some reason, the shared folder will not be available. Network may fail due to,
- Connectivity issues.
- Problems with the IP.
- Problems with the network card in the server with the shared folder.
- Problems with the network card in the machine where SQL Server is stored.
- Hardware problems in the machine.
- Network cable malfunctioning.
- Firewall issues.
- Path changes.
Solution: You need to check if there are connectivity issues, IP address is correct or not, network card is working fine, and the hardware is in perfect shape. You can also check if Firewall is blocking the network.
3. Permission Issues
The backup restoration process may fail if you do not have permission to access the folder where the backup is stored.
Solution: If you have privileges to modify the permissions to the folder, grant the required permissions. To provide permissions to the folder,
- Open the SQL Server Configuration Manager.
- Go to SQL Server Services and select SQL Server (MSSQLSERVER).
- Check the Log On As information. This is the account that you need to add in the folder.
- Right-click the folder with the backup file and select the Security tab.
If you do not have permissions to modify the folder, contact your system administrator to grant the permissions.
4. Backup File is Corrupt
It might happen that the backup file is corrupt and that is why the SQL Server cannot detect the file. Solution: If the backup file is corrupt, you need to take the help of a third-party SQL database repair software, such as Stellar Repair for MS SQL Technician. This software is specialized in restoring data from corrupt backup and data files. It can recover all the objects from the file, including tables, triggers, indexes, stored procedures, and even deleted records. The software supports MS SQL version 2022, 2019, 2017, 2016, and earlier. Here’s how to use the software:
- Download and install Stellar Repair for MS SQL Technician.
- Open the software and select the Extract from MS SQL Backup module.
- Select the backup file you want to repair and press the Scan button.
- Select the Standard Scan method which is faster. The Advanced Scan can be used if the Standard Scan fails.
- Select the backup you want to recover and press Next.
- If everything is fine, you will receive a message, saying that the MS SQL backup is repaired successfully.
- Click the Save icon.
- You can save the backup in a New Database. This option will create a new database from the backup. In addition, you can use the Live Database option (replace the existing database online) or save in other formats (like Excel, HTML, or CSV files). Then, press Next.
- Enter the SQL Server name and select the Windows Authentication method. Optionally, you can select the location to save the new database file. Then, click Next.
- Select the Fast-Saving method.
You will see a message when the file is saved successfully.
Conclusion
The error 3201 occurs when restoring the backup file in SQL Server. Above, we have explained the reasons that can lead to this error and the solutions to fix the issue. In case the backup file is corrupt, you can use Stellar Repair for MS SQL Technician. This software can easily repair corrupt SQL database backup (.bak) files and recover all the data. You can download the demo version of the software to evaluate its functionality.
