backup-Restore job error..

I have a database "Sales"  on sql server 2005 server named SrvPro and this database is backuped automatically everyday to this shared folder called Backup folder with Sales.bak then this backuped database is restored everyday on the sql server 2012 with the same name with a job named Restore..but when I check the job history on sql server 2012 server, ı get this error "
 Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot open backup device '\\SrvPro\e$\Backup\Sales.bak. Operating system error 32(The process cannot access the file because it is being used by another process.). [SQLSTATE 42000] (Error 3201)  RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

or

The job failed.  The Job was invoked by Schedule 9 (Restore).  The last step to run was step 1 (Restore).

Sql server Agent is working as NT Service\SQLSERVERAGENT on sql server 2012(REstored server)
Backup folder has Everyone Full permission on SrvPro(Backuped server)

what should I do to fix this problem?
Teoman SahinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can only perform a Restore operation if no one is connected to the database. You need to assure that when the Restore job runs that no one is connected to the database.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Before you ask, here's the command to kick everyone out of the database:
ALTER DATABASE N'Sales'
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

Open in new window

It's better you put the code for restoring the database immediately after this one so no one will have time to connect.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nishant joshiTechnology Development ConsultantCommented:
we can check few things as per first review.

make sure file is exists
SrvPro server's shared path is accessible
you are restoring on database might that database file is in use.

You can install Process Explorer provided by Microsoft on server and try to find out which process is using backup or db file.

Process Explorer

Nishant
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Salah Eddine ELMRABETTechnical Lead Manager (Owner)Commented:
Hi,

As explained by Vitor, this is not related to backup file access problem but Database connection problem,

You need to have exclusive access in order to restore the database, or you can move the database to offline status before restoring.

For more information have a look here:

Getting exclusive access to restore SQL Server databases

How to Automate SQL Server Restores for a Test Server

Best Regards.

Salah
0
Teoman SahinAuthor Commented:
thanks so I guess I have to rise it from single mode to multiuser mode after restoring

ALTER DATABASE N'Sales'
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

Restore code

alter database  N'Sales set multi user;

is that right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No need to. For sure when the database was backed up it was already in multi user so the restore will bring the database to that status immediately.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Thanks but why the B grade?
0
Teoman SahinAuthor Commented:
I have realized that the times of servers was different therefore the restore process did not start because the backup  process in progress but your solution was good if the server times was the same
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.