Read time: 5 minutes
Microsoft SQL Server is a popular database management application utilized by thousands of organizations across the globe. However, while this application is blended with multi-purpose features, companies also face critical issues that halt their processes.
And one of the issues that might occur randomly is ‘SQL Server Error 3156 Database Cannot Be Restored.’ To be precise, the error stats as mentioned below with the detailed extraction of the message:
“SQL error 3156: SQL error 3156: File ‘filename’ cannot be restored to ‘C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\FTData\Name’. Use WITH MOVE command to identify a valid location for the file.”
So, what is this error, and how can it be resolved quickly? In this article, we will answer all your questions while providing relevant solutions. After following the restoration methods, you could easily works on existing databases and export SQL Server tables to a CSV file easily and without facing an error.
Getting started
Before you proceed to the solutions to this error, you should check whether a user has permission to restore the specified file. And for this, follow these steps:
- If you aim to test this option, open Windows Explorer, then copy and paste the path(directory) included in the error message.
- Check whether you have permissions, and if not, restore it in another path/directory along with the permissions on the folder. Or else, you can grant permissions to the desired users in the folder.
Now, if you still face issues, there might be several possibilities.
Possible reasons behind the occurrence of SQL server error 3156
The message in error signifies that both logical and physical file names can’t be restored. This is due to the problem with the specified file location. Below might be the causes:
- Another file has used the same name, which users can’t override.
- Users might require access to the specified location or Windows directory.
- There is an incorrect allocation of the file’s path, or it might not exist in the explorer.
Ways to restore database
When it comes to resolving the error, there are various solutions to fix it. However, the two specific ways to overcome the issue are from BAK File in SQL Server.
1st Method – Using transact-SQL to restore database from backup
Follow the command to initiate this process:
2nd Method – Restore database backup using SQL server management studio
Follow the steps to resolve this issue through management studio:
- Initiate a connection with SQL Server Database engine
- Choose Databases with a right-click and select the Restore Database option.
- Select From Device option by using the Source field.
- Click browse (…) to open the “Select backup devices” window tab.
- The next step is to select “Backup media type” as a File, followed by clicking the Add button.
- Then select the .bak file that needs restoration. Finally, Click OK.
- Go to the “Select the backup sets to restore” section, and choose the backup file selected in the previous step. Click OK button
- Next is to select Options from the left side of the window, then choose the “Overwrite the existing database (WITH RECOVERY)” option from Restore options
- Click the “Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)” option in the Recovery state section.
- Click OK.
After these steps, you will have the control to set the permissions and implement the solutions for error 3156.
Solutions to fix SQL server error 3156 “database cannot be restored”
The error might showcase that you have taken the backup from the location: ‘D:\DB\LiveDB.mdf,’ and now you are attempting to restore the database at: ‘F:\ReportDB\ReportDB.mdf’
So, you can use the WITH MOVE command for relocating the database.
Use the Restore Database [ReportDB] command to copy FROM DISK = ‘xyz.bak’ WITH RECOVERY, and then type MOVE ‘LiveDB’ To ‘ F:\ReportDB\ReportDB.mdf.’ Finally, use the MOVE ‘LiveDB_Log’ TO ‘F:\ReportDB\ReportDB.mdf_Log.ldf’, followed by REPLACE, STATS = 10.
Note: In case you are not aware of user interface handling or do not like the method, you have the option for utilizing the T-SQL commands:
RESTORE DATABASE [databasename] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\aw.bak’ WITH FILE = 1,
MOVE N’filename’ TO C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\FTData\Name’.mdf’,
MOVE N’Logfile_log’ TO N’C:\sql\logfile_log.ldf’, NOUNLOAD, STATS = 5
In this code, MOVE clause is used for moving the files to a specified location.
After all these methods and efforts, if you are still not able to restore the database, then it is possible that the BAK file becomes corrupted. Hence, the SQL server won’t support SSMS and T-SQL methods to repair the damaged backup files.
Alternate solution – Kernel SQL Database recovery
If none of the above approaches works for you, it’s time to use an alternative solution to fix corruption errors in the SQL database. There are multiple options available in the market to fix SQL databases. For instance, Kernel for SQL Database Recovery is an advanced tool designed to help you scan and fix corruption errors in SQL Databases.
The tool is equipped with smart algorithms that scan the SQL databases and fix corruption and errors. It repairs any corrupt or inaccessible SQL database files and recovers all the objects, including tables, functions, triggers, records, etc. The tool is available as a trial version that enables you to recover limited data from your SQL Databases.
Conclusion
SQL Server database error 3156 is quite complex; even if a single step is missed during the resolution, it can lead to entire data loss. The alternate option is the utilization of the MOVE option, but then there is a risk of the file getting corrupted. So, the ideal approach is to use an automated solution that can repair and fix all the corruption errors in SQL databases. This article discusses all the possible solutions to fix SQL Server 3156 error. After following all the methods, you can restore the database back to SQL Server and export data from SQL database to excel as a form of backup.
