Search code examples
sql-serversql-server-2012asp.net-mvc-5shared-hostingsmo

SMO backup reports can't open backup device (which is a file), Message 3201

This code runs fine on a local SQL Server Express instance:

Dim thisSMOBackup As New Backup()
With thisSMOBackup
  .Database = singleDatabase.Name 'This contains the database name
  .Action = BackupActionType.Database
  .Devices.AddDevice(DatabaseFileName, DeviceType.File)
  .Incremental = False
  .LogTruncation = BackupTruncateLogType.Truncate
End With
Try
  thisSMOBackup.SqlBackup(thisServer) 'thisServer is setup w/ valid connection string and reads back data OK from the SQL server
  System.IO.File.AppendAllText(logfileName, singleDatabase.Name & " - backed up")
Catch ex As Exception
  System.IO.File.AppendAllText(logfileName, singleDatabase.Name & " - " & ex.InnerException.Message & vbCrLf)
End Try

When I apply the same code to a shared hosting SQL Server, it yields the following inner exception error message:

Cannot open backup device 'D:\aFolderName\aBackupName\theDatabaseName.bak'. Operating system error 3(The system cannot find the path specified.). BACKUP DATABASE is terminating abnormally.
Message #: 3201

There seems to be different ways to connect to the servers. For the SQL Server Express instance, I am using the .ServerInstance property, for the hosted server I am using the .ConnectionString property. Does that have anything to do with how the SMO code functions?

I have read that shared SQL hosting servers can have restrictions that are difficult to get past, but before I give up I wanted to ask for input.

The error itself suggests SMO can't find the location to write the file, but it's there, and I set the security for the folder to "Everyone" with "Full Control". But then errors seem to not always mean precisely what they say. I wondered if there were some other parameters I need to setup for this operation to succeed, or other places to look.

Solution

  • When you're trying to create a backup in your shared hosting environment, the backup file is being written to the file system of that remote server machine (NOT your own, local D: drive!) where your SQL Server is running.

    Most likely, the user account that runs the SQL Server remotely just doesn't have permission to write to that directory.

    Also: this is a good thing! After all, you wouldn't want a remote server machine which you don't know and have no control over to be able to have direct write access to your own, local machine - now would you?

    What you could do (esp. in a corporate environment) is to write backups to a UNC path (\\server\share\path) where both the remote SQL Server machine has write access to, and to which you have at least read access, so that you could go there and copy the .bak files to your local machine as needed. With a commercial hosting company, that option very rarely exists, however....