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!
Best Answer
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:
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:
Here the two important Options are Unchecked: