Why am I not able to create a backup plan for TFS?

Question

I am trying to create a backup plan using the TFS Power Tools but I keep running into this error message:

I have checked that the account has Full Control on the share, I can edit, create and delete files there. From the log:

[Info   @07:15:00.403] Starting creating backup test validation
[Error  @07:15:00.700] Microsoft.SqlServer.Management.Smo.FailedOperationException: Backup failed for Server 'WMSI003714N\SqlExpress'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Cannot open backup device '\\wmsi003714n\sql dump\Tfs_Configuration_20100910091500.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 1815).
BACKUP DATABASE is terminating abnormally.
   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
   at Microsoft.TeamFoundation.PowerTools.Admin.Helpers.BackupFactory.TestBackupCreation(String path)
[Error  @07:15:00.731] !Verify Error!: Account GROUPINFRA\SA-NO-TeamService failed to create backups using path \\wmsi003714n\sql dump
[Info   @07:15:00.731] "Verify: Grant Backup Plan Permissions\Root\VerifyDummyBackupCreation(VerifyTestBackupCreatedSuccessfully): Exiting Verification with state Completed and result Error"

Any ideas?

Answer 1

I had the same problem and noticed that your share name "sql dump" has a space in it, just like mine. I removed the space character and it worked fine!!

Answer 2

Does the account you are using to run the backup also have full control (or at least write) on the file system folder that is shared (i.e. both share and folder permissions allow writing)?

Remember that the account creating the job isn't necessarily the account running it (this might not apply as, IIRC, Power Toys backup doesn't use SQL Agent).

Try monitoring the destination with Process Monitor to see if it is a failure in SQL Server (i.e. fails before any file create/write) or in writing.

Answer 3

For me it was the Share permissions not being as explicit as the FS permissions. I had all the accounts setup on the directory, but once I fixed the share then I was all set and the tests passed.

Answer 4

Make sure you don't have any special characters or spaces in the backup folder path .. in this case (sql dump) has a space in it, I removed the space character and it should work or else make sure the folder has permissions - you can change the login credentials in the Account tab .

Answer 5

Not related to this specific special character problem, but I had exactly the same error when trying a least-privilege approach. I had to add the account used for the backup to the Backup Operator local Windows group. Hope it helps other people reading this thread.

Answer 6

My solution was.

1. create a share with everyone permission on it
2. then in folder security you select Everyone account and REMOVE any rights that will let someone to write there !

In this way even someone connects to this new share that TFS will use will not be able to modify. Maybe it a good idea also to create a hidden share (I mean myshare$).

You could inspect what permissions creates and what TFS want's creating In this way and modified and testing. You will surely find what he needs to inspect a file inside.