Restore SQL Server Backup Database => Operating system error 5(Access is denied) using MS SQL Server Management Tool

Occasionally I have to backup a SQLServer database from one machine to other one. Many times I encounter this Access is denied error, and I always forgot why it happens. So, I decided to write the solution in this blog so that I can reference it back in the future.

Backing up the Database

1. You need to have Microsoft SQL Server Management Studio. By the way, SQL2008 SQL Server Management Studio comes with intelliSense, it makes SQL writing much easier.
2. Connect to your database, in Object Explorer windows, right click on the database->Tasks->Backup  
   
3. Add  back destination. The location can only be your database local directory
4. Select Back type to "Full", so that you can transport the database file to another database server.
5. Click OK to complete the operation

Restoring

1. Copy the backup file to your local machine that host your destination database server. SQLServer somehow limited restore location to only local machine.
2. Once copy over, right click the backup file->Properties->Security tab
3. Click Add, you should able to find a user name start with "SQLServerMSSQLUser$username$SQLEXPRESS", add this user and give your SQL user right to read and write.
4. Open your database in SQL Server Management Studio
5. In the Database folder, right click->Restore Database...  
     
6. In the "To database:" field, type a new database name
7. Select "From device:" option
8. Click the browse button on the right and add your back up file.
9. Click Option Tab
10. Make sure your "Restore As" directory has "SQLServerMSSQLUser$username$SQLEXPRESS" security right.
      
11. Double check your options, and select OK

In summary, the reason why you get an access denied error is because MS SQL Server Management Studio has its own user account when interacting with your system. As long as your SQLServerMSSQLUser$username$SQLEXPRESS has access to the read and write permission, you shouldn't encounter Access is denied error.

[Update Mar 3, 2009]

Thank you to an anonymous post, he/she reminded me that you may encounter access denied error if you pick an incorrect restore path.

MS SQL creates two files during restore, the .mdf and .ldf. Usually by default these files are restored to:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

However, sometime (for the reason I don’t know), the default restore path is not the same as above. You may not able to restore if the path doesn’t exist, but more importantly, it may not has the security right.

If you want to restore the database file to another directory, once again, make sure the restoring directory does not have any existing files and has "SQLServerMSSQLUser$username$SQLEXPRESS" user right. Otherwise SQL Management will not be able to write the files in your specified directory.