0

I'm using SQL-DMO to restore a database backup that is located on a network share into a instance of SQL Server running on the local machine.

The code works fine on my machine. But on a test VM it only works if I put the backup in a local path. If I try it on the network, it fails.

I found out the SQL server on the VM was running under the System user and changed it to run as Network Service thinking this was a problem of network access. But it didn't solve the problem.

This code fails because the call to ReadFileList returns an empty result set.

Dim restore As New SQLDMO.Restore With {
  .Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database,
  .ReplaceDatabase = True,
  .Database = dbName,
  .Files = "[" + backupFile + "]",
  .Devices = ""}
Dim fileList = restore.ReadFileList(sqlserver)
Dim dataLogicalName = fileList.GetColumnString(1, 1)
Dim dataPhysicalName = Path.GetFileName(fileList.GetColumnString(1, 2))
Dim logLogicalName = fileList.GetColumnString(2, 1)
Dim logPhysicalName = Path.GetFileName(fileList.GetColumnString(2, 2))

[SQL-DMO]This cache contains no result sets, or the current result set contains no rows.

Before this code I do a check for File.Exists(backupFile) and it passes. However, I am aware that the process that runs this check (my program) is not the same that runs the restore (SQL Server), so it is not something to go by.

What could cause this behavior? What can I do to figure out why the server cannot see the backup file?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
R. Martinho Fernandes
  • 228,013
  • 71
  • 433
  • 510

1 Answers1

1

Look in services (Start->run->services.msc) and scroll down to SQL Server. The user under the logon tab most likely does not have permission to the share.

If Sql Server is going to need access to domain resources, I'd recommend changing the service to logon as a low privileged domain user, so that it can be assigned permissions to these resources.

brian
  • 3,635
  • 15
  • 17
  • That seems like it. The VM is not on the same domain as the share, and thus has no user with permission to access it. I assumed it was on the same domain because I could use explorer and File.Exists to access the file, but that was only happening because someone had entered their credentials on explorer when accessing the share. It will take a while to confirm, so I won't be accepting this answer yet, but I'm almost sure this is the problem. – R. Martinho Fernandes Mar 28 '11 at 16:04