How to backup and restore MS SQL database files in vb.net

If you are developing a MS SQL server database driven application then you should add database backup and restore facility into your application.

Here I am showing you how to backup and restore MS SQL database files in vb.net. First of all we need to add following namespace into our application.

Imports System.IO
Imports System.Data
Imports System.IO.File
Imports System.Data.SqlClient

Now following code block will allow us to load all existing database into a combobox.

   Sub load_db_method()
        con.Open()
        Using cmd As New SqlCommand("SELECT name from sys.databases", con)
            Using dr As IDataReader = cmd.ExecuteReader()
                While dr.Read()
                    ComboBox1.Items.Add(dr(0).ToString())
                    ComboBox3.Items.Add(dr(0).ToString())
                End While
            End Using
        End Using
        con.Close()
    End Sub

To load all backup files from the directory where they are stored, we will use the following code.

    Sub load_backup_files()
        For Each file As String In IO.Directory.GetFiles("D:\Backup\")
            ComboBox2.Items.Add(System.IO.Path.GetFileNameWithoutExtension(file))
        Next
    End Sub

In form load procedure we will create our connection string, check backup directory is exists or not and call the methods to load database files.

   Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        con = New SqlConnection("Data Source=[your server name];User ID=sa;Password=[your password];integrated security=true;Initial Catalog=master")
        load_db_method()
        If Directory.Exists("D:\Backup") Then
            load_backup_files()
        Else
            Directory.CreateDirectory("D:\Backup")
        End If
    End Sub

Now following two blocks of code will backup and restore our database respectively.

Backup Database Dialog Box

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            Dim t As String
            Dim d As String
            Dim m As String
            Dim y As String
            t = Date.Now.Second.ToString + Date.Now.Minute.ToString + Date.Now.Hour.ToString
            d = Date.Now.Day.ToString
            m = Date.Now.Month.ToString
            y = Date.Now.Year.ToString
            Dim uniqueid As String
            uniqueid = t & d & m & y

            cmd = New SqlCommand("backup database " & ComboBox1.Text.Trim & " to disk='D:\backup\" & ComboBox1.Text.Trim & "_" & uniqueid & ".bak'", con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            Label2.Text = "Database backup completed successfully" & vbCrLf & "You can find the backup file in D:\Backup\" & ComboBox1.Text.Trim & "_" & uniqueid & ".bak"

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

Restore Database Dialog Box

   Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Try
            Dim alterquery As String = "ALTER DATABASE " & ComboBox3.Text & " SET Single_User WITH Rollback Immediate"
            Dim altercommand As SqlCommand = New SqlCommand(alterquery, con)
            con.Open()
            altercommand.ExecuteNonQuery()
            Label4.Text = "Database mode set to single user"
            con.Close()
            Dim restorecommand = New SqlCommand("Restore database " & ComboBox3.Text.Trim & " from disk='D:\Backup\" & ComboBox2.Text & ".bak'", con)
            con.Open()
            restorecommand.ExecuteNonQuery()
            Label4.Text = Label4.Text & vbNewLine & "Database restoration completed successfully"
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Dim Alter2 As String = "ALTER DATABASE " & ComboBox3.Text.Trim & " SET Multi_User"
            Dim Alter2Cmd As SqlCommand = New SqlCommand(Alter2, con)
            con.Open()
            Alter2Cmd.ExecuteNonQuery()
            con.Close()
            Label4.Text = Label4.Text & vbNewLine & "Database mode set to multiuser"
        End Try
    End Sub

Finally, we must make sure that every time we backup a file it is added in backup file list immediately. Following code will take care of it whenever we click on TabPage2.

   Private Sub TabControl1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TabControl1.SelectedIndexChanged
        If TabControl1.SelectedTab Is TabPage2 Then
            load_db_method()
            ComboBox2.Items.Clear()
            For Each file As String In IO.Directory.GetFiles("D:\Backup\")
                ComboBox2.Items.Add(System.IO.Path.GetFileNameWithoutExtension(file))
            Next
        End If

    End Sub

Download the complete source code here.

If you liked this post, please like my blog.