Backup and Restore Sql Server database in VB.NET
This tutorial explains how to create a Sql Server Backup file in VB.NET and also how to restore the
database. We use a Backup file and restore database when our database becomes
corrupted or crashes. So let's have a pratical example of how to create it in VB.NET, this example is
developed in Visual Studio 2013.
First, we create this Windows Form and we declare the connectionString of the database.
Dim con As New SqlConnection("server = .\sqlexpress; database = database1; integrated security = true")
We start by the Backup, in BrowseButton click event we add this code:
1 2 3 4 5 6 7 8 | Private Sub browseButton_Click(sender As Object, e As EventArgs) Handles browseButton.Click Dim dlg As FolderBrowserDialog = New FolderBrowserDialog() If dlg.ShowDialog() = Windows.Forms.DialogResult.OK Then TextBox1.Text = dlg.SelectedPath BackupButton.Enabled = True End If End Sub |
Backup button
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | Private Sub BackupButton_Click(sender As Object, e As EventArgs) Handles BackupButton.Click Dim database As String = con.Database.ToString Try If TextBox1.Text Is String.Empty Then MessageBox.Show("Please enter backup file location") Else Dim cmd As String = "BACKUP DATABASE [" + database + "] TO DISK='" + TextBox1.Text + "\" + "database" + "-" + Date.Now.ToString("yyyy-MM-dd--HH-mm-ss") + ".bak'" Using Command As SqlCommand = New SqlCommand(cmd, con) If con.State <> ConnectionState.Open Then con.Open() End If Command.ExecuteNonQuery() con.Close() MessageBox.Show("database backup created sucessefully") BackupButton.Enabled = False End Using End If Catch ex As Exception End Try End Sub |
Now the restore operation: Browse2 Button:
1 2 3 4 5 6 7 8 9 10 11 | Private Sub browseButton2_Click(sender As Object, e As EventArgs) Handles browseButton2.Click Dim dlg As OpenFileDialog = New OpenFileDialog() dlg.Filter = "SQL SERVER database backup files|*.bak" dlg.Title = "database restore" If dlg.ShowDialog() = Windows.Forms.DialogResult.OK Then TextBox2.Text = dlg.FileName RestoreButton.Enabled = True End If End Sub |
Restore Button
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | Private Sub RestoreButton_Click(sender As Object, e As EventArgs) Handles RestoreButton.Click Dim database As String = con.Database.ToString() If con.State <> ConnectionState.Open Then con.Open() End If Try Dim sqlStmt2 As String = String.Format("ALTER DATABASE [" + database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE") Dim bu2 As SqlCommand = New SqlCommand(sqlStmt2, con) bu2.ExecuteNonQuery() Dim sqlStmt3 As String = "USE MASTER RESTORE DATABASE [" + database + "] FROM DISK='" + TextBox2.Text + "'WITH REPLACE" Dim bu3 As SqlCommand = New SqlCommand(sqlStmt3, con) bu3.ExecuteNonQuery() Dim sqlStmt4 As String = String.Format("ALTER DATABASE [" + database + "] SET MULTI_USER") Dim bu4 As New SqlCommand(sqlStmt4, con) bu4.ExecuteNonQuery() MessageBox.Show("database restore done successefuly") con.Close() Catch ex As Exception End Try End Sub |
Fore more explanation, watch this video:
Backup and Restore Sql Server database in VB.NET
Reviewed by Bloggeur DZ
on
01:21
Rating:
Excellent .. Amazing .. I’ll bookmark your blog and take the feeds also…I’m happy to find so many useful info here in the post, we need work out more techniques in this regard, thanks for sharing. this
RépondreSupprimeri have an erorr
RépondreSupprimerGood to see backup and restore sql server database details.
RépondreSupprimerVisual Foxpro Migration and Foxpro Migration