How to Backup and Restore of SQL Server database through VB.NET

This article describes how to back up and restore a SQL Server database using vb.net. The following code example requires a windows form with some combobox and textbox controls (see below picture.)

Back Up and restore SQL database
The code example shows how to back up an existing SQL database to a file and then how to restore it.

First you need to add reference of these libraries
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.Management.Sdk.Sfc

In the code, before any declarations, type the following Imports statements
Imports Microsoft.SqlServer.Management.Smo

See the code :
We declare two enum types members

 Enum Action
        BackUp
        Restore
    End Enum
    Private Sub btnBackUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackUp.Click
        Execute(Action.BackUp)
    End Sub
    Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click
        Execute(Action.Restore)
    End Sub

We create a database connection with the help of input information from the user interface, and then perform backup and restore database action with the help of these queries:

For Backup:
BACKUP DATABASE databaseName TO disk= ‘Filename

For Restore:
RESTORE DATABASE databaseName FROM disk=’Filename

 Private Sub Execute(ByVal strAction As Action)
        Dim Filename As String
        ServerName1 = cmbServerName1.Text.Trim()
        UserID1 = txtUserName1.Text.Trim()
        Password1 = txtPassword1.Text.Trim()
        database = cmbDataBase1.Text
 
        If cmbAuthe1.SelectedIndex = 0 Then
            strConn = "Data Source=" & ServerName1 &";Initial Catalog="' & cmbDataBase1.Text &'";Integrated Security=True"
        Else
            strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" &cmbDataBase1.Text & ";user id="' & UserID1 & '";password="' & Password1 & '";Integrated Security=false"
        End If
        con = New SqlConnection(strConn)
        con.Open()
        Dim strQuery As String
        If strAction = Action.BackUp Then
            Dim objdlg As New SaveFileDialog
            objdlg.FileName = database
            objdlg.ShowDialog()
            Filename = objdlg.FileName
            strQuery = "backup database " & database & " to disk='" & Filename &"'"
        Else
            Dim objdlg As New OpenFileDialog
            objdlg.FileName = database
            objdlg.ShowDialog()
            Filename = objdlg.FileName
            strQuery = "RESTORE DATABASE "' & database & '" FROM disk='" & Filename & "'"
        End If
 
        Dim cmd As SqlCommand
        cmd = New SqlCommand(strQuery, con)
        cmd.ExecuteNonQuery()
    End Sub

7 thoughts on “How to Backup and Restore of SQL Server database through VB.NET”

  1. Please can anyone help me.

    Cannot open backup device Operating system error 5(failed to retrieve text for this error. Reason: 15105). BACKUP DATABASE is terminating abnormally.

Comments are closed.