[Fixed] SQL Server Error 3156 Database Cannot Be Restored

Introduction

SQL Server Error Message 3156 is related to a database restoration. We will show different alternatives to solve the error message.

The error message is the following:

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 to identify a valid location for the file.

You can read the reference guide from here: SQL Server Database RESTORE WITH MOVE or not WITH MOVE

Also, You Can follow easy steps as mentioned below.

Requirements

The current article is suitable for any SQL Server version.

  • SQL Server installer
  • SSMS installer

Getting started

The first thing that you need to do is check if the user that is restoring the file has permissions in the file specified.

To test it, go to the Windows Explorer and copy and paste the path mentioned in the error message:

SQL Server Error 3156 Database

If you do not have permissions restore in another path with the permissions on the folder or grant permissions to the user in the folder.

In order to change the restoration path, in the Restore Database window, go to the files tab:

SQL Server Error 3156 Database

If you check the Relocate all files to the folder, you can change the restoration location to a valid place.

SQL Server Error 3156 Database

If you do not like the user interface, you can use the T-SQL commands:

The code uses the MOVE clause to move the files to a different location.

If your backup is damaged, you can use the Stellar Toolkit for MS SQL Solution. This software includes three modules:

  • MS SQL Database software repairs SQL Server Data files (MDF and NDF)
  • MS SQL Password Recovery software recovers lost SQL Server passwords (User and SA).
  • MS SQL Backup Recovery software recovers data from damaged or corrupt.BAK file. In this article, we will use this one.
SQL Server Error 3156 Database

Open the Extract from MS SQL Backup and select the bak file. The backup files have usually the bak extension and are usually stored in program files\SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Backup folder, however, you can customize and save the backups in a different place if you want.

SQL Server Error 3156 Database

There is also an option to search the backup if you do not know where is it. The Look in specifies where to start the search and the Search in Folder, will start the Search:

Once selected the backup file, press the Scan button:

SQL Server Error 3156 Database

The Scan button will start repairing the backup and the software will give you a Window to select the backup set. You can check the backup set name, the date:

SQL Server Error 3156 Database

Once selected the backup set, press the Next button and select the SQL Server version:

SQL Server Error 3156 Database

You can work with any SQL Server version and there are also options to work with files created on a version and converted to a new one. Press OK.

The software will show all the available SQL Server objects inside the backup including tables, views, synonyms, stored procedures, triggers, sequences, rules, default, assemblies and more:

SQL Server Error 3156 Database

It is possible to Preview the data inside tables:

SQL Server Error 3156 Database

You can also check the T-SQL code of the views:

SQL Server Error 3156 Database

Once selected the objects, press the Save button to save the information.

SQL Server Error 3156 Database

There are 4 options:

SQL Server Error 3156 Database

One is to save the information into SQL Server (MSSQL), the other option is to save in a comma-separated value format (CSV), in Hypertext Markup Language (HTML) or Excel (XLS). In this example, we will store into MSSQL.

You can save the backup in a New database or in an existing database (live database). Also, you can specify the Instance name and the Authentication type. You can use the default location or use a custom location also:

SQL Server Error 3156 Database

After completion of saving, you will be able to see the database restored in SQL Server:

SQL Server Error 3156 Database

Conclusion

In this article, we show different ways to solve the SQL Server Error message 3156. We show how to move the location of the database restored and also a way to fix the backup if it is damage.

Finally, we show how to use Stellar Toolkit for MS SQL to recover a backup damaged and restore the information. It is possible to restore in MSSQL, HTML, Excel or CSV.

Thank you for your time, if you have questions, do not hesitate to write your comments.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.