'How to restore a SQL Server bak file with VB.NET from a remote computer?

We have a utility database to be used in SQL Server that is going to be distributed in the format of a .bak file. Then I am writing this small VB.net application to restore it in SQL Server, but it's not working as supposed.

Basically the user should click in a button to locate the .bak file in his computer, and the app will do the rest. The problem is that SQL Server is saying that cannot open the .bak device because access is denied.

Here is my code:

Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
    LoadServers()
End Sub

Private Sub LoadServers()
    Dim instance As System.Data.Sql.SqlDataSourceEnumerator = System.Data.Sql.SqlDataSourceEnumerator.Instance
    Dim table As System.Data.DataTable = instance.GetDataSources()
    DisplayData(table)
End Sub

Private Sub DisplayData(table As System.Data.DataTable)
    Dim row As System.Data.DataRow
    Dim col As System.Data.DataColumn
    Dim result As String = ""
    For Each row In table.Rows
        For Each col In table.Columns
            If col.ColumnName = "ServerName" Then
                cmbServers.Items.Add(row(col))
            End If
        Next
    Next
    cmbServers.DropDownStyle = ComboBoxStyle.DropDownList
End Sub

Private Sub btnInstall_Click(sender As Object, e As EventArgs) Handles btnInstall.Click
    Dim cn As New SqlConnection("Data Source=" & cmbServers.Text & "; Initial catalog=Master; Integrated Security=true")

    Try
        Dim ofd As New OpenFileDialog With {
            .Filter = "Backup File | *.bak",
            .FileName = ""
        }

        If ofd.ShowDialog = DialogResult.OK Then
            Cursor = Cursors.WaitCursor
            Dim sql As String = $"restore database FOOTEST from disk = '\\" & "\" & My.Computer.Name.ToString() &  ofd.FileName.Replace(":", "") & "'"
            '\\mycomputername\c\users\chev\desktop\mybakfile.bak
            Dim cmd As New SqlCommand(sql, cn)
            cn.Open()
            cmd.ExecuteNonQuery()
            MsgBox("Installation complete")
            Cursor = Cursors.Default
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

When it tries to execute the restore command the code is being caught by the try-catch block with the following error message:

System.Data.SqlClient.SqlException: 'Cannot open backup device 'C:\Users\foo\Desktop\mydb.bak'. Operating system error 5(Access is denied). RESTORE DATABASE is terminating abnormally.'

The .bak file I am using is on a different computer of SQL Server (I am running the app from my work computer and the restore should happen on the server). However since I am using pipe syntax (double backslash) together with my computer name plus the path where the file is AND my drive C is shared, I don't understand why it's saying access is denied.

Any idea?

Thanks!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source