'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 |
|---|
