Sql Server Backup and move backup file: How to cope with file permissions?
- by Stefan Steinegger
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.
