SQL Server 2014 database restore fails

Question

I have clustered version of SQL Server 2014 for production, and for development I use SQL Server 2014 2014 Express edition.

I am facing problem while restore database backup from .bak.

I get the following error every time I try to restore

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'WEBDESIGNINA\SQLEXPRESS2014'. (Microsoft.SqlServer.SmoExtended)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA\Database_Name.mdf'.

File 'Database_Name' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA\Database_Name.mdf'. Use WITH MOVE to identify a valid location for the file.

The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA\Database_Name.ldf'.

File 'Database_Name_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\DATA\Database_Name.ldf'. Use WITH MOVE to identify a valid location for the file.

Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3634)

Only way to get around this problem is to delete the database & then restore to new database.

How can I overwrite this database without error

Answer 1

Happened with me too. Never seen this before. I took the DB offline, renamed the mdf/ldf, and started the restore, it is working now. Not sure if it is a good idea or not. But somehow I had to get this DB restored and move on.

Answer 2

I had this problem on a VPS with SQL 2014 Express. There was not enough security permission to a do a restore from the default backup directory: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup (although backups worked fine)

The error was: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

Actually, you can follow these steps to change the authority:Log in to SSMS using Windows Authentication Navigate to Security->Logins Either double Click, or Right Click and select Properties on the user you're interested in . In the "Select a page" box, select "Server Roles" Make sure that the "sysadmin" role is selected Press Ok to save the change and close the dialog Exit SSMS Reopen SSMS and login as the User you wanted, and you should now have sufficient Privileges