用VB代码备份SQL-SERVER

'*********************************************************
'* 名称：BackupDatabase
'* 功能：备份数据库
'* 控件：一个文本框和两个按钮（备份到和确定）
'*********************************************************
Public Sub BackupDatabase()
Dim cn As New ADODB.Connection
Dim s_path, s_dataexport As String
s_path = App.Path
Me.MousePointer = 11   '设置鼠标指针形状
'student1是需要备份的数据库名称
s_dataexport = "backup database student1 to disk='" + CommonDialog1.FileName + "'"
cn.Open "driver={sql server};server=" & d1 & ";database=student1;persist security info=false; userid=sa"  '数据库连接字符串
'这里不需要连接master数据库，即可完成备份
cn.BeginTrans
cn.Execute s_dataexport
Err.Number = 0
If Err.Number = 0 Then
    cn.CommitTrans
    MsgBox "数据备份成功！", vbInformation, "提示"
    MsgBox "数据备份文件存放路径:" & CommonDialog1.FileName, vbOKOnly, "提示"
    Unload Me
Else
    cn.RollbackTrans
    MsgBox "数据备份失败！请检查数据库是否正在打开！", vbCritical, "提示"
End If
cn.Close
Set cn = Nothing
Me.MousePointer = 1
End Sub'*********************************************************
'* 名称：RestoreDataBase
'* 功能：还原数据库
'* 控件：一个文本框和两个按钮（打开和确定）
'*********************************************************
Public Sub RestoreDataBase()
If Text1.Text = "" Then
    MsgBox "请选择要恢复的数据文件！", vbInformation, "提示"
    Exit Sub
Else
    ret = MsgBox("数据恢复操作将会覆盖以前的所有数据并且覆盖后无法恢复，您确定要进行恢复操作吗？", vbQuestion + vbOKCancel, "提示")
    If ret = vbOK Then
       Dim cn As New ADODB.Connection
       Dim sn As New ADODB.Recordset
       Dim s_restore As String
       Me.MousePointer = 11
       cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;server=" & d1 & ";Initial Catalog=master;Data Source=127.0.0.1;user id=sa;password=" & d3 & ""
       sn.Open "select  spid  from  sysprocesses  where  dbid=db_id('student1')", cn
        Do While Not sn.EOF
          cn.Execute "kill " & sn("spid")
          sn.MoveNext
        Loop
        sn.Close
        s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "'  with REPLACE"
        cn.Execute s_restore
         'Debug.Print gs_conn_string
         '此时需要连接master数据库才能完成数据恢复操作
         '同上student1为需要恢复的数据库
        s_restore = "restore database student1 from disk='" + Trim(Text1.Text) + "'"
         'text1一个用于记录需要恢复文件的地址的textbox
        cn.Execute s_restore
        cn.BeginTrans
        If Err.Number = 0 Then
            cn.CommitTrans
            MsgBox "数据恢复成功！", vbInformation, "提示"
            Command1.Enabled = True
            Label1.Visible = False
        Else
            cn.RollbackTrans
            MsgBox "数据恢复失败！", vbCritical, "提示"
            Command1.Enabled = True
        End If
        cn.Close
        Set cn = Nothing
        Me.MousePointer = 1
    Else
        Exit Sub
    End If                      '''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    Dim DBC As New DataBaseConnection
    If db.State = 1 Then
       db.Close
    End If
    db.ConnectionString = DBC.SqlConnectString(d1, d2, d3)
    rs.CursorType = adOpenDynamic
    rs.CursorLocation = adUseClient
    rs.LockType = adLockOptimistic
    db.CursorLocation = adUseClient
    db.Open
    Set cmd.ActiveConnection = db
    If Err.Number Then
       MsgBox Err.Description, 16 + vbOKOnly, Err.Number
       Exit Sub
    End If
    db.DefaultDatabase = "student1"
    If Err.Number Then
       MsgBox Err.Description, 16 + vbOKOnly, Err.Number
       Exit Sub
    End If
End If
End Sub                      '''''''''''''''''''''''''''''''''''''''''''''如果当前没有与要恢复的数据库立连接，则不需要加单引号中的内容。如果希望恢复数据库之后继续建立连接，则需要写这部分代码。我要恢复数据库名称为student1，备份数据库的时候是在连接状态下进行的，但是恢复数据库不可以在数据库存在连接的状态下进行操作！代码的解决方法是：先连接SQL Server中主库master 库，在该库中的sysprocesses表中存放着所有连接到此数据库的连接信息，将这些连接信息用Kill语句删除。然后再恢复数据库student1，由于用Kill语句后，数据库已经被断开，所以在恢复完成后，再用系统最初的连接数据库代码连接上数据库student1。

  'sql server 的备份方法
If CN.State = adStateOpen Then CN.Close
    CN.ConnectionTimeout = 10
    connstr = "Driver={SQL Server};Server=" & LocalServer & ";UID=" & LocalUser & ";PWD=" & LocalPw & ";DATABASE=" & LocalDb
    CN.Open connstr
Nowtime = Format(Now, "hh-mm-ss")
dd = str(Date)
riqi = dd + "-" + NowtimeSql = "BACKUP DATABASE tablename TO disk='D:\mssql7\BACKUP\tablename" & riqi & "' with noinit"
CN.Execute (Sql)

添加进度条ProgressBar2控件
引用Microsoft SQLDMO Object Library'声明
Public WithEvents bkps As SQLDMO.Backup
'数据库备份操作
Private Sub Command6_Click()
    Dim oSvr As SQLDMO.SQLServer
    Set oSQLServer = CreateObject("SQLDMO.SQLServer")
    oSQLServer.LoginSecure = False
    oSQLServer.Connect (fwqm), (yhm), (kl)
    Set stos = CreateObject("SQLDMO.Restore")
    stos.Action = SQLDMORestore_Database
    stos.Database = "tb" '欲恢复的数据库
    stos.ReplaceDatabase = True
    stos.Files = "c:\tb.bak" '备份文件
    ProgressBar2.Value = 0: ProgressBar2.max = 100
    stos.SQLRestore oSQLServer '执行恢复操作
    Set stos = Nothing
    MsgBox "数据库恢复完成"
End SubPrivate Sub stos_PercentComplete(ByVal Message As String, ByVal Percent As Long)
    ProgressBar2.Value = ProgressBar2.max * (Percent / 100)
End Sub
*******************************************************************
backup code:
    Dim oBackup As sqldmo.Backup

    gDatabaseName = DatabaseName '数据库名称
    Set oBackup = New sqldmo.Backup
    Set oBackupEvent = oBackup ' enable events

    oBackup.Database = gDatabaseName
    gBkupRstrFileName =backname ’ 备份文件名称
    oBackup.Files = gBkupRstrFileName

    ' Delete the datafile to allow the application to create a brand new file.
    ' This will prevent attaching the new backup data to the old data if there
    ' is any.
    If Len(Dir(gBkupRstrFileName)) > 0 Then
        Kill (gBkupRstrFileName)　‘删除已经存在的文件
    End If

    ' Change mousepointer while trying to connect.
    Screen.MousePointer = vbHourglass

    ' Backup the database.
    oBackup.SQLBackup gSQLServer　’备份数据库到指定文件

    ' Change mousepointer back to the default after connect.
    Screen.MousePointer = vbDefault

    Set oBackupEvent = Nothing ' disable events
    Set oBackup = Nothing
  restore code:    gDatabaseName = DatabaseName
    Set oRestore = New sqldmo.Restore
    Set oRestoreEvent = oRestore        ' enable events

    oRestore.Database = gDatabaseName
    gBkupRstrFileName = DataFileName
    oRestore.Files = gBkupRstrFileName

    ' Change mousepointer while trying to connect.
    Screen.MousePointer = vbHourglass

    ' Restore the database.
    oRestore.SQLRestore gSQLServer

    ' Change mousepointer back to the default after connect.
    Screen.MousePointer = vbDefault

    Set oRestoreEvent = Nothing         ' disable events
    Set oRestore = Nothing

'备份数据库
Private Sub MNUbackup_Click()
On Error GoTo errnum
    dlg.CancelError = True
    cn1.Execute "use master"
    dlg.Filter = "(*.bak)|*.bak"
    dlg.ShowSave
    MousePointer = 11
    cn1.Execute "BACKUP DATABASE hetong TO DISK = '" & dlg.FileName & "'"  '备份数据库
    MousePointer = 0
    Set cn1 = Nothing
    '重新建立连接
errnum:
    Call dbint
    Set cn1 = New ADODB.Connection
    cn1.Open "Driver={SQL Server};Server=" & SQL_SRV & ";Uid=" & SA & ";Pwd=" & PA & ";Database=" & SJK & ""
End Sub

'备份数据库
Private Sub MNUbackup_Click()
On Error GoTo errnum
    dlg.CancelError = True
    cn1.Execute "use master"
    dlg.Filter = "(*.bak)|*.bak"
    dlg.ShowSave
    MousePointer = 11
    cn1.Execute "BACKUP DATABASE hetong TO DISK = '" & dlg.FileName & "'"  '备份数据库
    MousePointer = 0
    Set cn1 = Nothing
errnum:

    Set cn1 = New ADODB.Connection
    cn1.Open "Driver={SQL Server};Server=" & SQL_SRV & ";Uid=" & SA & ";Pwd=" & PA & ";Database=" & SJK & ""
End Sub

'还原数据库
Private Sub MNURestore_Click()
    On Error GoTo errnum
    dlg.CancelError = True
    cn1.Execute "use master"
    dlg.Filter = "(*.bak)|*.bak"
    dlg.ShowOpen
    MousePointer = 11
    cn1.Execute "restore database hetong from disk ='" & dlg.FileName & "' with replace"            '还原数据库

errnum:

    Call dbint
    Set cn1 = New ADODB.Connection
    cn1.Open "Driver={SQL Server};Server=" & SQL_SRV & ";Uid=" & SA & ";Pwd=" & PA & ";Database=" & SJK & ""    MousePointer = 0
End Sub

用SQL语句:backup database 就可以的了.

自找麻烦, 用 backup database 多好.

兄弟们，但是这种情况怎么办？
环境1的数据库NetDB文件在：
C:\Program Files\Microsoft SQL Server\MSSQL\Data\NetDB.mdf
C:\Program Files\Microsoft SQL Server\MSSQL\Data\NetDB_Log.ldf
在环境1中执行：backup database netdb to disk="e:\dbBAK\netdb.bak"环境2的数据库NetDB文件在：
D:\Program Files\Microsoft SQL Server\MSSQL\Data\NetDB.mdf
D:\Program Files\Microsoft SQL Server\MSSQL\Data\NetDB_Log.ldf现在把环境1的：e:\dbBAK\netdb.bak
复制到环境2的：c:\dbBAK\netdb.bak
在环境2中执行：restore database netdb from diks="c:\dbBAK\netdb.bak"
这时就恢复不了！如果环境2的数据库文件路径与环境1的一致是没有问题，问题是不一致时如何恢复？

用强制还原restore database netdb from diks="c:\dbBAK\netdb.bak" with replace试试
如果还不行的话只能用with move选项自己指定逻辑文件的位置了
因为，默认情况是还原到备份时的原始位置的先使用 RESTORE FILELISTONLY 从备份集中获取逻辑文件列表
然后执行
restore database netdb from diks="c:\dbBAK\netdb.bak" with MOVE 'logical_file_name' TO 'operating_system_file_name'

例如：
BACKUP DATABASE Northwind
   TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
   FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
   FROM DISK = 'c:\Northwind.bak'
   WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
   MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

对于你的刻已这样：
RESTORE FILELISTONLY FROM DISK ='c:\dbBAK\netdb.bak'RESTORE DATABASE netdb FROM DISK = 'c:\dbBAK\netdb.bak' WITH MOVE 'Northwind' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\NetDB.mdf',
MOVE 'Northwind_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\NetDB_Log.ldf'
Go

调试易

用VB代码备份SQL-SERVER

解决方案 »