'
*************************************************************************
' **模 块 名:fBackupDatabase_a
' **描 述:备份数据库,返回出错信息,正常恢复,返回""
' **调 用:fBackupDatabase_a "备份文件名","数据库名"
' **参数说明:
' ** sBackUpfileName 恢复后的数据库存放目录
' ** sDataBaseName 备份的数据名
' ** sIsAddBackup 是否追加到备份文件中
' **说 明:引用Microsoft ActiveX Data Objects 2.x Library
' **创 建 人:邹建
' **日 期:2003年12月09日
' *************************************************************************
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
' **模 块 名:fBackupDatabase_a
' **描 述:备份数据库,返回出错信息,正常恢复,返回""
' **调 用:fBackupDatabase_a "备份文件名","数据库名"
' **参数说明:
' ** sBackUpfileName 恢复后的数据库存放目录
' ** sDataBaseName 备份的数据名
' ** sIsAddBackup 是否追加到备份文件中
' **说 明:引用Microsoft ActiveX Data Objects 2.x Library
' **创 建 人:邹建
' **日 期:2003年12月09日
' *************************************************************************
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