SQL-Server Error - Exclusive access could not be obtained because the database is in use

Question

I am actually trying to make a script  in Sql Server 2008  to restore one database from one backup file  I made the following code and I am getting an error -   Msg 3101  Level 16  State 1  Line 3 Exclusive access could not be obtained because  the database is in use  Msg 3013  Level 16  State 1  Line 3 RESTORE DATABASE is terminating abnormally    How do I fix this problem      IF DB ID  AdventureWorksDW   IS NOT NULL  BEGIN  RESTORE DATABASE  AdventureWorksDW   FILE   N AdventureWorksDW Data   FROM   DISK   N C  Program Files Microsoft SQL Server  MSSQL10 50 SS2008 MSSQL Backup AdventureWorksDW bak   WITH  FILE   1   MOVE N AdventureWorksDW Data   TO N C  Program Files Microsoft SQL Server  MSSQL10 50 SS2008 MSSQL DATA AdventureWorksDW mdf    MOVE N AdventureWorksDW Log    TO N C  Program Files Microsoft SQL Server  MSSQL10 50 SS2008 MSSQL DATA AdventureWorksDW 0 LDF    NOUNLOAD   STATS   10  END

User · Answer

For me  the solution is    Check Overwrite the existing database WITH REPLACE  in optoins tab at left hand side  Uncheck all other options  Select source and destination database  Click ok    That s it

User · Answer

taking original db to offline worked for me

User · Answer

Solution 1   Re-start SQL services and try to restore DB Solution 2   Re-start system   server and try to restore DB Solution 3   Take back of current DB  Delete the current destination DB and try to restore DB

User · Answer

I got this error when unbeknownst to me  someone else was connected to the database in another SSMS session  After I signed them out the restore completed successfully

User · Answer

I got this error when there was not enough disk space to restore Db  Cleaning some space solved it

User · Answer

I just restarted the sqlexpress service and then the restore completed fine

User · Answer

Here s a way I am doing database restore from production to development   NOTE   I am doing it via SSAS job to push production database to development daily   Step1   Delete previous day backup in development   declare  sql varchar 1024    set  sql    DEL C  ProdAEandAEXdataBACKUP AE11 bak  exec master  xp cmdshell  sql   Step2   Copy production database to development   declare  cmdstring varchar 1000  set  cmdstring    copy   Share SQLDBBackup AE11 bak C  ProdAEandAEXdataBACKUP  exec master  xp cmdshell  cmdstring    Step3  Restore by running  sql script  SQLCMD -E -S dev-erpdata1 -b -i  C  ProdAEandAEXdataBACKUP AE11 Restore sql    Code that is within AE11 Restore sql file   RESTORE DATABASE AE11 FROM DISK   N C  ProdAEandAEXdataBACKUP AE11 bak  WITH MOVE  AE11  TO  E  SQL DATA AE11 mdf   MOVE  AE11 log  TO  D  SQL LOGS AE11 ldf   RECOVERY

User · Answer

execute this query before restoring database   alter database  YourDBName   set offline with rollback immediate   and this one after restoring     alter database  YourDBName     set online

User · Answer

Set the path to restore the file  Click  Options  on the left hand side  Uncheck  Take tail-log backup before restoring  Tick the check box -  Close existing connections to destination database    Click OK

User · Answer

Use the following script to find and kill all the opened connections to the database before restoring database   declare  sql as varchar 20    spid as int  select  spid   min spid   from master  sysprocesses  where dbid   db id   lt database name gt     and spid      spid      while   spid is not null  begin     print  Killing process     cast  spid as varchar               set  sql    kill     cast  spid as varchar      exec   sql       select           spid   min spid        from          master  sysprocesses       where          dbid   db id   lt database name gt             and spid      spid end   print  Process completed       Hope this will help

User · Answer

Setting the DB to single-user mode didn t work for me  but taking it offline  and then bringing it back online did work   It s in the right-click menu of the DB  under Tasks   Be sure to check the  Drop All Active Connections  option in the dialog

User · Answer

I think you just need to set the db to single user mode before attempting to restore  like below  just make sure you re using master  USE master GO ALTER DATABASE AdventureWorksDW SET SINGLE USER

User · Answer

Use Master alter database databasename set offline with rollback immediate   --Do Actual Restore RESTORE DATABASE databasename FROM DISK    path of bak file  WITH MOVE  datafile data  TO  D  newDATA data mdf   MOVE  logfile Log  TO  D  newDATA DATA log ldf  replace  alter database databasename set online with rollback immediate  GO

User · Answer

I experienced this issue when trying to restore a database on MS SQL Server 2012  Here s what worked for me  I had to first run the RESTORE FILELISTONLY command below on the backup file to list the logical file names  RESTORE FILELISTONLY      FROM DISK    C  Program Files Microsoft SQL Server MSSQL12 MSSQLSERVER MSSQL Backup my db backup bak   This displayed the LogicalName and the corresponding PhysicalName of the Data and Log files for the database respectively  LogicalName      PhysicalName                com my db        C  Program Files Microsoft SQL Server MSSQL12 MSSQLSERVER MSSQL DATA com my db mdf com my db log    C  Program Files Microsoft SQL Server MSSQL12 MSSQLSERVER MSSQL DATA com my db log ldf  All I had to do was to simply replace the LogicalName and the corresponding PhysicalName of the Data and Log files for the database respectively in my database restore script  USE master  GO  ALTER DATABASE my db SET SINGLE USER WITH ROLLBACK IMMEDIATE  GO       RESTORE DATABASE my db     FROM DISK    C  Program Files Microsoft SQL Server MSSQL12 MSSQLSERVER MSSQL Backup my db backup bak      WITH REPLACE      MOVE  com my db  TO  C  Program Files Microsoft SQL Server MSSQL12 MSSQLSERVER MSSQL DATA com my db mdf       MOVE  com my db log  TO  C  Program Files Microsoft SQL Server MSSQL12 MSSQLSERVER MSSQL DATA com my db log ldf  GO      ALTER DATABASE my db SET MULTI USER  GO  And the Database Restore task ran successfully  That s all  I hope this helps

User · Answer

I ll assume that if you re restoring a db  you don t care about any existing transactions on that db   Right   If so  this should work for you   USE master GO  ALTER DATABASE AdventureWorksDW SET SINGLE USER --This rolls back all uncommitted transactions in the db  WITH ROLLBACK IMMEDIATE GO  RESTORE DATABASE AdventureWorksDW FROM         GO   Now  one additional item to be aware   After you set the db into single user mode  someone else may attempt to connect to the db   If they succeed  you won t be able to proceed with your restore   It s a race   My suggestion is to run all three statements at once

[sql] SQL-Server: Error - Exclusive access could not be obtained because the database is in use

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008