Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / Visual Basic
Tip/Trick

Backup & Restore of SQL Server database using VB.NET

Rate me:
Please Sign up or sign in to vote.
5.00/5 (22 votes)
13 Nov 2011CPOL 189.9K   36   18
Backup & Restore of SQL Server database using VB.NET


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:
VB
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.
VB
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)


Written By
Software Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionI don't see the backup file anywhwere? Pin
Roger Breton3-Jun-20 14:50
Roger Breton3-Jun-20 14:50 
AnswerRe: I don't see the backup file anywhwere? Pin
Member 103503544-Jul-20 12:22
Member 103503544-Jul-20 12:22 
Questionmfgurbuz@gmail.com Pin
Member 1466121219-Nov-19 7:47
Member 1466121219-Nov-19 7:47 
QuestionBackup and Restore Pin
ClintSoft24-Feb-19 17:01
ClintSoft24-Feb-19 17:01 
GeneralMy vote of 5 Pin
Edward Lubaini24-Apr-16 6:03
Edward Lubaini24-Apr-16 6:03 
Questionwishnu_programmer Pin
Member 1126430810-Jan-15 8:50
Member 1126430810-Jan-15 8:50 
GeneralMy vote of 5 Pin
Member 107580613-Jun-14 3:09
Member 107580613-Jun-14 3:09 
GeneralMy vote of 5 Pin
Adarsh chauhan14-Aug-13 2:28
professionalAdarsh chauhan14-Aug-13 2:28 
GeneralError After BackUp Pin
Member 968818412-Jun-13 0:48
Member 968818412-Jun-13 0:48 
GeneralRe: Error After BackUp Pin
Patil Kishor14-Aug-13 4:16
Patil Kishor14-Aug-13 4:16 
QuestionBackup Not working Pin
Code-Hunt4-Feb-13 22:08
Code-Hunt4-Feb-13 22:08 
AnswerRe: Backup Not working Pin
crouchie0425-Apr-13 12:13
crouchie0425-Apr-13 12:13 
AnswerRe: Backup Not working Pin
Jiaging Yu22-Oct-13 23:50
Jiaging Yu22-Oct-13 23:50 
QuestionHOW ABOUT RESTORE PART Pin
JamesB2816-Jan-13 14:42
JamesB2816-Jan-13 14:42 
Questionhelp Pin
bibang13-Jan-13 23:02
bibang13-Jan-13 23:02 
Questiongud but its not wrking.. Pin
Guruprasad Bhavsar15-Nov-12 6:28
Guruprasad Bhavsar15-Nov-12 6:28 
GeneralMy vote of 5 Pin
D-Kishore28-Aug-12 19:19
D-Kishore28-Aug-12 19:19 
GeneralVery Good Example. Thank you. :D Pin
Sjnilan15-Jan-12 21:15
Sjnilan15-Jan-12 21:15 

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

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