请教：delphi控制SQL Server数据恢复的问题！(好邪门！!!!!数据库一直被占用！)(最多可以加到1000分)

Question

我用
    backup database mydb to disk='c:\mydb.bak'
把数据库备份下来。
然后要通程序把c:\mydb.bak还原到数据库。sql语句如下：
    restore database mydb from disk='c:\mydb.bak'
我知道数据库不能被打开，所以，在恢复前，我试着关闭已经存在的数据库连接，用了如下语句：    AdoConnection1.close();                  //这两个语句的所有组合我都试过了
    AdoConnection1.Connected:=false;         
然后我用一个新的AdoConnection连接到master数据库。
然后执行sql:
    restore database mydb from disk='c:\mydb.bak'
提示数据库正在使用。
我再试着改变
    AdoConnection1.ConnectionString，用原来的AdoConnection对象连接到master，连接没有出错。（我试过执行select * from spt_fallback_db，这个表在mydb中是没有的，执行成功）然后我再执行
    restore database mydb from disk='c:\mydb.bak'
还是提示数据库被占用！为什么我明明已经连接到master数据库了，mydb数据库还使用？难道一被连接就得等到程序关闭才能被释放？我几乎把所有的可能存在的方法试过了，都不行啊，是不是只能把数据恢复做成一个独立的EXE文件？各位做过Sql server备份的提供点经验、代码、思路...谢谢

Answer 1

在Master数据库中建如下存储过程：
Create Procedure killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for 
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status<>-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
endGO
在恢复数据库之前先执行此存储过程，像这样：Use Master
Go
exec killspid 'mydb'
Go
restore database mydb from disk='c:\mydb.bak'在Delphi中直接用ExecSQL先后执行以上SQL语句即可

Answer 2

你说的问题我还真没有碰见过!不过我得想法是：当你连接SQL SERVER之后，这个连接通道已经被这个连接给占用了，即使你关闭了这个通道，但实际上只是不执行而已，数据库的通道并没有被释放出来。所以当你恢复之前，应该改变以下连接，当你下一次再使用时，仍然需要释放数据库通道，也就是改变数据库连接。
这只是鄙人的想法，是否正确有待进一步证实！

Answer 3

很简单的！
AdoConnection1不用断开，
使用一个adoquery,SQL语句如下：
adoquery1.SQL.Clear;
adoquery1.SQL.Add('use master');
adoquery1.SQL.Add('restore database mydb from disk='''c:\mydb.bak''');
adoquery1.SQL.Add('use mydb');恢复数据库之前一定要user master !

Answer 4

补上贴，adoquery1.connectionstr可以用adoconnection的。

Answer 5

在“查询分析器”中输入：sp_who看一下，就知道了：）

Answer 6

i agree to :xiaohuitu(小灰兔) (

Answer 7

unit backup;interfaceuses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, StdCtrls, Buttons, ComCtrls, DB, ADODB;type
  TFbackup = class(TForm)
    Panel1: TPanel;
    StatusBar1: TStatusBar;
    Panel3: TPanel;
    btnClose: TBitBtn;
    btnBackup: TBitBtn;
    btnRestore: TBitBtn;
    pgBar: TProgressBar;
    Label1: TLabel;
    SaveDialog1: TSaveDialog;
    ADOQuery1: TADOQuery;
    procedure btnBackupClick(Sender: TObject);
    procedure btnRestoreClick(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  Fbackup: TFbackup;implementation
uses dldata;
{$R *.dfm}procedure TFbackup.btnBackupClick(Sender: TObject);
var
  i:integer;
  device_is:boolean;
begin
with pgbar do
begin
pgbar.Max:=100;
pgbar.Min:=0;
pgbar.Position:=0;
pgbar.Step:=20;
end;
with savedialog1 do
  begin
    filter:='备份文件(*.dat)*.dat';
    defaultext:='dat';
    filename:='';
    options:=[ofhidereadonly,offilemustexist,ofpathmustexist];
    device_is:=false;
    if execute then
      begin
        statusbar1.Panels[1].Text:='正在备份中....';
try
        with adoquery1 do
          begin
            close;
            sql.Clear;
            sql.Add('execute sp_helpdevice');
            open;
            pgbar.Stepit;
            first;
            while not eof do
              begin
                if trim(fieldbyname('device_name').AsString)='dl_1' then
                  begin
                    device_Is:=true;
                    break;
                  end;//if fieldbyname('name').AsString='dl_1' then
                  next;
              end;//for i:=0 to recordcount-1 do
              pgbar.Stepit;
            //-----------------------------------------------------------
            if device_Is then
              begin
                close;
                sql.Clear;
                sql.Add('execute sp_dropdevice ''dl_1''');
                execsql;
              end;//if not device_Is then
              pgbar.Stepit;
            //------------------------------------------------------------
            close;
            sql.Clear;
            sql.Add('execute sp_addumpdevice ''disk'',''dl_1'','''+filename+'''');
            execsql;
            //--------------------------------------------------------
            pgbar.Stepit;
            close;
            sql.Clear;
            sql.Add('backup database dl to dl_1');
            execsql;
            pgbar.Stepit;
          end;//with adoquery1 do
except
  showmessage('备份失败！');
end;//try
      end;//if execute then
  end;//with savedialog1
  statusbar1.Panels[1].Text:='备份完成。';
end;
procedure TFbackup.btnRestoreClick(Sender: TObject);
begin
with pgbar do
begin
pgbar.Max:=100;
pgbar.Min:=0;
pgbar.Position:=0;
pgbar.Step:=50;
end;
if messagedlg('恢复数据库后必须重新登陆！是否继续？',mtwarning,[mbok,mbcancel],0)=mrok then
begin
statusbar1.Panels[1].Text:='正在恢复中....';
dm.dlconnection.Close;
dm.Free;
//try
with adoquery1  do
  begin
    close;
    sql.Clear;
    sql.Add('restore database dl from dl_1');
    execsql;
  end;//with do
//except
//  showmessage('备份文件不存在！ss');
//end;
pgbar.StepIt;pgbar.StepIt;
statusbar1.Panels[1].Text:='恢复完成.';
application.MainForm.Caption:='正在关闭系统....';
winexec(pchar(application.exename),sw_show);
application.Terminate;
end;//if messagedlg()=mrok
end;procedure TFbackup.btnCloseClick(Sender: TObject);
begin
close;
end;procedure TFbackup.FormClose(Sender: TObject; var Action: TCloseAction);
begin
action:=cafree;
end;end.

Answer 8

我用下面的代码没有问题,即使在调试环境中 -------------------------------------------------------unit Unit1;interfaceuses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB;type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    Button1: TButton;
    Button2: TButton;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  Form1: TForm1;implementation{$R *.dfm}procedure TForm1.FormCreate(Sender: TObject);
begin
  ADOConnection1.Open ; 
end;procedure TForm1.Button1Click(Sender: TObject);
begin
  Caption := '正在备份中...' ;
  with ADOQuery1 do
  begin
    Close ;
    Sql.Text := 'Backup DataBase Hdcims to disk = ''D:\hdcims.bak''' ;
    ExecSql ;
  end ;
  Caption := '备份完成 !' ;
end;procedure TForm1.Button2Click(Sender: TObject);
begin
  Caption := '正在恢复中...' ;
  with ADOQuery1 do
  begin
    Close ;
    Sql.Add('use Master') ;
    Sql.Add('Restore DataBase Hdcims From Disk = ''D:\Hdcims.Bak''') ;
    ExecSql ;
  end ;
  Caption := '恢复完成 !' ;end;end.

Answer 9

谢谢forgot2000(忘记2000年)的sp，100分！
    别外，这个sp好像只能放在master库中，如果放在mydb中，会提示无法删除自己的进程，如果先Use master，又说不能找到这个sp。而从这个库调用的另一个库的sp我不太清楚（学delphi+sql server才1个月,不过，我学过vb,asp...），不过从逻辑上来讲，是行不通的（就像不能引一个已经关闭的Form中的变量或控件一样）。谢谢cookieyfeng(怪癖幽魂)的想法，50分
    我先用语句断开连接，然后用sp_who看了一下，真的还没有断开，装态为sleeping.非常感谢xiaohuitu(小灰兔)，100分+本贴50分！
    你的做法好简单，却帮了我的大忙，我一时晕了头，把use给忘记了。谢谢gmc007(江西的佬表) ，本贴子40分！
   让我知道了还有sp_who的这sp，好用。一个同事搞了两年SQL Server还不知道有这个呢，[:)  也许这个很少用吧，但有用！剩下10给第一个帮我UP的人。
其它的分数另外贴子给分。

Answer 10

我觉得xiaohuitu(小灰兔)的做法在并发操作时会有问题，当然，如果是对单机而言，大家的做法都是正确的，具体搂主不妨试一试，在多台机器同时通过不同的指令来访问某个数据库时，你必须先断开所有与这个数据库相关的事务才能进行恢复操作。

Answer 11

谢谢。你所说的那个问题我知道。你那个存储过程我也试过了，很好用，而且我现在也找到了一种方法：
先用
try...except...end;
关掉其它的连接，然后，再把当前的连接改到master数据库，这样就不会了(不过，我程序中已经说明必须断开所有的客户端程序)。
还有一个问题是：怎样把数据备分到客户端，我知道可以通过共享目录备份下来。但好像不是很完美，我们的程序不可能要求每个客户端都共享一个目录。我的做法是：
在服务器上开一个共享，把文件备份到那个共享目录，然后再把文件拷贝到客户端。不知道有没有更好的办法？此贴继续有效！
因为 forgot(忘记forgot2000)大哥/大姐不要分，下面的贴子有100，作为散分的贴子。要分的可以去那里UP。
http://expert.csdn.net/Expert/topic/1378/1378665.xml?temp=.367016

Answer 12

要把其他所有使用这个数据库的进程都kill掉，用sqlserver的kill命令

Answer 13

这个我知道:
forgot大哥（女士在这里很少，就这样叫了）已经做了一个存储过程：Create Procedure killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for 
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status<>-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
GO

Answer 14

在服务器上开共享目录可以，但必须要求是对所有用户完全共享的目录（即既可读又可写），备份路径直接写网络路径即可。
forgot和forgot2000是同一个人，是男是女你查旧帖子吧！

Answer 15

我很开心，我以前只是在这里查询资料，看别人的答案，很少帮别人回答问题（是不是好自私？）。其实帮别人回答问题也很有成就感。：）
   这个备份问题我以前也碰到过，也尝试了好久和好多方法，包括大家写的相类似的存储过程，不过我到后来发觉还是自己的最快最简洁。对于SQL数据库来说，一定要独占方式下才能恢复，哪怕你用相同的用户在其他客户机上同时登录也不行。你可以用系统存储过程检查谁已登录或KILL他们。但是，对于备份本身这个动作，一定要独占方式，而且是use master 而不是use mydb,明白这一点就会发现就是这么简单。

Answer 16

谢谢
xiaohuitu(小灰兔) 这里拿分
http://expert.csdn.net/Expert/topic/1378/1378693.xml?temp=.1416742

Answer 17

TO forgot(忘记forgot2000):其实，我说的意思不是可不可以的问题，因为我已经试过，而且很成功。但总要共享一下目录，有定麻烦。不过，这是我目前知道的最好的方式。所以我想知道有没有更好的方式(人心不足蛇吞象，呵呵~~)。

Answer 18

create   PROCEDURE  GY_DBBak  
          @bakequip    int,         --  备份设备：磁盘&磁带  
          @bakpath     varchar(50), --  带全路径的备份文件名  
          @baktype     int,         --  完全备份&增量备份  
          @baklog      int,         --  ‘0’备份日志  
          @bakdb       int,         --  ‘0’备份数据库  
          @kind  varchar(7),        --备份还是恢复  
          
  @retmsg  varchar(20)  output     --返回信息  
AS  
   DECLARE  @DevName_data    varchar(50)  
   DECLARE  @DevName_log   varchar(50)
   declare @db_path varchar(100)
   declare @log_path varchar(100)
       
   DECLARE  @RC INT     SELECT    @db_path    =  @bakpath    +  '.dat'  
SELECT    @log_path   =  @bakpath    +  'log.dat'  
        SELECT    @RC=0   DBCC  CHECKDB(Northwind)  
/***********************************************************
** CREATE BACKUP AND RESTORE DEVICES
************************************************************/
IF @RC=0
   BEGIN

    EXEC sp_addumpdevice 'disk', @DevName_data,@db_path

exec sp_addumpdevice 'disk', @DevName_log,@log_path
    select @rc=@@error
IF @RC<>0
begin
EXEC SP_DropDevice @Devname_data
exec sp_dropdevice @devname_log
SELECT @RC=-1000
return @rc
end
  END

   IF  @kind='backup'  
   BEGIN  
       IF  @bakequip=0  
       BEGIN  
           IF  @baktype=0  
           BEGIN  
               IF  @bakdb=0  
               BEGIN    
                   BACKUP  DATABASE  Northwind  TO  DISK=@Devname_data   
                   WITH  INIT  
               END  
               IF  @baklog=0  
               BEGIN              
                   BACKUP  LOG  Northwind  WITH  NO_LOG              
                   BACKUP  LOG  Northwind  TO  DISK=@DevName_log 
                   WITH  INIT,NO_TRUNCATE  
               END  
           END  
           ELSE  BEGIN  
               IF  @bakdb=0  
               BEGIN  
                   BACKUP  DATABASE  Northwind  TO  DISK=@DevName_data
                   WITH  NOINIT  
               END  
               IF  @baklog=0  
               BEGIN  
                   BACKUP  LOG  Northwind  WITH  NO_LOG              
                   BACKUP  LOG  Northwind  TO  DISK=@DevName_log 
                   WITH  NOINIT,NO_TRUNCATE  
               END  
           END          
       END  
       SELECT  @retmsg='数据库备份成功！'  
   END  
 
   IF  @kind='restore'      
   BEGIN  
       RESTORE  DATABASE  Northwind  FROM  DISK=  @DevName_data WITH  REPLACE  
       SELECT  @retmsg='恢复数据库成功！'  
   END  
 
   RETURN  0

Answer 19

或用这个来杀进程：create  proc  killspid  (@dbname  varchar(20))  
as  
begin  
declare  @sql  nvarchar(500)  
declare  @spid  int  
set  @sql='declare  getspid  cursor  for    
select  spid  from  sysprocesses  where  dbid=db_id('''+@dbname+''')'  
exec  (@sql)  
open  getspid  
fetch  next  from  getspid  into  @spid  
while  @@fetch_status  <  >-1  
begin  
exec('kill  '+@spid)  
fetch  next  from  getspid  into  @spid  
end  
close  getspid  
deallocate  getspid  
end  --用法  
use  master  
exec  killspid  '数据库名'

Answer 20

我靠！整理得半死的FAQ居然不能提交，那个页面出错！