Avatar of jdr0606
jdr0606
Flag for United States of America asked on

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
Microsoft DevelopmentMicrosoft SQL Server

Avatar of undefined
Last Comment
jdr0606

8/22/2022 - Mon
igordevelop

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
jdr0606

ASKER
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.
SOLUTION
Anthony Perkins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jjjosef

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# ~
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
igordevelop

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
ASKER CERTIFIED SOLUTION
jdr0606

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jdr0606

ASKER
Completed the permission solution