Backup SQL Server localdb in vb.net-CodePudding

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")

CodePudding user response：

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)

CodePudding user response：

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