Public Function BuckupDB(DBName As String, DeviceName As String, FilePath As String) As String Dim rst As New ADODB.Recordset
Dim LogicalName, FilPath, str As String Dim i As Integer
FilPath = FilePath + DeviceName i = InStr(DeviceName, ".") LogicalName = left(DeviceName, i - 1) On Error GoTo error If ConnectSMS("master") = 0 Then BuckupDB = "数据库打不开" Exit Function End If
'查找设备名是否存在,存在则删掉 '新建设备 str = "select * from sysdevices where name='" & LogicalName & "'" rst.Open str, con, adOpenStatic, adLockOptimistic If Not rst.EOF Then con.Execute "sp_dropdevice '" & LogicalName & "','delfile'" End If rst.Close con.Execute "sp_addumpdevice 'disk','" & LogicalName & "','" & FilPath & "'" '备份 con.Execute "BACKUP DATABASE " + DBName + " to " & LogicalName BuckupDB = "备份完毕!" CloseSMS Exit Function error: BuckupDB = err.description CloseSMS End Function
Public Function RestoreDB(DBName As String, LogicalName As String) As String Dim rst As New ADODB.Recordset Dim str As String Dim i As Integer If ConnectSMS("master") = 0 Then RestoreDB = "数据库打不开" Exit Function End If On Error GoTo error
'检查数据库是否已经存在 str = "select * from sysdatabases where name='" & DBName & "'" rst.Open str, con, adOpenStatic, adLockOptimistic If Not rst.EOF Then '删除数据库 con.Execute "DROP DATABASE " + DBName End If rst.Close '恢复 con.Execute "Restore database " + DBName + " FROM " + LogicalName RestoreDB = "恢复完毕!" CloseSMS Exit Function error: RestoreDB = err.description CloseSMS End Function
SQL(7.0)的备份与恢复 1.备份到文件时采用如下命令:(假设数据库名为ESchool,备份后的文件名为E:\1234.001) BACKUP DATABASE [ESchool] To DISK=`E:\1234.001`2.从文件中恢复时采用如下命令: RESTORE DATABASE ESchoolFROM DISK = `E:\1234.001` WITH REPLACE , MOVE `Backschool3_Data` TO `SQL路径:\ESchool.mdf`, Move `Backschool3_Log` TO `SQL路径:\ESchool.ldf`说明:`Backschool3_Data`与`Backschool3_Log` 一般是数据库生成时自动生成的名字,具体名字可用SQL Server中的工具查看。3.备份到数据库和从数据库中恢复则相对简单,可查看SQL Server 的帮助。
[email protected]
Dim rst As New ADODB.Recordset
Dim LogicalName, FilPath, str As String
Dim i As Integer
FilPath = FilePath + DeviceName i = InStr(DeviceName, ".")
LogicalName = left(DeviceName, i - 1) On Error GoTo error
If ConnectSMS("master") = 0 Then
BuckupDB = "数据库打不开"
Exit Function
End If
'查找设备名是否存在,存在则删掉
'新建设备
str = "select * from sysdevices where name='" & LogicalName & "'"
rst.Open str, con, adOpenStatic, adLockOptimistic
If Not rst.EOF Then
con.Execute "sp_dropdevice '" & LogicalName & "','delfile'"
End If
rst.Close
con.Execute "sp_addumpdevice 'disk','" & LogicalName & "','" & FilPath & "'"
'备份
con.Execute "BACKUP DATABASE " + DBName + " to " & LogicalName BuckupDB = "备份完毕!"
CloseSMS
Exit Function
error:
BuckupDB = err.description
CloseSMS
End Function
Dim rst As New ADODB.Recordset
Dim str As String
Dim i As Integer If ConnectSMS("master") = 0 Then
RestoreDB = "数据库打不开"
Exit Function
End If
On Error GoTo error
'检查数据库是否已经存在
str = "select * from sysdatabases where name='" & DBName & "'"
rst.Open str, con, adOpenStatic, adLockOptimistic
If Not rst.EOF Then
'删除数据库
con.Execute "DROP DATABASE " + DBName
End If
rst.Close
'恢复
con.Execute "Restore database " + DBName + " FROM " + LogicalName
RestoreDB = "恢复完毕!"
CloseSMS
Exit Function
error:
RestoreDB = err.description
CloseSMS
End Function
1.备份到文件时采用如下命令:(假设数据库名为ESchool,备份后的文件名为E:\1234.001)
BACKUP DATABASE [ESchool] To DISK=`E:\1234.001`2.从文件中恢复时采用如下命令:
RESTORE DATABASE ESchoolFROM DISK = `E:\1234.001` WITH REPLACE ,
MOVE `Backschool3_Data` TO `SQL路径:\ESchool.mdf`,
Move `Backschool3_Log` TO `SQL路径:\ESchool.ldf`说明:`Backschool3_Data`与`Backschool3_Log` 一般是数据库生成时自动生成的名字,具体名字可用SQL Server中的工具查看。3.备份到数据库和从数据库中恢复则相对简单,可查看SQL Server 的帮助。