Avatar of Saxitalis
Saxitalis
 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!
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Saxitalis

8/22/2022 - Mon
TempDBA

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.
Saxitalis

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
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Saxitalis

ASKER
thanks
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