SQL Server Error 3156 Database Cannot Be Restored – Resolved

Microsoft SQL Server, used by many large organizations to store a large amount of data. SQL Server Management Studio software and T-SQL are useful for backing up the entire database to a .bak file. In many situations, users have to restore the database from the BAK file, but sometimes the database restore failed. This blog covers 3156 errors in SQL Server that occur when you restore the database. Here, we will discuss the causes of this error and provide the simplest way to fix SQL Server error 3156 Database cannot be restored step by step.

Error Database Cannot Be Restored

Msg 3156, Level 16, State 3, Line 2

File 'LiveDB' cannot be restored to 'D:\DB\LiveDB.mdf'. Use WITH MOVE to identify a valid location for the file.

Possible Reasons Behind SQL Server Error 3156

This message identifies the logical or physical file names of files that could not be restored due to a problem with the specified location. Some possible causes are-

• You may need access to the specified Windows directory.
• You may have entered the path incorrectly or specified a path that does not exist.
• The file name may be used by a file that can not be overridden.

Two Manual Tricks to Restore Database from BAK File in SQL Server

There are two free methods that are helpful for restoring SQL Database from backup file.

Trick 1. Restore Database From Backup Using Transact-SQL

RESTORE DATABASE [Test] FROM DISK = 'C:\backups\backup.bak'

Trick 2. Restore Database Backup Using SQL Server Management Studio

• Connect to the SQL Server Database engine
• Right-click on Databases and select Restore Databases
• In General page, Use Source for restore section and select From Device
• Click the browse (…) button and “Select backup devices” window will open
• Select “Backup media type” as File and click Add button and select the .bak file which you want to restore. Click OK
• In the “Select the backup sets to restore” section, select the backup file that selected in the above step and click OK button
• Now, click Options from the left side and select “Overwrite the existing database (WITH RECOVERY)” from Restore options
• In the Recovery state section, select “Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)” option
• Click OK.

Solution to Fix SQL Server Error 3156 Database Cannot Be Restored

The error looks like, you taken the backup from ‘D:\DB\LiveDB.mdf’ and now you are trying to restore the database at ‘F:\ReportDB\ReportDB.mdf’. Now, use the WITH MOVE option to relocate the file.

RESTORE DATABASE [ReportDB]
FROM DISK = 'xyz.bak' WITH RECOVERY
MOVE 'LiveDB’ TO 'F:\ReportDB\ReportDB.mdf',
MOVE 'LiveDB_Log' TO 'F:\ReportDB\ReportDB.mdf_Log.ldf',
REPLACE, STATS = 10

If the database still does not restore, the BAK file may be corrupted. SSMS and T-SQL are not supported to restore damaged backup files. In this difficult situation, you must use the third-party solution.

Automated Solution to Fix SQL Error 3156

To fix damaged SQL Server .bak file, use SQL Backup Recovery Software. This application is capable to fix corrupt SQL database from .bak file with all data which includes MDF, NDF, and LDF database files. The tool fix SQL Server Error 3156, Error 3154, 3241 Error, etc. With this utility, you can execute the restoration of highly corrupted BAK file with all objects.

Download Now Purchase Now

Top Benefits & Features of Advanced Solution

• Simple GUI:- The first most amazing thing about this utility is that it is very easy to use. Any novice user can be easily accessible to use this utility without any interruption.
• All SQL Server Versions Supportive:- The software supports BAK file of all SQL Server versions which includes SQL Server 2017, 2016, 2014, 2012, 2008, etc.
• Fix Damaged Backup File:- This application is specially designed to easily resolve damages in backup file and restore them into SQL Server Database.
• Fix Errors:- The tool can easily fix SQL Server error 3156, No backupset selected to be restored, BAK file is not visible, etc.
• Create New Database:- You can easily restore SQL BAK file to new database. You just only need to enter a unique name which not already created in SQL Server databases.

Note: If you are facing MDF or NDF file corruption issues and your SQL database is inaccessible, must try to use SQL Database Recovery to resolve such issues.

Conclusion

How to fix SQL Server error 3156 database cannot be restored query is now resolved. If users can not able to restore the database using the MOVE option, the backup may be corrupted. To overcome this issue, we suggested the best and trusted software that helps to fix corrupted SQL Server Backup File and restore them to the SQL database.

Frequently Asked Questions