membuat backup restore database sql server

Salah satu pertanyaan yang sering diajukan adalah bagaimana cara melakukan backup dan restore database sql server menggunakan coding (vb.net). hal ini dilakukan untuk memudahkan user dalam penggunaan aplikasi tanpa harus "menyentuh" server. Langkah awal adalah membuat form seperti gambar di bawah ini :

Langkah selanjutnya adalah membuat Coding sebagai berikut :

Imports System.Data.SqlClient

'imports Microsoft.SqlServer

Public Class backuprestore

    Dim con, con1 As SqlConnection

    Dim cmd As SqlCommand

    Dim dread As SqlDataReader

    Private Sub backuprestore_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        server(".")

        server(".\sqlexpress")

    End Sub

    Sub server(ByVal str As String)

        con = New SqlConnection("Data Source=" & str & ";Database=Master;integrated security=SSPI;")

        con.Open()

        cmd = New SqlCommand("select *  from sysservers  where srvproduct='SQL Server'", con)

        dread = cmd.ExecuteReader

        While dread.Read

            cmbserver.Items.Add(dread(2))

        End While

        dread.Close()

    End Sub

    Sub connection()

        con = New SqlConnection("Data Source=" & Trim(cmbserver.Text) & ";Database=Master;integrated security=SSPI;")

        con.Open()

        cmbdatabase.Items.Clear()

        cmd = New SqlCommand("select * from sysdatabases", con)

        dread = cmd.ExecuteReader

        While dread.Read

            cmbdatabase.Items.Add(dread(0))

        End While

        dread.Close()

    End Sub

    Private Sub cmbserver_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbserver.SelectedIndexChanged

        connection()

    End Sub

    Sub query(ByVal que As String)

        On Error Resume Next

        cmd = New SqlCommand(que, con)

        cmd.ExecuteNonQuery()

    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

        If ProgressBar1.Value = 100 Then

            Timer1.Enabled = False

            ProgressBar1.Visible = False

            MsgBox("Successfully Done")

        Else

            ProgressBar1.Value = ProgressBar1.Value + 5

        End If

    End Sub

    Sub blank(ByVal str As String)

        If cmbserver.Text = "" Or cmbdatabase.Text = "" Then

            MsgBox("Server Name & Database Blank Field")

            Exit Sub

        Else

           

            If str = "backup" Then

                SaveFileDialog1.FileName = CmbDatabase.Text

                SaveFileDialog1.ShowDialog()

                Timer1.Enabled = True

                ProgressBar1.Visible = True

                Dim s As String

                s = SaveFileDialog1.FileName

                query("backup database " & CmbDatabase.Text & " to disk='" & s & "'")

            ElseIf str = "restore" Then

                OpenFileDialog1.ShowDialog()

                Timer1.Enabled = True

                ProgressBar1.Visible = True

                query("RESTORE DATABASE " & CmbDatabase.Text & " FROM disk='" & OpenFileDialog1.FileName & "'")

            End If

        End If

    End Sub

    Private Sub cmbbackup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdBackup.Click

        blank("backup")

    End Sub

    Private Sub cmdrestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdrestore.Click

        blank("restore")

    End Sub

End Class