Network Drive mapping-Automation-Production issue

Question

Network Drive mapping-Automation-Production issue

selvakumar.sms

SSC Eights!

Points: 895
More actions
March 17, 2014 at 10:38 pm

#305238

Hi,
I deployed small batch file in startup in window server.whenever we are restarting the server bacth file running successfully.
batch.bat
net use Z: \\Servername\Sharename password /user:servername\admin /Persistent:YES
network drive is mapping successfully, but when we are taking DB backup in network drive.getting below error.
Cannot open backup device '\\Servername\sharename\backup.Data'. Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally.
After i run below command in DB(SSMS).
EXEC XP_CMDSHELL 'net use Z: \\servernamne\Sharename password /user:servername\admin'
backup is running successfully.What might be the issue.Pls help its production issue.
--------------------------------------------------------
I am Learner -- SQL

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27168 More actions · Answer 1

After the restart the server your script run successfully to do the mapping. But when SQL Server runs the backup it won't able to access the path as SQL do not have permission to access that path but as soon as you execute it in SSMS it connect with SQL LOGIN and able to access.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

selvakumar.sms SSC Eights! Points: 895 More actions · Answer 2

How to overcome this one ? pls guide me.

--------------------------------------------------------

I am Learner -- SQL

free_mascot One Orange Chip Points: 27168 More actions · Answer 3

Execute the SSMS command while server starts or before backup starts

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

selvakumar.sms SSC Eights! Points: 895 More actions · Answer 4

we are manually doing for long time,but i need to automate that one.

--------------------------------------------------------

I am Learner -- SQL

free_mascot One Orange Chip Points: 27168 More actions · Answer 5

That's what, you can create a job to run either when sql server agent starts or before backup.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

selvakumar.sms (3/18/2014)
we are manually doing for long time,but i need to automate that one.

You can either use a start up stored procedure or run the command as a separate step before the backups or take the backups directly to the share.

--

SQLBuddy