sql2kdbm.pas

在自己的程序中完成SQK2K中的数据库的备份和恢复。虽然调用外部程序来完成

//MS SQL Service 2000 数据库维护(完全备份和恢复) -- 朱翔 2004-5-20
unit Sql2KDBM;


interface

uses
  Classes, WinSvc, Windows, ADODB, SysUtils;

type
  Sql2KMan=class //Sql Server 2K Service Manager
  private
    ServiceInstalled:Boolean;
    SHandle, MHandle:SC_HANDLE; // servcie & Manager Handle
//    SvcName:string;
    DBConnection:TADOConnection;
    Qry:TADOQuery;
    ConnectStr:string;
    procedure CheckServiceExists;//本地是否安装了SQL2KSVR?
    function DataSize(DataBaseName:string):Integer; //获得数据容量大小
    function GetErrorMessage(ErrorCode:DWORD):string;//根据操作系统返回的错误代码，翻译错误信息
  public
    procedure DoSql(SqlStr:string);//执行SQL语句
    function GetServiceStatus:Integer;//服务的运行状态
    function GetBackupDevice:TStringList;//在SQLSVR中已登记的备份设备列表, '设备'参照SQLSVR的备份设备概念
    function BootService(SingleAdmin:Boolean):boolean; //启动服务,可选单用户管理理员模式
    function ExecuteBackup(DataBaseName,BackupDevice:string):Boolean; //执行完全备份
    function ExecuteRestore(DataBaseName,BackupDevice:string):Boolean; //执行完全恢复
    procedure DeleteBackupDevice(DeviceName:string); //删除备份设备, Sql2K本身没有提供更改备份设备物理文件名的功能
    procedure AddBackupDevice(DeviceName,Filename:string); //添加备份设备
    procedure ShutDownService; //关闭服务
    property Installed:Boolean read ServiceInstalled; //本机是否已安装SQLSERVER2K的SERVICE
    property ServiceStatus:Integer read GetServiceStatus; //服务运行状态
    property ConnectString:string read ConnectStr write ConnectStr; //数据联接字串
    constructor Create();
    destructor Destroy();override;

end; //type Sql2KMan

const
//
  SvcName:PChar='MSSQLSERVER';

implementation

procedure Sql2KMan.AddBackupDevice(DeviceName, Filename: string);
begin
  DoSql('EXEC SP_ADDUMPDEVICE '+QuotedStr('disk')+' '+QuotedStr(DeviceName)+' '+QuotedStr(Filename));
end;

procedure Sql2KMan.CheckServiceExists;//本地是否安装了SQL2KSVR?
var
  BResult:Boolean;
  DisplayName:PChar;
  BufSize:DWORD;
  ErrorMsg:string;
  ErCode:DWORD;
begin

  MHandle:=OpenSCManager(nil, nil, GENERIC_READ+GENERIC_EXECUTE); //获得对SERVICE CONTROL MANAGER的枚举访问的句柄
  ErCode:=GetLastError();
  case ErCode of
    ERROR_ACCESS_DENIED:ErrorMsg:='ERROR_ACCESS_DENIED';
    ERROR_DATABASE_DOES_NOT_EXIST:ErrorMsg:='ERROR_DATABASE_DOES_NOT_EXIST';
    ERROR_INVALID_PARAMETER:ErrorMsg:='ERROR_INVALID_PARAMETER';
  else
    ErrorMsg:=GetErrorMessage(ErCode);
  end; //case ercode

  SHandle:=OpenService(MHandle, SvcName, SERVICE_ALL_ACCESS);
  ErCode:=GetLastError();
  case ErCode of
    ERROR_ACCESS_DENIED:ErrorMsg:='ERROR_ACCESS_DENIED';
    ERROR_INVALID_HANDLE:ErrorMsg:='ERROR_INVALID_HANDLE';
    ERROR_INVALID_NAME:ErrorMsg:='ERROR_INVALID_NAME';
    ERROR_SERVICE_DOES_NOT_EXIST:ErrorMsg:='ERROR_SERVICE_DOES_NOT_EXIST';
  else
    ErrorMsg:=GetErrorMessage(ErCode);
  end; //case ercode

  if SHandle=0 then
    ServiceInstalled:=False
  else
    ServiceInstalled:=True;

end;

constructor Sql2KMan.Create;
begin
  //设一个默认值
  ConnectStr:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=Karma;Initial Catalog=master;Data Source=.';
  CheckServiceExists; //影响 ServiceInstalled

end;

function Sql2KMan.DataSize(DataBaseName: string): Integer;
begin
  Exit;
end;

procedure Sql2KMan.DeleteBackupDevice(DeviceName: string);
begin
  DoSql('EXEC SP_DROPDEVICE '+QuotedStr(DeviceName));
end;

destructor Sql2KMan.Destroy;
begin
  CloseServiceHandle(SHandle); // 回收用于访SERVICE的句柄
  CloseServiceHandle(MHandle);
  inherited Destroy;
end;

function Sql2KMan.ExecuteBackup(DataBaseName,
  BackupDevice: string): Boolean;
begin
  DoSql('EXEC BACKUP DATABASE '+QuotedStr(DataBaseName)+' to '+QuotedStr(BackupDevice)+' with format, skip');
end;

function Sql2KMan.ExecuteRestore(DataBaseName,
  BackupDevice: string): Boolean;
begin
  DoSql('EXEC RESTORE DATABASE '+QuotedStr(DataBaseName)+' from '+QuotedStr(BackupDevice));
end;

function Sql2KMan.GetBackupDevice: TStringList;
var
  StatusCode:Integer;
begin
  Result:=TStringList.Create;
  if not(ServiceInstalled) then Exit; //服务没有安装，退出
  StatusCode:=GetServiceStatus; //取服务运行状态
  if StatusCode<>SERVICE_RUNNING then Exit; //服务不是在RUNNING中，退出

  try
    DBConnection:=TADOConnection.Create(nil);//创建联接
    DBConnection.ConnectionString:=ConnectStr;
    DBConnection.LoginPrompt:=False;
    DBConnection.Open;
    Qry:=TADOQuery.Create(nil);
    Qry.Connection:=DBConnection;//使用已创建的联接
    Qry.SQL.Text:='EXEC SP_HELPDEVICE';//取所有设备列表
    Qry.Prepared:=True;
    Qry.Open;
    Qry.Filter:='status=16'; //只显示磁盘文件设备
    Qry.Filtered:=True;
    Qry.First;
    while not(Qry.Eof) do
    begin
      Result.Add(Qry.Fields[0].Text); //偶数行为device_name
      Result.Add(Qry.Fields[1].Text); //奇数行为physical_name
      Qry.Next;
    end;
    Qry.Close;
  finally
    Qry.Free;
    DBConnection.Free;
  end;
end;

function Sql2KMan.GetServiceStatus:Integer;//服务运行状态
var
  SvcStatus:SERVICE_STATUS;
  BResult:BOOL;
  StatusStr:string;
begin
  BResult:=QueryServiceStatus(SHandle,SvcStatus);//查询状态

//  if BResult=0 then
  Result:=SvcStatus.dwCurrentState;//状态代码

  Exit;
  case SvcStatus.dwCurrentState of
    SERVICE_STOPPED:StatusStr:='stopped';
    SERVICE_START_PENDING:StatusStr:='start pending';
    SERVICE_STOP_PENDING:StatusStr:='stop pending';
    SERVICE_RUNNING:StatusStr:='running';
    SERVICE_CONTINUE_PENDING:StatusStr:='continue pending';
    SERVICE_PAUSE_PENDING:StatusStr:='pause pending';
    SERVICE_PAUSED:StatusStr:='paused';
  else
    Result:=GetLastError();
    case Result of
      ERROR_ACCESS_DENIED:StatusStr:='ERROR_ACCESS_DENIED';
      ERROR_INVALID_HANDLE:StatusStr:='ERROR_INVALID_HANDLE';
    else
      StatusStr:=GetErrorMessage(GetLastError());
    end; //case Result
  end;//case dwCurrentState

end;


procedure Sql2KMan.ShutDownService();
var
  SvcStatus:SERVICE_STATUS;
begin
  ControlService(SHandle,SERVICE_CONTROL_STOP,SvcStatus);
end;

function Sql2KMan.BootService(SingleAdmin: Boolean): boolean;
var
  SvcArg:PChar;
  Args:DWORD;
  ErCode:Integer;
  ErrorMsg:string;
begin

  if SingleAdmin then
  begin
    Args:=1; //有两个参数
    SvcArg:='-m -x';// -x'; //-m:在单用户管理员,模式下启动 SQL Server 实例 -x:禁用维护 CPU 统计。可以加快SQLSVR的速度
  end
  else
  begin
    Args:=0;//正常启动,没有参数
    SvcArg:=nil;
  end;

  ErCode:=Integer(StartService(SHandle,Args,SvcArg));//调用系统函数来启动

  ErCode:=GetLastError();
  case ErCode of
    ERROR_ACCESS_DENIED:ErrorMsg:='The specified handle was not opened with SERVICE_START access. ';
    ERROR_INVALID_HANDLE:ErrorMsg:=' The specified handle is invalid. ';
    ERROR_PATH_NOT_FOUND:ErrorMsg:=' The service binary file could not be found. ';
    ERROR_SERVICE_ALREADY_RUNNING:ErrorMsg:=' An instance of the service is already running. ';
    ERROR_SERVICE_DATABASE_LOCKED:ErrorMsg:=' The database is locked. ';
    ERROR_SERVICE_DEPENDENCY_DELETED:ErrorMsg:=' The service depends on a service that does not exist or has been marked for deletion. ';
    ERROR_SERVICE_DEPENDENCY_FAIL:ErrorMsg:=' The service depends on another service that has failed to start. ';
    ERROR_SERVICE_DISABLED:ErrorMsg:=' The service has been disabled. ';
    ERROR_SERVICE_LOGON_FAILED:ErrorMsg:=' The service could not be logged on. ';
    ERROR_SERVICE_MARKED_FOR_DELETE:ErrorMsg:=' The service has been marked for deletion. ';
    ERROR_SERVICE_NO_THREAD:ErrorMsg:=' A thread could not be created for the service. ';
    ERROR_SERVICE_REQUEST_TIMEOUT:ErrorMsg:=' The service did not respond to the start request in a timely fashion. ';
  else
    ErrorMsg:=GetErrorMessage(ErCode);
    ErrorMsg:=ErrorMsg+'';
  end; //case ercode

end;

procedure Sql2KMan.DoSql(SqlStr: string);
begin
  try
    DBConnection:=TADOConnection.Create(nil);
    DBConnection.ConnectionString:=ConnectStr;
    DBConnection.LoginPrompt:=False;
    DBConnection.Open;
    Qry:=TADOQuery.Create(nil);
    Qry.Connection:=DBConnection;
    Qry.SQL.Text:=SqlStr;
    Qry.Prepared:=True;
    Qry.ExecSQL;
    Qry.Close;
  finally
    Qry.Free;
    DBConnection.Free;
  end;
end;

function Sql2KMan.GetErrorMessage(ErrorCode:DWORD): string;
var
  ErrorStr:PChar;
begin
//  ErrorStr:='alsdkjfal;skdfj;alsdjf;alsdf;las';
  FormatMessage(FORMAT_MESSAGE_ALLOCATE_BUFFER or
                FORMAT_MESSAGE_FROM_SYSTEM or
                FORMAT_MESSAGE_IGNORE_INSERTS,
                nil,
                ErrorCode,
                LANG_NEUTRAL,//MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), // Default language
                @ErrorStr,
                SizeOf(ErrorStr),
                nil);
  Result:=ErrorStr;

end;

end.