3

I need a copy of a live database called Carers on the same server for testing purposes. The test database name will be Carers_Test.

I took a backup of Carers using SSMS. And tried to restore it using the GUI. When I followed the steps, the only thing I changed was the name of the database.

But the restore fails with the error:

Exclusive access could not be obtained because the database is in use.

I do not want to do anything with the current live database, why is that matter that the database is in use? I have tried to restore the same backup on my local host and it restored successfully. When I try to restore it on the same server with the live database then I get this error.

Please someone help me...I'm going crazy!

  • 3
    Silly question, but did you also MOVE the files to different filenames? – BlueGI Dec 4 '19 at 15:00
  • This could be because the physical files point to the same files. – Eric Dec 5 '19 at 0:06
5

It's reaaly simple to solve. You are trying to override the original files you need to you use the move option like this

restore database <Your-Database> from disk ='<backup-file-path>' 
with move 'mdf_file_name' to '<new-path>',
move 'log_file_name' to '<new-path>'

by the way to discover the file name from backup use this command

restore filelistonly from disk ='<backup-file-path>' 
2

I think it also depends on which version of Management Studio you're using. In 2016+ when you change the name of the Destination Database, SSMS changes the name of the Restore As files (see the screenshots). I seem to remember that in older versions of SSMS you had to manually change the names of the files, or else they would conflict with the existing files.

Also, be very careful of the Options "Overwrite the existing database" and "Take a tail-log backup before restore". Both of those should be Unchecked!

Lastly, if I were you, I would take a backup of the production database immediately before trying a restore of that production database.

Here I've changed the name of the new database: page 1

Here the names of the new files are changed by SSMS. You might need to manually change these. In any case, check what SSMS is trying to do: page 2

Here the two important Options are Unchecked: page 3

2

It has been suggested that the reason for your error is because of the database files (use the same files as your current/existing database). That is not the reason for your error. If that were the reson, you would have seen a different error message. Below is from trying to restore a database named TSQL into the name TSQLTest, using the same file names as the existing TSQL database:

The file 'C:\DemoDatabases\DbFiles\a\TSQL.mdf' cannot be overwritten. It is being used by database 'TSQL'.

SSMS is pretty good at changing the database files names for you when you type something for the name of the database to be restored. Old SSMS did this in a pretty stupid way, and then came one version which didn't change the names for you, and then in the next version came the "intelligent" file changing that we see today. I don't recall the version history, as for when those changes were made, but it was a while ago.

The reason for your error is that the restore GUI will by default try to do a tail log backup using the NORECOVERY option. That will fail if there are users in that database.

0

how about something like that? I'm restoring database named [Northwind] into a new database named [NORTHWND_new]

be aware that I'm changing the name of data and log files.

USE [master]

RESTORE DATABASE [NORTHWND_new]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Northwind.bak'
WITH FILE = 1, MOVE N'Northwind' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\NORTHWND_new.MDF', 
    MOVE N'Northwind_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\NORTHWND_new_log.ldf', NOUNLOAD, STATS = 5
GO
0

Thank you very much for all the answers.Tibor Karaszi and Conrad S., especially thanks to you both as the problem was the tail backup. It was originally ticked and the error was caused by this.

I'm relieved now :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.