SqlBuddy [exclusive access could nout be obtained because the database is in use]错误的解决

http://www.koders.com/?s=SqlBuddy&scope=7P8U5XS5R165PYKSC24CUDPFHC&la=C%23

using System;
using SQLDMO;
namespace SqlBuddy.App.Commands
{
    /// <summary>
    /// Summary description for RestoreDB.
    /// </summary>
    public class RestoreDB :Command
    {
        private SQLDMO.Database _database;
        public override void Run(){
            Restore();
        }
        
        //nothing in constructor, so GUI pops up asking for File To restore and target database etc
        public RestoreDB(){
        }
        // target database set in constructor, GUI pops up asking for file to restore
        public RestoreDB(string targetDatabes){
        }
        
        //file  and target database set, so no GUI pops up, except error or confirmation dialog.
        public RestoreDB(string targetDatabes, string fileToRestore){
    
        }
        public override bool IsAvailable{ get{ return true; } }
        
        public void Restore(){
                //This is for restore
                SQLDMO.SQLServer srv = Engine.Instance.SqlServer;
                //create a restore class instance
                SQLDMO.Restore res = new SQLDMO.RestoreClass();                
                //set the backup device = files property ( easy way )
                res.Devices = res.Files;
                //set the file to restor from
                res.Files = "c://DBBackups//website_db_200301060200.BAK";
                //set the database to the chosen database
                res.Database = "website";
                // Restore the database
                res.ReplaceDatabase = true;                
                res.SQLRestore(srv);                
                                
        }
    }
}
=============
Step 2: Pause SQL Server 2000 Desktop Engine (Windows) to release exclusive locks

To pause WMSDE, follow these steps:

1.	Click Start, and then click Run.
2.	In the Open box, type cmd, and then press ENTER.
3.	At the command prompt, type the following lines and press ENTER after each line:net pause mssql$sharepoint net continue mssql$sharepoint
4.	Type exit, and then press ENTER.

===================

下面是摘自一个论坛的。
Hi!

We're using a backup with sql server agent when doing a backup / restore
procedure. In some cases I get the following error when the restore job
fails:

Executed as user: DOMAIN/Administrator. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error 3101)
RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.

Is it related to unhandled lock? I've resolved this by restarting the sql
server. But is there a way to avoid such issues?
One more question. Is it OK to backup/restore database while there're users
connected? Or I can do only backup?

Hi

You can backup the database when someone else is connected to it, but you
can't restore a database while someone is connected and you get the error
you describe. This may only be someone using EM or QA and having the
database selected in the object explorer window. Try a executing sp_who2 to
see who is connected to it first.
执行
sp_who2如果在DBName一行中有正在恢复的数据库，那么这可能是出现错误的原因。
John

Hi John!

Thanks for your reply!
This event occured when there was an Administrator connected to database
through the Microsoft Navision application.
Since, this doesn't usually happen it's OK. I will just make sure that noone
is connected.

下面是另外一个解决方案，我没有看懂。
===============
Hello Everyone, 
   Recently, I need to write a program for my company, 
 which is about backup and restore SQL Server 2000 database 
 through a VB6 application program. I use SQL DMO to do 
 this program. The backup part can run successful, but I 
 can't do the restore part. 
   The problem is that when I restore the SQL DB, the 
 program prompt "Exclusive access could not be obtained 
 because the database is in use." . It is because once I 
 open the VB6 application program, it connects to the SQL 
 server through ADO. 
   I would like to ask what method that I can use to 
 restore SQL DB in my VB6 application (because I must 
 restore it from this VB application, I do not pefer to use 
 SQL server enterprise manager). 
  Thank you 


In T-SQL you can set the database in single user or dbo-only mode with 
 ALTER DATABASE <database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE and 
 ALTER DATABASE <database> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE 
可以在程序正改正错误，怎么做呢？？？？？？？？？？ 
You can do the same from SQL-DMO by setting the DBOption.SingleUser and 

DBOption.DBOUseOnly properties to true.

Because you are going to restore the database you have to make sure that you
aren't connected to the database yourself either, just connect to master
instead. 

这句话的意思是执行sq_who2之后，DBName一览中不能有正在还原的数据库，只能有默认的master

--
Jacco Schalkwijk
SQL Server MVP


does this apply to SQL server 7 too?  I am trying this command from SQL 7 query analyzer:
USE PLTBO_030 alter database PLTBO_030 SET SINGLE_USER with rollback immediate
         RESTORE DATABASE PLTBO_030
      FROM DISK= 'e:/sqlbackup/PLTBO_030.bak' alter database PLTBO_030 SET MULTI_USER and getting a syntax error.
Thanks in advance !



Shelby,

'alter table....set' is SQL Server 2000's syntax and is not valid in SQL Server
7.0.

Try :

EXEC sp_dboption 'pubs', 'single user', 'TRUE'

--
- Vishal


correction, its

alter database....set

--
- Vishal

Thanks!