none
Unable to restore database "..because it is in use by this session." RRS feed

  • Question

  • Hi,

     

    I am unable to restore a db on SQL Server 2005 running on XP Professional. The backup was made on the same machine just a few hours previously. The error msg is:

     

    Restore failed for Server 'Server_Name'. (Microsoft.SqlServer.Smo)

    System.Data.SqlClient.SqlError: RESTORE cannot process database 'DbName' because it is in use by this session. It is recommended that the master database be used when performing this operation.

     

    However, there are no other apps currently connected to the db and the db is not involved in any query windows. This should be a perfectly routine restore, which I have done hundreds of times on many servers. I rebooted the server and still the same error.

     

    Any suggestions?

     

    Thanks.

    Dan

    Friday, June 15, 2007 2:10 AM

Answers

  •  

    Hi - I just ran into this problem too, and my issue was I was restoring a database that I also was using as my default database.

     

    So, by changing my user default database to the master db, I was able to restore.

     

    Hope that helps.

    Tuesday, December 18, 2007 12:17 AM
  • Your login to the server cannot have the database as its default. Go to Security > Logins > your login user. Right click to get properties and where it says Default, put the database back to "Master." It doesn't take you out of the other db, just takes it out of that connection for the restore. I'd routinely restored but must have one day changed the default thinking it wouldn't hurt anything. Now I know it hurts restores to that db if I log onto the server as that connection with that default! Hope this helps clear it up.

    Wednesday, November 19, 2008 2:29 AM
  • I'm assuming that you are using Enterprise Manager or SQL Server Management Studio.

     

    You are connected to the database.

     

    With SSMS, close all query windows until the only window left open is [Object Exporer Details]. In the [Object Explorer] pane, click on any database OTHER than the one you want to RESTORE. IF there are no others listed, click on [System Databases], then click on the Master database.

     

    With EM, close all windows until you have on [Console Root], then click on the Master database

     

    Now try your RESTORE again.

    Friday, June 15, 2007 2:36 AM
    Moderator
  • I got the same problem. After my hard research, I found it is because your login is using your destined database as default database. SSMS is using your login default database as working platform and always keeps a session (connection) alive. So you just need to go to your login properties and change the "Default database" to something else. It works for me!
    Tuesday, January 8, 2008 7:59 PM
  • You do NOT want to be connected to the database. That is the problem.

     

    Connect to a different database, such as Master. In Object Explorer, you do not want to have the database in question selected.

    Friday, June 15, 2007 2:25 PM
    Moderator
  • Hi rebton - thanks for your suggestion.

    I had the same problem and changing my default database sorted it, although I did have to close down SSMS and reopen for the changes to take effect.

    Wednesday, January 2, 2008 3:38 PM
  • I ran into the same problem.

     

    Thanks for the suggestion. I changed my Default database and it worked for me.

     

    Monday, October 27, 2008 9:05 PM
  • Hi All,

    It's an old post, but high rank, so I will add one option. Default database might be specified for login used. Change that to master in Object Explorer tab under Security >> Logins >> Your Login.

    Thanks,
    Montek

     


    "Do you think I am what I am without being what I'm not?"
    Thursday, July 8, 2010 10:53 AM
  • FWIW:

    The issue is that the your user's default database is the on you are trying to update or is offline.

    I know this solution is stated in other posts but here's how got past the issue.  Both the windows login I was using and the "sa" login were both pointing to the database that I was trying to update as their default database (don't ask).

    This solution works for both the SQL 2005 and SQL EM (SSMS):

    When I got the connection dialog box right after SQL startup, I clicked the "Options>>" button and typed in "master" (no quotes).  Once I connected (I was getting a login error because the database I was trying to update was offline from a previous troubleshooting step), I ran this:

    USE [master]
    GO
     
    ALTER DATABASE yourDbHere SET ONLINE
    GO

    ALTER LOGIN [yourLoginHere] WITH DEFAULT_DATABASE=[master]
    GO

    Disconect and reconnect to your DB Engine and you're good to go.

    Rock on

    Thursday, July 22, 2010 12:42 AM

All replies

  • I'm assuming that you are using Enterprise Manager or SQL Server Management Studio.

     

    You are connected to the database.

     

    With SSMS, close all query windows until the only window left open is [Object Exporer Details]. In the [Object Explorer] pane, click on any database OTHER than the one you want to RESTORE. IF there are no others listed, click on [System Databases], then click on the Master database.

     

    With EM, close all windows until you have on [Console Root], then click on the Master database

     

    Now try your RESTORE again.

    Friday, June 15, 2007 2:36 AM
    Moderator
  • Yes, I am using SSMS and I am clearly connected to the db. There are no query windows open. I have checked the option to Overwrite the existing  database, and the Restore As paths are correct.

     

    I suspect I can drop the db and then restore, but that has never been necessary before and should not be necessary now.

     

    I am stumped.

    Friday, June 15, 2007 11:15 AM
  • You do NOT want to be connected to the database. That is the problem.

     

    Connect to a different database, such as Master. In Object Explorer, you do not want to have the database in question selected.

    Friday, June 15, 2007 2:25 PM
    Moderator
  • First of all, I routinely restore in exactly this way and it has never been a problem before on any server.

    Second, it is not clear what you mean by not connected to the database. I do have a connection to the server in SSMS, and the db in question is one of the db's on the server. However, I did click on one of the other db's on the server and tried, and got the same error.

     

    Thanks for any other suggestions you or anyone might have.

     

    Dan

    Friday, June 15, 2007 3:18 PM
  • What Arnie mentioning is .... when u restore a database, No user should be connected to the database. System will be trying for exclusive lock on db and if anyone is using it will throw this error.  Go to acivity monitor and check is there any user connected to this particular db if anyone is there kill that connection.

     

    or run Exec SP_Who or exec sp_who2 and check any user is connected to this database. If connected kill those connection. or

     

    or run this command to set the database in single user mode

     

    Alter database yourdatabasename set single_User with ROLLBACK IMMEDIATE 

     

    the above command will disconnect all the connection from the database. Then u can restore the database

     

     

    Madhu

     

     

    Saturday, June 16, 2007 11:51 AM
    Moderator
  • Attempt to restore this database using query editor than using SSMS.

    use master

    go

    restore database .,...

    Sunday, June 17, 2007 6:37 PM
    Moderator
  •  

    Hi,

     

    I have meet the same problem.

    At first, I use litespeed to restore my DB as usual, I got this error. And then, I closed all the connection to my DB and open a new query in SSMS, input

    "use master   

     Restore ...."

    The error still existed. At last, I restart the sql service and try again, It was still failedSad

     

    what's wrong with my DB? I executed "SP_Who" and "SP_Who2" and found there was not any connections in my DB.

     

    Who can tell me the reason and how to resolve it?

     

    Thank you! 

    Wednesday, November 28, 2007 12:40 AM
  • Sound weird everyone - at first it sounded like the normal issue where you have a rogue connection somewhere being missed, but with the reboots and sp_who2 proving no database connections exist, it sounds like you've uncovered a serious bug...

    I hope I never run into this issue when I need to restore in an emergency, but if I did, here's what I'd try:

     

    1. Try putting database in single-user mode before restore:

    Either use options in database properties, or ALTER DATABASE command; as I think someone's already suggested this, try:

     

    2. Start up SQL Server in single-user mode:

    Stop SQL Server service, then start SQL from the command-line with sqlservr -m (also -s server$instancename if not a default instance) and retry the restore.  sqlservr.exe is usually in CStick out tonguerogram Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

     

    3. Maybe try minimal config mode?

    As above, but use sqlservr -f (also -s server$instancename if not a default instance) instead of -m

     

    Good Luck Guys.

    Wednesday, November 28, 2007 1:22 PM
  •  

    Hi - I just ran into this problem too, and my issue was I was restoring a database that I also was using as my default database.

     

    So, by changing my user default database to the master db, I was able to restore.

     

    Hope that helps.

    Tuesday, December 18, 2007 12:17 AM
  • Hi rebton - thanks for your suggestion.

    I had the same problem and changing my default database sorted it, although I did have to close down SSMS and reopen for the changes to take effect.

    Wednesday, January 2, 2008 3:38 PM
  • I got the same problem. After my hard research, I found it is because your login is using your destined database as default database. SSMS is using your login default database as working platform and always keeps a session (connection) alive. So you just need to go to your login properties and change the "Default database" to something else. It works for me!
    Tuesday, January 8, 2008 7:59 PM
  • I ran into the same problem.

     

    Thanks for the suggestion. I changed my Default database and it worked for me.

     

    Monday, October 27, 2008 9:05 PM
  • Your login to the server cannot have the database as its default. Go to Security > Logins > your login user. Right click to get properties and where it says Default, put the database back to "Master." It doesn't take you out of the other db, just takes it out of that connection for the restore. I'd routinely restored but must have one day changed the default thinking it wouldn't hurt anything. Now I know it hurts restores to that db if I log onto the server as that connection with that default! Hope this helps clear it up.

    Wednesday, November 19, 2008 2:29 AM
  • Thanks rebton, saved me some time!!
    Tuesday, December 22, 2009 1:31 AM
  • Thanks a million. 

     

    The discussion was 'assumptive' in that changing the default database was the same as putting on socks. Everyone knew how to do it. NOT.  You did a great job putting some meat behind the comments. Much appreciated.

     

     


    D-B-S
    Wednesday, June 23, 2010 8:36 PM
  • Hi All,

    It's an old post, but high rank, so I will add one option. Default database might be specified for login used. Change that to master in Object Explorer tab under Security >> Logins >> Your Login.

    Thanks,
    Montek

     


    "Do you think I am what I am without being what I'm not?"
    Thursday, July 8, 2010 10:53 AM
  • FWIW:

    The issue is that the your user's default database is the on you are trying to update or is offline.

    I know this solution is stated in other posts but here's how got past the issue.  Both the windows login I was using and the "sa" login were both pointing to the database that I was trying to update as their default database (don't ask).

    This solution works for both the SQL 2005 and SQL EM (SSMS):

    When I got the connection dialog box right after SQL startup, I clicked the "Options>>" button and typed in "master" (no quotes).  Once I connected (I was getting a login error because the database I was trying to update was offline from a previous troubleshooting step), I ran this:

    USE [master]
    GO
     
    ALTER DATABASE yourDbHere SET ONLINE
    GO

    ALTER LOGIN [yourLoginHere] WITH DEFAULT_DATABASE=[master]
    GO

    Disconect and reconnect to your DB Engine and you're good to go.

    Rock on

    Thursday, July 22, 2010 12:42 AM
  • I had the same problem , I restarted the sql server and that helped (after defaulting to master )
    Thursday, November 11, 2010 5:52 AM
  • Thanks JRSEAP, solved the problem for me (after restarting SQL management studio).

    To Microsoft -> SERIOUSLY? HOW RIDICULOUSLY ABSURD ?! ?!

    Tuesday, December 7, 2010 10:45 PM
  • hi to all,
    I have the same problem, and been on the Link to find solution but finally reaches the solution.
    for this
    first Completly exit from MS SQL server and run again. In the first Connect to server windows Click -> Options
    a Connection Properties windows will appear Change Conect to Datebase: master
    and click connect now if you click on Query you will see master next to disconnect icon. now you r connected with master
    if you see your database(in which you want to restore) name next to disconnect it mean you are not connected to master so need to exit again and run SQL again
    now if you restore any backup to any existing Database it will not create any error. But make sure you have taken backup of Self created Functions and Storeprocedures.
    as may the second copy did not have these ones. the main ideas behind is you cant restore backup in the same database in which you are connected.

    Eric

    Ericcisco

    Friday, January 28, 2011 11:42 AM
  • Hi,

     

       I am using SQL 2008 R2, while restoring database i am getting the following error:pls guide me

    Thanks in advance

    Jeriesh


    Cheers : Jeriesh
    Thursday, September 22, 2011 8:12 AM
  • Hello Jeriesh,

    Do the following:

    1. Expand Security->Logins

    2. Locate your account or the account being used for your connection.

    3. Right click on it and Select Properties

    4. Now from Login Properties window, make sure the database you are trying to restore is not set as a Default database. --> If it is, change default database to master and click Ok.

    Now you should be able to restore.

    Don't forget to change the default database back to whichever db it was set prior to your restore :)

    Let me know if this answers your question.

    Good luck!

    Bsharper

     

     


    • Edited by Bsharper Friday, October 21, 2011 10:07 PM
    Friday, October 21, 2011 10:06 PM
  • I was getting the same error and followed the suggestions, and now I received a different error.

    "The tail of the log for the database has not been backed up.  Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose.  Use the WITH REPLACE or WITH STOPAT clause of the restore statement to just overwrite the contents of the log."

    I'm not doing anything special.  I took a backup file from one database and want to restore that to an identical database.  I've used the backup option and now want to use the restore option.

    What am I doing wrong?

    Wednesday, October 26, 2011 3:46 PM
  • sometimes it just needs refresh ... do restart SQL services

    Monday, April 16, 2012 6:24 AM
  • If you put your DB in single_user mode, another connection (like SQL agent or even the application user) can take up that session and will still prevent the restore. I prefer to use restricted_user mode. This allows only elevated connections which the application user generally is not part of. Just my suggestion :)

    Stanley Johns K. MCTS/MCITP in SQL server 2008.

    Tuesday, April 17, 2012 4:50 AM
  • So you are overwriting a DB. What I do is, right click on the DB>properties>options and then I set the DB to restricted_user mode. This will prevent lower level user access to the DB and will allow my DB account to restore over the DB. Also make sure in your restore options, you have checked the box that indicates an overwrite of the DB.

    Stanley Johns K. MCTS/MCITP in SQL server 2008.

    Tuesday, April 17, 2012 4:53 AM
  • Thank you very much!! 5 years later and because of this post I was able to restore my corporate database with only 5 minutes down time.
    Tuesday, November 6, 2012 1:58 PM
  • Thanks for the info. I had something similar but not manually from management studio but in a job.

    I start the SQL from a batchfile via the task-scheduler (See below command).

    SQLCMD -S MYDBSERVER -U sqladmin -P SecretPass12! -i RestoreGlossTNew.sql -o RestoreglossTNew.LOG

    The RestoreGlossTNew.sql contains the following

    use Master
    RESTORE DATABASE GlossTest
    FROM DISK = 'C:\GLOSSBAK\GLOSSBAK.BAK'
    WITH MOVE 'GlossData_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\glosstest.mdf',
    MOVE 'GlossData_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\glosstest.ldf', REPLACE
    GO

    This script restores the database but also renames the database.

    I had a similar error and solved it by adding the use Master at the top.

    This script has been running for years this way and never was a problem. So perhaps I changed the default database for the user and this resulted in this problem.

    Hope this is useful for someone.

    Thursday, January 3, 2013 8:29 AM
  • Thanks. Worked then, works 6yrs later
    Friday, May 10, 2013 5:18 PM
  • Thank you each and every individual who gives back to the community by posting your thoughts and experiences, and possible solutions.  Here is an example of a problem that was seeming so unsolvable yet 10 seconds reading someone's post and my whole day changes for the better.

       This is cheers to every BLOG and EVERY BLOGGER  on our planet.  You guys make it a small world !!!!

    Thursday, February 6, 2014 6:04 PM
  • This worked for me.  Thank you so much.  I will make sure Master is selected as default on all my SQL servers. Thanks so much!
    Thursday, July 24, 2014 2:15 PM
  • I also encountered this problem when restoring a few databases.

    What I did are the following:

    1. Rename first the database for restoration using the following queries:

    ALTER DATABASE [database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    EXEC sp_renamedb N'[database_name]', N'[database_name]_OLD'

    This is to prevent other applications from using the database, just in case other unknown applications are persistently trying to connect/make queries to the database.

    2. Perform the actual restore.

    3. Rename the database back to its original name

    EXEC sp_renamedb N'[database_name]_OLD', N'[database_name]'

    3. Don't forget to set the database to multi-user again:

    ALTER DATABASE [database_name] SET MULTI_USER WITH ROLLBACK IMMEDIATE
    GO



    Saturday, April 16, 2016 8:48 AM
  • In case you are still encountering the error message "Exclusive access could not be obtained because the database is in use", you may want to set your database to multi_user first before doing the actual restore.

    ALTER DATABASE [database_name_OLD] SET MULTI_USER WITH ROLLBACK IMMEDIATE
    GO


    Saturday, April 16, 2016 8:56 AM
  • Thank you so much Ericcisco, this finally allowed me to avoid the one remaining database connection, and that in turn allowed me to do the restore.

    It is also the most "gentle" solution and does not need any before/after configuration changes. Fantastic

    Tuesday, August 30, 2016 1:46 PM
  • thank you I changed my default database to master.

    It works for me

    Wednesday, September 26, 2018 5:42 PM