*************************************************************************
'**模 块 名：fBackupDatabase_a
'**描 述：备份数据库,返回出错信息,正常恢复,返回""
'**调 用：fBackupDatabase_a "备份文件名","数据库名"
'**参数说明：
'** sBackUpfileName  恢复后的数据库存放目录
'** sDataBaseName 备份的数据名
'** sIsAddBackup  是否追加到备份文件中
'**说 明：引用Microsoft ActiveX Data Objects 2.x Library

'*************************************************************************
Public Function fBackupDatabase_a(ByVal sBackUpfileName$ _
  , ByVal sDataBaseName$ _
  , Optional ByVal sIsAddBackup As Boolean = False _
  ) As String
 
Dim iDb As ADODB.Connection
Dim iConcStr$, iSql$, iReturn$

On Error GoTo lbErr

'创建对象
Set iDb = New ADODB.Connection

'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=zj"
iDb.Open iConcStr

'生成数据库备份语句
iSql = "backup database [" & sDataBaseName & "]" & vbCrLf & _
   "to disk='" & sBackUpfileName & "'" & vbCrLf & _
   "with description='" & "zj-backup at:" & Date & "(" & Time & ")'" & vbCrLf & _
   IIf(sIsAddBackup, "", ",init")
  
iDb.Execute iSql
GoTo lbExit

lbErr:
iReturn = Error
lbExit:
fBackupDatabase_a = iReturn
End Function

'*************************************************************************
'**模 块 名：frestoredatabase_a
'**描 述：恢复数据库,返回出错信息,正常恢复,返回""
'**调 用：frestoredatabase_a "备份文件名","数据库名"
'**参数说明：
'** sDataBasePath  恢复后的数据库存放目录
'** sBackupNumber  是从那个备份号恢复
'** sReplaceExist  指定是否覆盖已经存在的数据
'**说 明：引用Microsoft ActiveX Data Objects 2.x Library
'**创 建 人：邹建
'**日 期：2003年12月09日
'*************************************************************************
Public Function fRestoreDatabase_a(ByVal sBackUpfileName$ _
  , ByVal sDataBaseName$ _
  , Optional ByVal sDataBasePath$ = "" _
  , Optional ByVal sBackupNumber& = 1 _
  , Optional ByVal sReplaceExist As Boolean = False _
  ) As String

Dim iDb As ADODB.Connection, iRe As ADODB.Recordset
Dim iConcStr$, iSql$, iReturn$, iI&

On Error GoTo lbErr

'创建对象
Set iDb = New ADODB.Connection
Set iRe = New ADODB.Recordset

'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=zj"
iDb.Open iConcStr

'得到还原后的数据库存放目录,如果没有指定,存放到SQL SERVER的DATA目录
If sDataBasePath = "" Then
  iSql = "select filename from master..sysfiles"
  iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
  iSql = iRe(0)
  iRe.Close
  sDataBasePath = Left(iSql, InStrRev(iSql, "\"))
End If

'检查数据库是否存在
If sReplaceExist = False Then
  iSql = "select 1 from master..sysdatabases  where name='" & sDataBaseName & "'"
  iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
  If iRe.EOF = False Then
   iReturn = "数据库已经存在!"
   iRe.Close
   GoTo lbExit
  End If
  iRe.Close
End If

'关闭用户进程,防止其它用户正在使用数据库,导致数据恢复失败
iSql = "select spid from master..sysprocesses where dbid=db_id('" & sDataBaseName & "')"
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly
While iRe.EOF = False
  iSql = "kill " & iRe(0)
  iDb.Execute iSql
  iRe.MoveNext
Wend
iRe.Close

'获取数据库恢复信息
iSql = "restore filelistonly from disk='" & sBackUpfileName & "'" & vbCrLf & _
  "with file=" & sBackupNumber
iRe.Open iSql, iDb, adOpenKeyset, adLockReadOnly

'生成数据库恢复语句
iSql = "restore database [" & sDataBaseName & "]" & vbCrLf & _
  "from disk='" & sBackUpfileName & "'" & vbCrLf & _
  "with file=" & sBackupNumber & vbCrLf
With iRe
  While Not .EOF
   iReturn = iRe("PhysicalName")
   iI = InStrRev(iReturn, ".")
   iReturn = IIf(iI = 0, "", Mid(iReturn, iI)) & "'"
   iSql = iSql & ",move '" & iRe("LogicalName") & _
  "' to '" & sDataBasePath & sDataBaseName & iReturn & vbCrLf
   .MoveNext
  Wend
  .Close
End With
iSql = iSql & IIf(sReplaceExist, ",replace", "")

iDb.Execute iSql
iReturn = ""
GoTo lbExit

lbErr:
iReturn = Error
lbExit:
fRestoreDatabase_a = iReturn
End Function