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
1496 Downloads
Details

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

  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
  7.  
  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
  29.  
  30.         Dim cmd As SqlCommand
  31.         cmd = New SqlCommand(strQuery, con)
  32.         cmd.ExecuteNonQuery()
  33.     End Sub

Published by

Hirendra Sisodiya

Programmer at PSPL. Can be found on Google Plus.

  • ASA

    OK BA?

  • bibang

    its not complete >.< can u share the complete code of this?

  • JIan

    jerome lorenzo pangalan taga solano

  • JIan

    Tanga kayo

  • JIan

    tanong niyo si jerome