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

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

This article describes how to back up and restore a SQL Server database using 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

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

  1.  Enum Action
  2.         BackUp
  3.         Restore
  4.     End Enum
  5.     Private Sub btnBackUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackUp.Click
  6.         Execute(Action.BackUp)
  7.     End Sub
  8.     Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click
  9.         Execute(Action.Restore)
  10.     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

  1.  Private Sub Execute(ByVal strAction As Action)
  2.         Dim Filename As String
  3.         ServerName1 = cmbServerName1.Text.Trim()
  4.         UserID1 = txtUserName1.Text.Trim()
  5.         Password1 = txtPassword1.Text.Trim()
  6.         database = cmbDataBase1.Text
  8.         If cmbAuthe1.SelectedIndex = 0 Then
  9.             strConn = "Data Source=" & ServerName1 &";Initial Catalog="' & cmbDataBase1.Text &'";Integrated Security=True"
  10.         Else
  11.             strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" &cmbDataBase1.Text & ";user id="' & UserID1 & '";password="' & Password1 & '";Integrated Security=false"
  12.         End If
  13.         con = New SqlConnection(strConn)
  14.         con.Open()
  15.         Dim strQuery As String
  16.         If strAction = Action.BackUp Then
  17.             Dim objdlg As New SaveFileDialog
  18.             objdlg.FileName = database
  19.             objdlg.ShowDialog()
  20.             Filename = objdlg.FileName
  21.             strQuery = "backup database " & database & " to disk='" & Filename &"'"
  22.         Else
  23.             Dim objdlg As New OpenFileDialog
  24.             objdlg.FileName = database
  25.             objdlg.ShowDialog()
  26.             Filename = objdlg.FileName
  27.             strQuery = "RESTORE DATABASE "' & database & '" FROM disk='" & Filename & "'"
  28.         End If
  30.         Dim cmd As SqlCommand
  31.         cmd = New SqlCommand(strQuery, con)
  32.         cmd.ExecuteNonQuery()
  33.     End Sub


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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>