'*************************************************************************
'** 模 块 名: 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
'** 模 块 名: 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
'*************************************************************************
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
'** 模 块 名: frestoredatabase_a
'** 描 述:恢复数据库 , 返回出错信息 , 正常恢复 , 返回 ""
'** 调 用: frestoredatabase_a " 备份文件名 "," 数据库名 "
'** 参数说明:
'** sDataBasePath 恢复后的数据库存放目录
'** sBackupNumber 是从那个备份号恢复
'** sReplaceExist 指定是否覆盖已经存在的数据
'** 说 明:引用 Microsoft ActiveX Data Objects 2.x Library
'*************************************************************************
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