During the second day of our most recent Pragmatic Foundation class, Sherri realized that there was a problem with the student PC’s. When they tried to back up databases to the C: drive, they received an error message stating access denied. All students had local admin rights, but it turns out that the permissions of the user account do not matter in this situation.
When trying to back up a database to your hard drive, the SQL Server service needs access to the file system. By default, users can back up to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup. If you have the need to back up to a different location, then you have to make a small change in the service settings.
From the start menu, under Microsoft SQL Server 2008 > Configuration Tools, click on SQL Server Configuration Manager.
Right click on SQL Server and click ‘Properties’.
On the ‘Log On’ tab, you have the option of changing the account that is used by SQL Server. In the ‘Built in Account’ drop down, select the Local Service account and save. You should now be able to back up to other locations other than the default.