1

I can't seem to restore a bak file to the same server but a new database. It works fine if I do it on another computer. I use SQL Server 2012 through SSMS. Everytime I do it it says Exclusive access could not be obtained because the database is in use, even though it's a nonexistent destination database.

Here are the steps:

  1. Right click Databases
  2. Restore Database...
  3. [General] Source from Device, select the bak file
  4. [General] Change the Destination database name to a new name
  5. [Files] Change mdf and ldf Restore As paths to a different, new path
  6. [Options] (unnecessary on the other computer) Check Overwrite the existing database (WITH_REPLACE)
  7. [Options] (unnecessary on the other computer) Check Close existing connections to destination database
  8. Click OK

This is the error message:

Restore of database 'DELVIPROD_JUNE' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

Additional information:
System.Data.SqlClient.SqlError:Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)

  • Have you tried to create a new empty database before to restore this files? – McNets Jul 6 '17 at 7:20
  • 1
    Yes, tried that as well. Created a new empty db, and then restored to that. Same results – iceman2992 Jul 6 '17 at 7:21
  • Are you executing it on the server machine or from another computer? – McNets Jul 6 '17 at 7:25
  • On the server machine, accessed through RDP – iceman2992 Jul 6 '17 at 7:41
  • 2
    @iceman2992, you should not specify the overwrite and close options if your intent is to create a new database. The error suggests the generated restore script isn't proper for the task you are attempting. Review the generated script by clicking on the script button in the restore dialog and add it to your question. – Dan Guzman Jul 6 '17 at 10:33
3

Close all tabs on the right side in the management studio and try again. It is possible that you clicked on the database and some tab on the right side is accessing it.

Alternatively, you may use this sql (replace file name and path)

USE master;
GO
ALTER DATABASE DELVIPROD_JUNE SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
restore database DELVIPROD_JUNE FROM DISK = 'C:\temp\db.bak'   WITH replace 
ALTER DATABASE DELVIPROD_JUNE SET MULTI_USER;

If you want to make a copy of a database, this might help https://serverfault.com/questions/62590/how-to-duplicate-mssql-database-on-the-same-or-another-server

  • Wouldn't this replace the original database .mdf and .ldf files, if I don't specify where to restore the new files? – iceman2992 Jul 6 '17 at 7:42
  • If you are restoring the db, why are the old ldf, mdf needed? If you want to take preserve them, stop db, copy them and start db again. You may also use WITH MOVE 'DELVIPROD_JUNE _Data' TO 'c:\SQLData\DELVIPROD_JUNE _Data.mdf', MOVE 'DELVIPROD_JUNE _Log' TO 'c:\SQLData\DELVIPROD_JUNE _Log.ldf' format in restore database sql. – Allen King Jul 6 '17 at 7:45
  • 1
    Because the original database is in use and should not be disturbed. I'm trying to make a copy of the production database to run development scripts on – iceman2992 Jul 6 '17 at 7:49
  • @iceman if you want to make a copy of the db this may help serverfault.com/questions/62590/… – Allen King Jul 6 '17 at 7:51
  • Having said that, I should point out that it is no a good idea to mess with the server that hosts a production database. – Allen King Jul 6 '17 at 7:54
1

Here is an example of a script that can be generated when you iterate through your steps and instead of clicking on OK, you click on the generate script icon and send to a new query window:

USE [master]
RESTORE DATABASE [DELVIPROD_JUNE] 
FROM  DISK = N'F:\SQL\BACKUP\SRV1\StackExchange\FULL\SRV1_StackExchange_FULL_20170624_223003.bak' 
WITH  FILE = 1,  
-- important bit here is the move
-- SQL Server will move the logical file name to a new file location
-- MOVE <logical_name> TO <physical_location>
MOVE N'StackExchange' TO N'D:\SQL\SQL_DATA\StackExchangeNew.mdf',  
MOVE N'StackExchange_log' TO N'E:\SQL\SQL_LOGS\StackExchangeNew_log.ldf',  
NOUNLOAD,  
STATS = 5
GO

The important bit is the MOVE option. Assuming you are using a new database name and you changed the file location then the restore dialog in the Files section will have displayed two (2) columns. One named 'Original File Name' and an additional 'Restore As' column where you can modify the database file path and file names for the new database.

Because you are restoring a new database, there is no need to specify the WITH_REPLACE option, as the database does not yet exist.

However, before you execute the script or finish the dialog by clicking on OK, ensure that the files you listed in the Restore As portion do not exist on the file system.

If the files do exist run the following statement to find out which database is using the files:

SELECT DB_NAME(database_id), FILE_ID, physical_name, state_desc FROM sys.master_files

This should enable you to pinpoint the issue.

As for the error message you are seeing: If the files are in use, then somebody or some system is using them. Use the above script to see if the database files are being used by a different database.

1

I've had this trouble on some SQL servers as well. When I think I should have exclusive access it will still tell me I don't. If you're absolutely sure you have exclusive access it maybe due to some sort of stupid non intuitive workflow of Management Studio, where you need to put the UI focus on another database that's not the one you are trying to restore.

Things to check first

  1. Make sure there's nothing else running that connects to your DB (including webservers etc... ie Stop any IIS stuff that maybe connecting to it and any other server that may connect to it)

  2. Do a MSSQL service restart to make sure you've dropped any connections that hang on to it

  3. Check that your SQL server login in Management student doesn't have your target database as the default database to connect to when opening - check the login window's > options > connection properties for the default entries.

  4. So here's the real non-intuitive kicker. Once connected, right click on another database that's not the one you want to restore . From the context menu, go to Tasks and restore set all the values that you need to, to restore the database you actually want to restore.

For some reason just right clicking on your database will result in a connection.

I think this weirdness only happens on some versions of SQL Server or Management studio, which is probably why it only happens to you on some servers. It's one of the most dumbass frustrating feature of SQL studio that took me ages to figure out.

0

To restore a database you need to have Exclusive access to the database. So before restoring you need to check if there are any connections to the database and you need to kill those session.

What I do is make the database in Restricted Mode and then kill all active sessions after checking sp_who2. Now restore the database and it should go fine.

  • Your answers is not quite correct. If you are restoring a *.bak file as a new database, then there can't be any connections to that database, as it does not exist at the time the database is starting to be restored. – John aka hot2use Jul 11 '17 at 8:52
  • @hot2use I doubt its being restored an new, because for that the error for exclusive access will not pop up. Looks like question was updated. May be there is another database with same name. – Ramakant Dadhichi Jul 11 '17 at 9:19

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.