Backup & Restore of SQL Server database using VB.NET

• Download source
• Download demo

Introduction

When we want to take complete backup or restore the SQL Server database, most of us prefer to use a regular backup
utility which is available through Enterprise Manager in SQL Server 2000 or Management Studio in SQL Server 2005.
so backup & restore can be performed by firing a simple SQL query to server it is a easiest method to use. But it works only on SQL Server 2000 or SQL Server 2005. It doesn't work on SQL Express edition, it gives an error.

Using the Code

Create a Windows application in VB.NET 2005 and design as shown in the above image. Then import namespace Imports System.Data.SqlClient create a variable for connection as follows:

Dim con As SqlConnection
Dim cmd As SqlCommand
Dim dread As SqlDataReader

write a sub functions, in form load there is server function call done with parameter
"." or ".\Sqlexpress" "." dot is used for global server. Server function adds the server list to first combo box. connection function is used to set connection with selected database for backup & restore query function is used to fired a query on connected database.

Imports System.Data.SqlClient

Public Class Form1
    Dim con As SqlConnection
    Dim cmd As SqlCommand
    Dim dread As SqlDataReader
    
    Private Sub Form1_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 cmbbackup.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