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.)
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
OK BA?
its not complete >.< can u share the complete code of this?
Tanga kayo
tanong niyo si jerome
jerome lorenzo pangalan taga solano
please share the full code plz
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.