关于数据备份和还原的问题，请教了100分！！不够再加！

[moonyli]

用CommonDialog控件进行数据备份，需要定义什么啊？

还有需要调用动态库吗？

请大侠赐教！最好能详细点！

如果有其他方式也可以！

谢谢！

[zlt982001]

CommonDialog 只是对话框控件,不能进行备份
数据备份要看是什么数据库 ,如果是 Access 直接复制就可以
如果要压缩，就用 xceed zip 这个控件

如果是 SQLserver 可以使用 SQL 语句备份或 SQL_DMO 对象备份
详细请参考 SQL帮助

[TechnoFantasy]

CommonDialog只是一个打开、保存文件的对话框，本身没有文件操作能力。这个控件包含在
Microsoft Common Dialog Control里面

[wzzwwz]

只是用来获取文件名和路径的，其他诸如保存，读取的功能必须自己写

[bihai123]

是整个数据库备份还是只备份各别表呢?

[faysky2]

备份:
'引用ADO
Private Sub Command1_Click()
Dim backUpFileName As String
With CommonDialog1
.DialogTitle = "保存的文件名"
.Filter = "*.SQL|*.*"
.InitDir = App.Path
.ShowSave
backUpFileName = CommonDialog1.FileName
End With

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='" & backupdilename & "'")
End Sub
'-------------------------------
恢复:
Private Sub Command2_Click()
Dim backUpFileName As String
With CommonDialog1
.DialogTitle = "保存的文件名"
.Filter = "*.SQL|*.*"
.InitDir = App.Path
.ShowSave
If Dir(CommonDialog1.FileName) <> "" Then
backUpFileName = CommonDialog1.FileName
Else
MsgBox "文件不存在！"
Exit Sub
End If
End With

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='" & backUpFileName & "' with replace")
End Sub

[上官云峰]

sqlserver备份
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 Sub
还原
Private 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 = sa
pwd = ""
addr = 13.1.1.50

'创建对象
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
'**创 建 人：邹建
'**日 期：2003年12月09日
'*************************************************************************
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 = sa
pwd = ""
addr = 13.1.1.50


'创建对象
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

[bbhere]

CommonDialog只能选择备份的路径和文件名，备份功能需要另外实现

[bbhere]

如果是备份sqlserver，可参考以下

备份有两种形式完全备份和差异备份

　　完全备份：是对数据库进行完整备份，周期比较长，如1周等

　　差异备份：是对更新备份的数据进行备份，周期比较短，如1小时

　　命令介绍： Backup Database [数据库名] To Disk=[保存路径和名称] With [其他属性]

　　代码注释：ServerIP :SQLServer所在机器的 IP
　　　　　　　jtsg:SQLServer中数据的名称

Public Cn As ADODB.Connection
Set Cn = New ADODB.Connection
strcon = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=jtsg;Data Source=" + ServerIp + ";" + _
"use procedure for prepare=1;auto translate=true;" + _
"use encryption for data=false;tag with column collation when possible=false"
Cn.ConnectionString = strcon
On Error Resume Next
Cn.Open
If Cn.Errors.Count > 0 Then
For i = 0 To Cn.Errors.Count - 1
If Cn.Errors(i).Number = -2147467259 Then
MsgBox "网络不通或服务器未开，请确认后再试", vbOKOnly + vbInformation, "提醒"
Unload Me
Exit Sub
Exit For
End If
Next i
End If


二、完全备份
Private Sub BackupAll()
Cn.Execute "backup database jtsg to disk='" + App.Path + "\backup\myjtsgall.bak' with name='jtsg backup all',description='Full Backup Of jtsg'"
MsgBox "数据完全备份已经完成", vbOKOnly + vbInformation, "提醒"
End Sub


三、差异备份
Private Sub BackupDif()
Cn.Execute "backup database jtsg to disk='" + App.Path + "\backup\myjtsgdif.bak' with differential ,noinit,name='jtsg backup dif',description='Differential Backup Of jtsg'"
MsgBox "数据差异备份已经完成", vbOKOnly + vbInformation, "提醒"
End Sub


四、数据恢复

　　数据恢复，问题很多，要区分用户的备份形式。一般备份的默认形式是 Append to media，即添加到备份设备已有内容后面。前面介绍的备份方式就这种。还有一种备份形式是 OverWrite，即覆盖设备原有内容。这两中备份形式决定恢复的方式也不一样。

　　命令介绍： Restore Database [数据库名] From [保存备份的路径和名称] With[其他属性]

　　在其他属性中重点介绍：File 、 NORECOVERY和 RECOVERY

　　1.FILE
　　这是不同备份形式的重要参数，用来指定从设备上的第几个备份中恢复，比如采用APPEND形式备份，作了两次备份，恢复第一个备份时应该在恢复命令中使用“FILE=1”；恢复第二个备份时则使用“FILE=2”的选项。
　　当然如果备份形式是OVERWRITE，备份中就只有一次备份，所以使用 FILE=1的选项；

　　2.RECOVERY
　　如果使用RECOVERY选项，那么恢复完成后，SQLSERVER回滚被恢复数据库中所有未完成的事务，以保证数据库的一致性。在恢复后用户就可以访问数据库。所以RECOVERY用来恢复最后一个备份。NORECOVERY则相反。默认为RECOVERY。

　　代码解释：还原备份的文件可以选择，完全备份和差异备份
Private Sub RestoreData()
Cn.Execute "restore database jtsg from disk='" + App.Path + "\backup\myjtsgall.bak' with FILE=1"
MsgBox "数据恢复已经完成", vbOKOnly + vbInformation, "提醒"
End Sub

[zhzhq800204]

’通过CommonDialog获得数据库文件
Private Sub CmdSel_Click()
Cdlg.Filter = "(*.mdb)|*.mdb|"
Cdlg.DialogTitle = "选择数据库"
Cdlg.Action = 1
Cdlg.FilterIndex = 2
Cdlg.InitDir = App.Path

TxtPath.Text = Cdlg.FileName
End Sub


'取得文件值
On Error Resume Next
If TxtPath.Text = "" Then
MsgBox "请输入备份文件的路径及文件名！", vbYes + vbExclamation, "提示"
TxtPath.SetFocus
Exit Sub
Else
If InStr(TxtPath.Text, ".mdb") <> 0 Then s = "" Else s = ".mdb"
Backmdb = TxtPath.Text & s
If InStr(Backmdb, "/") <> 0 Then
MsgBox "数据库路径符号输入错误，请重新输入！", vbOKOnly + vbExclamation, "提示"
TxtPath.Text = ""
TxtPath.SetFocus
Exit Sub
End If
k = GetAttr(Backmdb)
If k = "" Then
'实现数据库备份
FileCopy Dbase_Name, Backmdb
MsgBox "数据库备份完成！", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
ddd = MsgBox("您选择的数据库已经存在，覆盖吗?", vbYesNo + vbCritical, "警告")
If ddd = vbYes Then
FileCopy Dir1.Path & "\" & File1.FileName, BackPath
MsgBox "数据库备份完成！", vbOKOnly + vbExclamation, "提示"
Exit Sub
Else
MsgBox "数据库操作终止！", vbOKOnly + vbExclamation, "提示"
Exit Sub
End If
End If
End If
’数据库还原
If TxtPath.Text = "" Then
MsgBox "请输入源数据库的路径及文件名！", vbYes + vbExclamation, "提示"
TxtPath.SetFocus
Exit Sub
Else
If InStr(TxtPath.Text, ".mdb") <> 0 Then s = "" Else s = ".mdb"
Backmdb = TxtPath.Text & s
If InStr(Backmdb, "/") <> 0 Then
MsgBox "数据库路径符号输入错误，请重新输入！", vbOKOnly + vbExclamation, "提示"
TxtPath.Text = ""
TxtPath.SetFocus
Exit Sub
End If
k = GetAttr(Backmdb)
If k = "" Then
MsgBox "源数据库路径错误，此文件不存在！", vbOKOnly + vbExclamation, "提示"
TxtPath.Text = ""
TxtPath.SetFocus
Exit Sub
Else
ddd = MsgBox("您确定要还原数据库吗?", vbYesNo + vbCritical, "警告")
If ddd = vbYes Then
On Error GoTo NN
FileCopy Backmdb, Dbase_Name
MsgBox "数据库还原完成！", vbOKOnly + vbInformation, "提示"
Exit Sub
Else
MsgBox "数据库操作终止！", vbOKOnly + vbInformation, "提示"
Exit Sub
End If
End If
End If
End If
NN: MsgBox "数据库正在使用，请关闭数据库！", vbOKOnly + vbExclamation, "提示"
End Sub