Backup to Share with SMO fails, but works to local hard disk

I have a short program (vb.net) which backs up an SQLExpress database. It works fine if the destination is a file on the local drive. However, it always fails if the destination file is a network share or a mapped share. The same program verifies the share is available by trying to read and write a small file before performing the backup.

This is a stripped down version of the code

Dim svr As Server = New Server("Servername")
svr.ConnectionContext.LoginSecure = false
svr.ConnectionContext.Login = TextBoxBULogin.Text
svr.ConnectionContext.Password = TextBoxBUPwd.Text
Dim bkp As Backup = New Backup()
bkp.Action = BackupActionType.Database
bkp.Database = TextBoxBUDatabase.Text
Dim x As New Microsoft.SqlServer.Management.Smo.BackupDeviceItem(destfilepath, DeviceType.File)
bkp.Devices.Add(x)
bkp.SqlBackup(svr)

The exception generated is an SMO.FailedOperationException and reads "System.Data.SqlClient.SqlError: Cannot open backup device 'Z:\v1tbcontacts00.bak'. Device error or device off-line."

Where "Z:\" is a network share. If the backup device string is set to "C:\v1tbcontacts00.bak" the backup succeeds.

Have you tried to use Transact-SQL to backup to the share? In any case you may find it better to back up to the local drive, then copy the backup file using operating system commands to the network share for performance purposes.|||

Henry,

using SQL authentication will make SMO to use the SQL Server Sevice account to authenticate on the network share, so although you might be able to check the network share for existance within your application, the credentials used for this is not used for making the backup on the file share.

Client application (Windows token) --> SQL Server using Windows authentication and connected with Windows Authentication --> SMO uses the Windows credentials of the connected client to access the share

Client application (Windows token) --> SQL Server using Mixed Authentication and connected with SQL Server Authentication --> SMO uses the SQL Server Service account credentials of to access the share

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The practical solution to my issue is to perform a database backup to the local hard disk and then copy the backup to a share.

Better still. the nugget of information about how authentication works with SMO explains why the problem occured in the first place.

Thanks