Ensure you are in the [master] database, not the database you are trying to RESTORE, otherwise you become the connection that is preventing exclusive access to the DB.

Regards,

-Darius

• Proposed as answer by Lucian L Friday, January 07, 2011 12:02 AM

detach and reattach the database. This will kick all sessions off and allow the restore to take place.
No its not very clever way of resolving these issues, but its effective with out being drastic.

Thank you Darius

it worked

Regards,

Nabi

Thank you Darius !

It worked very good !

• Proposed as answer by GS2010 Wednesday, July 28, 2010 7:13 PM

Thanks for all the replies - they were helpful.

If anyone wants to know - I had to stop server - try backup - unable to connect to server - Start server - backup and it worked.

Then I went to log in but it reported failure to load database and after about 3 goes or 2 mins it was all restored.

I had the same problem. If you use management studio, choose the option in the restore window which says "Restrict access to the restored database" and it would resolve the issue.

After the restore is complete reset the database back to multi_user mode.

bru, 

select database , bring it offline and bring it online and try to restore database 

that worked for me

Tiki

Following How to Get Exclusive Access to SQL Server Database article will be of help to you.

Hope This Helps!

Thanks
Ashish Kumar Mehta

The way we fixed it was a bit more simpler then all these:

(using SQL Server 2005 as an example)

1.  Go to the Activity Monitor within Management dropdown

2.  On the left pane under "Select a page" click on "Locks by Object".

3.  Scroll over to the right & sort by "Database".

4.  Find the database that is appears to be locked (and trying to restore) and take note of the "Process ID" on the left.

5.  Click back to "Process Info" under "Select a page" and find the Process ID # from step #4.

6.  KILL IT WITH SWIFT PREJUDICE!

7.  Activate restore.

8.  Snicker as your restore is happening & the offending person is trying to reconnect.

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com

i had this problem too, but i resolved it.

u should:

because of u are using ur db so u can not restore it by itself, therefor u should login with another db like master then u can restore ur db

        public void RestoreBackup(string DBName, string Path, string BackupFileName)
        {
            SqlConnection con = new SqlConnection("data source=Bahman-pc;Initial Catalog=Master;User ID=sa;Password=123;Integrated Security=True;   Trusted_Connection=true;");
            SqlCommand com = new SqlCommand();
            string UseMaster = "USE master";
            string restore = "RESTORE DATABASE Battalion FROM DISK ='" + Path+BackupFileName + "' WITH FILE = 1 , REPLACE";           
            string Alter1 = @"ALTER DATABASE [" + DBName + "] SET Single_User WITH Rollback Immediate";           
            string Alter2 = @"ALTER DATABASE [" + DBName + "] SET Multi_User";
            try
            {
                con.Open();
                com.Connection = con;
                com.CommandText = UseMaster;
                com.ExecuteNonQuery();
                com.CommandText = Alter1;
                com.ExecuteNonQuery();
                com.CommandText = restore;
                com.ExecuteNonQuery();
                com.CommandText = Alter2;
                com.ExecuteNonQuery();               
            }
            catch (Exception ex)
            {
                com.CommandText = Alter2;
                com.ExecuteNonQuery();
            }
            finally
            {
                com.Clone();
            }
        }

• Proposed as answer by yanzi0407 Thursday, September 01, 2011 7:04 AM
• Unproposed as answer by yanzi0407 Thursday, September 01, 2011 7:04 AM

Hi,

I am having the same issue on a SQL 2008 database.  The website is not live yet, so other than me, nobody would be trying to connect to it.  The database is on a sql only web server.  The website is on another web server.  I have several identical sites with their respective databases all running on these machines with no issue.

When I try to run:

Use Master
  
Alter Database myDatabaseName
  SET SINGLE_USER With ROLLBACK IMMEDIATE 

(of course putting my actual database name there :)

I get this error:

Any ideas what's up?

thanks

David

use Master 

ALTER DATABASE yourdatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

restore database databasename

from ...

Thanks Madhu

Open SQL Management Studio and press F8 to open Object Explorer.

There you can find Management Click on Management to view Activity Monitor.

• Proposed as answer by desperatenaveed Saturday, June 23, 2012 9:34 AM

• Proposed as answer by siauwei Tuesday, September 18, 2012 8:13 AM
• Unproposed as answer by siauwei Tuesday, September 18, 2012 8:13 AM

Very Tanks for:

SqlConnection.ClearAllPools();

I wrote VB.net code to unlock the database. I was tired of always doing this manually, so I wrote a command-line tool in VB.Net to kill database locks . Replace 'test' with your database name and replace my connection string with your own. Connection strings are a different topic all together.

• Edited by RandomMicrosoftUser Monday, July 21, 2014 3:10 PM