Backup SQL Server localdb in vb.net

Question

I want to make a backup copy of my SQL Server localdb database. I tried with this code:

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim Sfd As New SaveFileDialog() With {
        .Filter = "Backup file | *.bak",
        .FileName = ""
    }

    If Sfd.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            Cursor = Cursors.WaitCursor

            Dim dbname As String = "RestoDB.mdf"
            Dim sql As String = "Backup database [" + System.Windows.Forms.Application.StartupPath + "\RestoDB.mdf] To DISK = '{Sfd.Filename}'"
            Dim cmd As New SqlCommand(sql, con)
            cmd.ExecuteNonQuery()
            MsgBox("Backup complete")
            Cursor = Cursors.Default
        End If

    End Sub

I get an error telling me

does not exist. Make sure that the name is entered correctly.

This is the connection string:

Private con As New SqlClient.SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\CHAKER\Documents\RestoDB.mdf;Integrated Security=True;Connect Timeout=30")

Andrew Morton · Accepted Answer · 2021-11-22 11:01:10Z

2

It is unlikely (and it should not be the case) that System.Windows.Forms.Application.StartupPath is "C:\Users\CHAKER\Documents".

What you could do is get the current user's documents folder and use that to construct the full path to the database file:

Dim dbName As String = "RestoDB.mdf"
Dim dbFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
Dim dbFullPath = Path.Combine(dbFolder, dbName)
Dim sql = $"Backup database ""{dbFullPath}"" TO DISK = '{Sfd.Filename}'"

(The example I saw used double-quotes around the database parameter, not square brackets.)

But it would be better to use the value from the connection string:

Dim csb = New SqlConnectionStringBuilder("yourConnectionString")
Dim dbFullPath = csb.AttachDBFilename
Dim sql = $"Backup database ""{dbFullPath}"" TO DISK = '{Sfd.Filename}'"

If the connection string was stored in a variable, it would only be need to be changed in one place if it needed to be edited.

If you are using a version of VB before string interpolation was introduced, you can use

Dim sql = String.Format("Backup database ""{0}"" TO DISK = '{1}'", dbFullPath, Sfd.Filename)

edited Nov 22, 2021 at 11:01

answered Nov 21, 2021 at 17:37

Andrew Morton

25.3k9 gold badges66 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chaker Over a year ago

i get this exception Database '{dbFullPath}' does not exist. Make sure that the name is entered correctly. i tried your second code i want to ask you please about dollar sign i use VS 2013 so i get a message Character is not valid

Andrew Morton Over a year ago

@Chaker I've edited the answer to show how to use String.Format.

Chaker Over a year ago

The code is working thank you

Andrew Morton Over a year ago

@Chaker You're welcome :) (There's no need, on Stack Overflow, to add an answer with the code that you ended up using.)

Chaker · Accepted Answer · 2021-11-23 11:25:49Z

I use this code for backup

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim Sfd As New SaveFileDialog() With {
.Filter = "Backup file | *.bak",
.FileName = ""
}
        If Sfd.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            Cursor = Cursors.WaitCursor

            Dim csb = New SqlConnectionStringBuilder("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\CHAKER\Documents\RestoDB.mdf;Integrated Security=True;Connect Timeout=30")
            Dim dbFullPath = csb.AttachDBFilename
            Dim sql = String.Format("Backup database ""{0}"" TO DISK = '{1}'", dbFullPath, Sfd.FileName)
            Dim cmd As New SqlCommand(sql, con)
            cmd.ExecuteNonQuery()
            MsgBox("Backup complete")
            Cursor = Cursors.Default
        End If

    End Sub

Collectives™ on Stack Overflow

Backup SQL Server localdb in vb.net

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related