Backup and Restore Sql Server database in VB.NET

This tutorial explains how to create a Sql Server Backup file in VB.NET and also how to restore the

database. We use a Backup file and restore database when our database becomes

corrupted or crashes. So let's have a pratical example of how to create it in VB.NET, this example is

developed in Visual Studio 2013.

First, we create this Windows Form and we declare the connectionString of the database.

    Dim con As New SqlConnection("server = .\sqlexpress; database = database1; integrated security = true")

We start by the Backup, in BrowseButton click event we add this code:

    Private Sub browseButton_Click(sender As Object, e As EventArgs) Handles browseButton.Click
        Dim dlg As FolderBrowserDialog = New FolderBrowserDialog()
        If dlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
            TextBox1.Text = dlg.SelectedPath
            BackupButton.Enabled = True

        End If
    End Sub

Backup button

Private Sub BackupButton_Click(sender As Object, e As EventArgs) Handles BackupButton.Click
        Dim database As String = con.Database.ToString
        Try
            If TextBox1.Text Is String.Empty Then
                MessageBox.Show("Please enter backup file location")
            Else
                Dim cmd As String = "BACKUP DATABASE [" + database + "] TO DISK='" + TextBox1.Text + "\" + "database" + "-" + Date.Now.ToString("yyyy-MM-dd--HH-mm-ss") + ".bak'"
                Using Command As SqlCommand = New SqlCommand(cmd, con)
                    If con.State <> ConnectionState.Open Then
                        con.Open()

                    End If
                    Command.ExecuteNonQuery()
                    con.Close()
                    MessageBox.Show("database backup created sucessefully")
                    BackupButton.Enabled = False

                End Using
            End If
        Catch ex As Exception

        End Try
    End Sub

Now the restore operation: Browse2 Button:

Private Sub browseButton2_Click(sender As Object, e As EventArgs) Handles browseButton2.Click
        Dim dlg As OpenFileDialog = New OpenFileDialog()
        dlg.Filter = "SQL SERVER database backup files|*.bak"
        dlg.Title = "database restore"

        If dlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
            TextBox2.Text = dlg.FileName
            RestoreButton.Enabled = True

        End If
    End Sub

Restore Button

Private Sub RestoreButton_Click(sender As Object, e As EventArgs) Handles RestoreButton.Click
        Dim database As String = con.Database.ToString()
        If con.State <> ConnectionState.Open Then
            con.Open()

        End If

        Try
            Dim sqlStmt2 As String = String.Format("ALTER DATABASE [" + database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE")
            Dim bu2 As SqlCommand = New SqlCommand(sqlStmt2, con)
            bu2.ExecuteNonQuery()
            Dim sqlStmt3 As String = "USE MASTER RESTORE DATABASE [" + database + "] FROM DISK='" + TextBox2.Text + "'WITH REPLACE"
            Dim bu3 As SqlCommand = New SqlCommand(sqlStmt3, con)
            bu3.ExecuteNonQuery()
            Dim sqlStmt4 As String = String.Format("ALTER DATABASE [" + database + "] SET MULTI_USER")
            Dim bu4 As New SqlCommand(sqlStmt4, con)
            bu4.ExecuteNonQuery()
            MessageBox.Show("database restore done successefuly")
            con.Close()

        Catch ex As Exception

        End Try
    End Sub