FIX: Backup Problems with SQL Server Express

For several months (if not years) I've always had problems backing up and restoring SQL Server Express databases. Usually, the error messages related "Access is Denied" to the backup file from which I was restoring. Up until now this was not a major issue because I was only using the databases internally and did not need to share or distribute them to others. However, with some recent developments in a new product we're creating, I finally resolved the issue.

The error messages always relayed the same "Access is Denied" issue. So, I assumed from the beginning that SQL Server Express did not have sufficient privileges to read and write to the backup file. Come to find out, SQL Server Express uses the NETWORK SERVICE account when the service is started. Thus, with the help of this post on the Microsoft forums I resolved the problem by changing the login account from NETWORK SERVICE to the Local System Account:

1) Go to Start -> Control Panel -> Administrative Tools

2) If you are logged in as a limited user, right-click on "Services" and click "Run As" then click "The following user:" and login as an Administrator. Otherwise, just double-click the "Services" icon.

3) Scroll down to the "SQL Server (SQLEXPRESS)" service. You'll notice that the Log On As column is set to "Network Service".

4) Right-click on the service and click "Properties".

5) Click the "Log On" tab and you'll notice the "This account:" button is highlighted. Select the "Local System account".

6) Click OK

7) Repeat items 1 - 6 for the following services:

SQL Server Active Directory Helper

SQL Server Browser

SQL Server Full Text Search (SQLEXPRESS)

SQL Server Reporting Services (SQLEXPRESS)

8) Restart your machine.

After restarting, restoring a database from the backups worked just fine. I'm not sure how this could be implemented on a hosted solution, since our final product will be delivered to a hosting company and I won't have direct access to the server. We may still have to build a script to install the database from scratch. Bummer.