Link to home
Start Free TrialLog in
Avatar of John Porter
John PorterFlag for United States of America

asked on

Cannot save Backup to a mapped drive location with SQL Backup script

Hello Experts,

I am using the following SQL Backup script:

USE master;
ALTER DATABASE MyDataBase
SET RECOVERY SIMPLE;
BACKUP DATABASE GF_Eharvest_2010
TO DISK = 'C:\Backups\MyDataBase.bak' WITH FORMAT;

ALTER DATABASE msdb
SET RECOVERY SIMPLE;
BACKUP DATABASE msdb
TO DISK = 'C:\Backups\msdb_MyDataBase.bak' WITH FORMAT

This works fine when backing up to the local C:\ drive..

When I try this it does not work:

USE master;
ALTER DATABASE MyDataBase
SET RECOVERY SIMPLE;
BACKUP DATABASE GF_Eharvest_2010
TO DISK = 'P:\Backups\MyDataBase.bak' WITH FORMAT;

ALTER DATABASE msdb
SET RECOVERY SIMPLE;
BACKUP DATABASE msdb
TO DISK = 'P\Backups\msdb_MyDataBase.bak' WITH FORMAT

P is a network share. Do you have to change the TO DISK to TO NETWORK or something?

Does anyone know how to do this?

Thanks!
Avatar of TempDBA
TempDBA
Flag of India image

Just two things here:-
<<TO DISK = 'P\Backups\msdb_MyDataBase.bak' WITH FORMAT>> is this correct? because P drive is without a colon following.

when you are using a network drive, you need to create a UNC path and use the same for its usage. like
\\network drive\blah..\blah...

\\network drive is a UNC name here pointing to P drive which is a non local one.
Avatar of John Porter

ASKER

Yup - that was a typo should have been P:\

Also, I had tried the UNC path to no avail before but just tried again. Thought there may be a security  issue so I browsed to network folder using same UNC path as in script to make sure I can explicitly create/delete files there..

Still no Love...

I get this SQL error message:

Msg 3201, Level 16, State 1, Line 4
Cannot open backup device '\\10.0.0.1\Backups\MyDataBase.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.
Msg 3201, Level 16, State 1, Line 9
Cannot open backup device '\\10.0.0.1\Backups\msdb_MyDataBase.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 9
BACKUP DATABASE is terminating abnormally.

Any ideas?

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks