Click here to Skip to main content
Click here to Skip to main content

Backup & Restore of SQL Server database using VB.NET

By , 13 Nov 2011
Rate this:
Please Sign up or sign in to vote.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Patil Kishor
Software Developer
India India
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 PinmemberAdarsh chauhan14-Aug-13 2:28 
GeneralError After BackUp PinmemberMember 968818412-Jun-13 0:48 
GeneralRe: Error After BackUp PinmemberPatil Kishor14-Aug-13 4:16 
QuestionBackup Not working PinmemberSnehasish Nandy4-Feb-13 22:08 
AnswerRe: Backup Not working Pinmembercrouchie199825-Apr-13 12:13 
AnswerRe: Backup Not working PinmemberJiaging Yu22-Oct-13 23:50 
QuestionHOW ABOUT RESTORE PART PinmemberJamesB2816-Jan-13 14:42 
Questionhelp Pinmemberbibang13-Jan-13 23:02 
Questiongud but its not wrking.. PinmemberGuruprasad Bhavsar15-Nov-12 6:28 
GeneralMy vote of 5 Pinmemberkishore doni28-Aug-12 19:19 
GeneralVery Good Example. Thank you. :D PinmemberSjnilan15-Jan-12 21:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 13 Nov 2011
Article Copyright 2011 by Patil Kishor
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid