Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

SQL Restore from networked server

I recently had a job running on a reporting server that would restore the backups from the production server over the network.  I had a system failure and had to rebuild the reporting server and now when I run the restore script I receive the following error.  I have attached the job restore script.

Msg 3201, Level 16, State 2, Line 22
Cannot open backup device '\\serv007\r$\SQLBackups\DYNAMICS_EXT\DYNAMICS_EXT_backup_201212222235.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 22
RESTORE DATABASE is terminating abnormally.
Untitled.doc
0
jdr0606
Asked:
jdr0606
2 Solutions
 
igordevelopCommented:
Hi,

I am sure this is a user permissions issue.

Try to go to the Control panel->administrative tools->services-> SQL Server

At the properties, choose log on as: local system account and also choose to allow service to interact with desktop.

I hope this will solve the problem.

Please let me know if anything.

Regards,
Igor
0
 
jdr0606Author Commented:
Unfortunately that didn't resolve the issue, I'm still getting the same error.

My current work around is to copy the .bak file using \\serv007... to the server with reporting SQL and then restore from the local source.
0
 
Anthony PerkinsCommented:
Since you appear to be backing up to a remote server, you cannot use a local system account for this, it has to be a domain account with the appropriate permissions on the SQL Server box as well as access to the remote server.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
jjjosefCommented:
The database is often running as a service under an account with no network access.

If this is the case, then you wouldn't be able to restore directly over the network.

Either the backup needs to be copied to the local machine or the database service needs to

run as a user with the proper network access.

For more information follow th given link...

SQL Server backup and restore to network drive

~ Edit by Modulus_Twelve - This post contains unattributed content from http://stackoverflow.com/questions/16491/restore-database-backup-over-the-network# ~
0
 
igordevelopCommented:
Hi,

I don't know if you have developed the script, but you can try to modify it to copy the backup file locally and than make the restore. Obviously the script can not do this directly via the remote server.

Or whoever made the script, needs to be asked to modify it a bit.

You can try yourself, to take the backup file locally and see if you can do the restore manually. If this works, than we'll know the diagnose.

Regards,
Igor
0
 
jdr0606Author Commented:
I resolved the issue and it was related to permissions.

The script I attached works as is however the SQL Server service must be configured to login to with "This Account" network account with domain permissions to the network/server location and not to the "local account".
0
 
jdr0606Author Commented:
Completed the permission solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now