I ran into this little gotcha a little while ago, so I
thought I’d share it and break my blog silence.
One of our clients hosts their own sql server (express) and
website, and I kept noticing that there was no way to do a backup. When I went into sql management studio and tried
to add a backup destination, it didn’t give an option for file name, only
tape. And the “Backup device” section
was disabled.
Even running the “backup” command from t-sql didn’t
work. It gave me an access error. This was my clue. The problem was that the sql express service
was running as the “Network Service” login, which didn’t have access to any
folders on the file system. I gave it
access to one folder, and then Management Studio let me pick destinations from
the file system. I was also able to add
backups sets and run backups from the command line. I could have changed the network service to
run as “system” but didn’t to mess with someone else’s box.
Pretty
simple fix, but not very obvious what the problem was.