在vb.net中使用程序备份sql server数据库

在界面上使用button按钮触发

备份过程如下：

 Private Sub Button_backup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button_backup.Click

        '执行备份过程
        Try

‘备份数据库

            Me.backupDb()
            MsgBox("数据成功备出！", MsgBoxStyle.OKOnly)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OKOnly)
           End Try

    End Sub

’备份方法其中backupDir为应用程序中制定备份路径的全局变量，在应用程序启动的时候从配置文件里面读出来的值

 Private Sub backupDb()
        Dim mysqlcomm As SqlClient.SqlCommand
        Dim strsql As String
        Dim strbak As String
        Try
            '判断备份路径参数里面是否制定路径
            If backupDir <> Nothing Then
                '判断路径是否存在
                If Not System.IO.Directory.Exists(backupDir) Then
                    '创建路径
                    System.IO.Directory.CreateDirectory(backupDir)
                End If
            Else
                If Not System.IO.Directory.Exists(Application.StartupPath + "/backup") Then
                    '创建路径
                    System.IO.Directory.CreateDirectory(Application.StartupPath + "/backup")
                End If
                backupDir = Application.StartupPath + "/backup"
            End If

            strbak = backupDir
            strsql = "backup database wjb to disk='" + strbak + "/wjb.bak' with name='wjb backup all',description='Full Backup Of wjb'"    ‘备份用的sql语句
            mysqlconn.Open()     ‘mysqlconn为创建的连接
            mysqlcomm = New SqlClient.SqlCommand(strsql, mysqlconn)   ’执行备份
            mysqlcomm.ExecuteNonQuery()
            mysqlconn.Close()
        Catch ex As Exception
            If Not mysqlconn.State.Closed = ConnectionState.Closed Then
                mysqlconn.Close()
            End If
            Throw ex
        End Try    End Sub

在上边的备份过程中，用到的mysqlconn连接通过一下方法配置

‘这里指出，连接的是master数据库

 Sub open_Masterconn()
        Dim sqlconnStr As String

        Try
            sqlconnStr = "data source = " + DbHost + " ; initial catalog = master; user id = " + connUser + "; password = " + connpwd
            mysqlconn = New SqlClient.SqlConnection(sqlconnStr)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        ' MsgBox("数据库连接准备完成")

    End Sub

备份步骤：

1 如果备份应用程序当前使用的数据库，配置连接当前数据库的连接或者连接master数据库的连接；如果备份其他数据库只有配置连接master的连接了

2 打开连接

3备份数据库

注明：在备份完数据库后，只要执行打开当前应用程序使用数据库的连接，不影响其他模块功能。

如果是恢复数据库，则必须退出系统，重新登陆