高手帮忙啊!我在vb6中用SQL-DMO做sql数据库的备份,程序出错。代码如下:
Dim oSQLServer As New SQLDMO.SQLServer
Dim DV As New SQLDMO.BackupDevice
oSQLServer.LoginSecure = False
oSQLServer.Connect "(local)", "sa","sa" '连接服务器
Set bkps = CreateObject("SQLDMO.Backup")
bkps.Database = "Northwind" '指定需备份的数据库
bkps.Action = 0 'SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
bkps.Files = "D:\test.bak" '指定备份文件
bkps.Initialize = True
ProgressBar1.Value = 0
ProgressBar1.Max = 100
DoEvents
bkps.SQLBackup oSQLServer '该处出错
'oSQLServer.BackupDevices.Remove "Northwindbak"
ProgressBar1.Value = 100
DoEvents
bkps = Nothing
MsgBox "数据库备份完成", vbOKOnly, "系统消息"错误提示是:实时错误‘-2147221499(80040005)’:
〔SQL-DMO〕代码执行异常错误:EXCEPTION_ACCESS_VIOLATION
Dim oSQLServer As New SQLDMO.SQLServer
Dim DV As New SQLDMO.BackupDevice
oSQLServer.LoginSecure = False
oSQLServer.Connect "(local)", "sa","sa" '连接服务器
Set bkps = CreateObject("SQLDMO.Backup")
bkps.Database = "Northwind" '指定需备份的数据库
bkps.Action = 0 'SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
bkps.Files = "D:\test.bak" '指定备份文件
bkps.Initialize = True
ProgressBar1.Value = 0
ProgressBar1.Max = 100
DoEvents
bkps.SQLBackup oSQLServer '该处出错
'oSQLServer.BackupDevices.Remove "Northwindbak"
ProgressBar1.Value = 100
DoEvents
bkps = Nothing
MsgBox "数据库备份完成", vbOKOnly, "系统消息"错误提示是:实时错误‘-2147221499(80040005)’:
〔SQL-DMO〕代码执行异常错误:EXCEPTION_ACCESS_VIOLATION
解决方案 »
- 关于禁止托盘右键启动该程序
- 如何将一个已经存在的recordset存放到access中去
- VB dataReport里怎么没有checkbox控件。我要打印怎么办啊
- 自定义报表??
- 谁有VB简体繁体转换源代码?
- 请教DCOM的配置问题
- 想学vb,用什么编译工具最好,再推荐一些好书,谢谢!
- 如何显示股票曲线图?????????????????????????
- 给csdn.net的几点建议.有同感者跟贴~
- 我用CommonDialog作打开文件时,如果按取消按钮仍打开上次文件,请问如何避免。
- 怎样禁止TDBGrid在AfterColEdit事件后读取数据库,自动刷新数据?
- 想问下大家学VB都是看什么代码的啊
"BACKUP DATABASE Northwind TO disk='D:\test.bak' with noinit"
下面的代码放到窗体代码里
Private Sub mnudatabackup_Click()
On Error GoTo ErrHandler Dim i As String
Dim SQLname As String
Dim iReturn As String
SQLname = Year(Now) & "年" & Month(Now) & "月" & "(QC).bak"
With CommonDialog1
.DialogTitle = "数据备份"
.FileName = SQLname
.Filter = "(数据库)*.bak|*.bak"
.CancelError = True
.ShowOpen
.InitDir = App.Path & "\Databackup"
End With
iReturn = fBackupDatabase_a(CommonDialog1.FileName, "jzd")
MsgBox iReturn
Exit Sub
ErrHandler:
' 用户按了“取消”按钮
MsgBox "用户取消备份操作或备份过程中出现错误,备份失败!", 48, "提示"
Exit Sub
End SubPrivate Sub mnudatarevert_Click()
On Error GoTo ErrHandler
Dim i As String
Dim iReturn As String
With CommonDialog1
.DialogTitle = "数据还原"
.FileName = ""
.Filter = "(数据库)*.bak|*.bak"
.CancelError = True
.ShowOpen
.InitDir = App.Path & "\Databackup"
End With
iReturn = fRestoreDatabase_a(CommonDialog1.FileName, "jzd")
MsgBox iReturn
Exit Sub
ErrHandler:
' 用户按了“取消”按钮
MsgBox "用户取消还原操作或还原过程中出现错误,还原失败!", 48, "提示"
Exit Sub
End Sub
'*************************************************************************
'**模 块 名: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$
Dim uid, pwd, addr As String
On Error GoTo lbErr
'读取数据库相关信息
uid = ReadIniFile(getiniPath("jzd.ini"), "Admin", "uid")
pwd = ReadIniFile(getiniPath("jzd.ini"), "Admin", "pwd")
addr = ReadIniFile(getiniPath("jzd.ini"), "Admin", "address")
'创建对象
Set iDb = New ADODB.Connection
'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & uid & ";password=" & pwd & ";Initial Catalog=jzd;Data Source=" & addr
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
iReturn = "数据库备份成功!"
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 = True _
) As String
Dim iDb As ADODB.Connection, iRe As ADODB.Recordset
Dim iConcStr$, iSql$, iReturn$, iI&
Dim uid, pwd, addr As String
On Error GoTo lbErr
'读取数据库相关信息
uid = ReadIniFile(getiniPath("jzd.ini"), "Admin", "uid")
pwd = ReadIniFile(getiniPath("jzd.ini"), "Admin", "pwd")
addr = ReadIniFile(getiniPath("jzd.ini"), "Admin", "address")
'创建对象
Set iDb = New ADODB.Connection
Set iRe = New ADODB.Recordset
'连接数据库服务器,根据你的情况修改连接字符串
iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=" & addr & ";User ID=" & uid & ";password=" & pwd 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(CommonDialog1.FileName, "jzd")函数没有啊。这是备份的关键部分吧。
那两段代码我都有,在备份和恢复小的数据库时可以操作,可是当数据量大了以后,比如几个G时就会超时了。而且恢复如果失败的话数据库都不能用了,必须要到企业管理器去处理一下。
'引用ADO
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=登录名;Password=密码;Initial Catalog=数据库;Data Source=Sql服务器别名"
cn.CursorLocation=adUseClient
cn.Open
cn.Execute ("backup database 数据库 to disk='c:\test'")
End Sub
-------------------------------
恢复:
Private Sub Command2_Click()
Dim cn As ADODB.Connection,rs As New ADODB.RecordSet
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=登录名;Password=密码;Initial Catalog=master;Data Source=Sql服务器别名"'借助master数据库来恢复
cn.CursorLocation=adUseClient
cn.Open
rs.Open "select spid from sysprocesses where dbid=db_id('你的数据库名)", cn
Do While Not rs.EOF
cn.Execute "kill " & rs("spid") '杀掉sql服务器的所有连接,否则会出现:数据库正在使用,无法完成排它操作等等,很重要
rs.MoveNext
Loop
cn.Execute ("restore database 数据库名 from disk='c:\test' with replace")
End Sub