#Google Analytic Tracker

Pages

Feb 18, 2009

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  
    SQL Management Screenshoot1.1
  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...  
       SQL Management Screenshoot2.1
  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.
       SQL Management Screenshoot3.1
  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.

7 comments:

Anonymous said...

You don't know it, but you just saved me days of problems. Had multiple database backups FedExed to my location, tried to restore the database, and got the access denied message. My boss and I thought the other company botched the full-backup job and sent us garbage. I did a quick Google, found your page, fixed the problem in under five minutes, and now we're off and running. Thank you.

Anonymous said...

Just to add to the usefulness of this entry- When doing a restore, check the file path(s) under options, in case the original file path was different than where you are trying to restore to.

Anonymous said...

Thanks. Your input has proved invaluable.

Ron said...

I too found this very useful, the default folder set to one level higher than the data folder. I had previously been able to import backup database data without changing any settings

Anonymous said...

This was great!!! Thank you so much!

Anonymous said...

Thank you so much~ you save my day ~~~~!!!!

Anonymous said...

Owe you one! Top post!