Sql Server Backup and move backup file: How to cope with file permissions?

Question

With our product we have a simple backup tool for the sql server database. This tool should just make a full backup and restore to and from any folder.

Of course, the user (usually an administrator) needs permission to write to the target folder.

To avoid the problem of not being able to perform a backup to a network drive, I write the backup to a temp file in the Sql Server backup directory. Then I move it to the target folder. This requires permission to delete the temporary file from the sql servers backup folder. Restore is the same in the other direction.

This seemed to work fine until someone tested it on vista, where the user does not have write access to the backup folder by default.

So there are many solutions to solve this, but none of them seemed to be really nice.

One solution would be to find another folder for the temporary file. Both the sql server user as well as the administrator performing the backup need read and write permissions. Is there such a directory?

Any other ideas? Thanks a lot.

---

Edit: Solution must work with Sql Server 2005 and 2008, C# 3.0 (Smo), Windows XP and Vista.

Answer 1

Applications performing backup operations can request to open files in the backup mode. What that means is that the application has the SeBackupPrivilege privilege and it opens files with the FILE_FLAG_BACKUP_SEMANTICS flag set:

SeBackupPrivilege—allows file content retrieval, even if the security descriptor on the file might not grant such access. A caller with SeBackupPrivilege enabled obviates the need for any ACL-based security check.

While this sounds technical and complicated, what it boils down to in practice is to copy the files using ROBOCOPY /B

Answer 2

Ok, I found the solution. It is actually pretty simple if you know a bit more about Vista then I do.

The backup folder is configured the same way as under XP, but the administrator permissions are not available until the process is in elevated mode. To get there, I just needed to add a manifest to the executable which requires the administrator privileges:

<requestedExecutionLevel level="requireAdministrator" uiAccess="false" />

Now I get an UAC when calling the application and everything works fine.