DB backup to UNC path failing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AJC - 28 Oct 2005 14:12 GMT

We are trying to backup a DB from a W2K3 Cluster running SQL2000Ent. the
following is the backup command from the created Job:

BACKUP DATABASE [NGProd] TO DISK =
N'\\Mviparprtsrv01\NextGenRoot\DBBackup\NGProd_data_dump.bak' WITH INIT ,
NOUNLOAD , NAME = N'NGProd backup Report Server Backup', NOSKIP , STATS =
10, NOFORMAT

The job fails with the following errors from the Job history log:

1) The job failed. The Job was invoked by User MVIPA\sgadminclt. The last
step to run was step 1 (Step 1). [This is the only step in the job]

2) Executed as user: MVIPA\mvipasql. Cannot open backup device
'\\Mviparprtsrv01\NextGenRoot\DBBackup\NGProd_data_dump.bak'. Device error or
device off-line. See the SQL Server error log for more details. [SQLSTATE
42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE
42000] (Error 3013). The step failed.

And here are the errors from the SQL Error log:

BackupDiskFile::CreateMedia: Backup device
'\\Mviparprtsrv01\NextGenRoot\DBBackup\NGProd_data_dump.bak' failed to
create. Operating system error = 5(Access is denied.).

BACKUP failed to complete the command BACKUP DATABASE NGProd TO DISK =
N'\\Mviparprtsrv01\NextGenRoot\DBBackup\NGProd_data_dump.bak' WITH INIT ,
NOUNLOAD , NAME = N'NGProd backup Report Server Backup', NOSKIP , STATS =
10, NOFORMAT

The accounts have access to the servers and paths and the job will work if
we use a local path on the SQL cluster. This leads me to believe that we can
only use local paths and then copy the DB to the remote server.

Can anyone confirm or deny my beleif?

Thanks, Al

Reply to this Message

Andrew J. Kelly - 28 Oct 2005 15:10 GMT

Backups are done under the account that SQL Server runs under. Make sure
the account that SQL Server is running under has permissions to that share
and it should be fine.

Signature

Andrew J. Kelly SQL MVP

> We are trying to backup a DB from a W2K3 Cluster running SQL2000Ent. the
> following is the backup command from the created Job:
[quoted text clipped - 38 lines]
>
> Thanks, Al

Reply to this Message

AJC - 28 Oct 2005 16:04 GMT

Yes, the account has permissions to the share (Fll Control) as well as the
NTFS permissions (Full Control). Still does not work.

> Backups are done under the account that SQL Server runs under. Make sure
> the account that SQL Server is running under has permissions to that share
[quoted text clipped - 42 lines]
> >
> > Thanks, Al

Reply to this Message

Geoff N. Hiten - 28 Oct 2005 15:19 GMT

HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128

I have used this technique from a cluster with complete success.

Signature

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

> We are trying to backup a DB from a W2K3 Cluster running SQL2000Ent. the
> following is the backup command from the created Job:
[quoted text clipped - 38 lines]
>
> Thanks, Al

Reply to this Message

AJC - 28 Oct 2005 16:11 GMT

I have run through this article and am setup as it requires but still not ble
to get a successful backup. The error that leaps out at me as confusing is
from the Job history log "Device error or device off-line".

> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
[quoted text clipped - 43 lines]
> >
> > Thanks, Al

Reply to this Message

Geoff N. Hiten - 28 Oct 2005 16:18 GMT

Did you log in from the console of the SQL Server as the SQL Service
account? That is the key element to test whether you can write backup files
to that location.

If you are appending to a broken backup file, this error can occur. Delete
or rename the existing file and see how it works. Make sure there is plenty
of free space on the target drive.

Signature

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

>I have run through this article and am setup as it requires but still not
>ble
[quoted text clipped - 58 lines]
>> >
>> > Thanks, Al

Reply to this Message

AJC - 28 Oct 2005 16:36 GMT

I am unable to logon from the console, the server is in Portland and I am in
Charlotte. I am testing via Remote Desktop. Through RDP, logging on as the
service account, I can write to the share and connect to it. I can have an
admin out there try from the console.

I also setup a new test, when I typed in the UNC path in the "Use this
directory" and clicked next, I get thie error "Backup directory is invalid.
Are you sure this is what you want?" I have a yes and no button, if I choose
Yes and test the job, if fails to run and create the backup file.

> Did you log in from the console of the SQL Server as the SQL Service
> account? That is the key element to test whether you can write backup files
[quoted text clipped - 66 lines]
> >> >
> >> > Thanks, Al

Reply to this Message

Geoff N. Hiten - 28 Oct 2005 17:07 GMT

RDP to the server works just fine for testing. You should not be getting
the invalid backup directory error. For some reason, the SQL service is
unable to connect to the target file server. Was there a password change
for the service account since the SQL Service started? SQL would be using
stale credentials in that case.

Try doing an xp_cmdshell "dir \\uncservername\pathname" to see if SQL can
even read the directory. You may have to do a service stop/start or a
complete host reboot to clear stale credentials.

Signature

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

>I am unable to logon from the console, the server is in Portland and I am
>in
[quoted text clipped - 89 lines]
>> >> >
>> >> > Thanks, Al

Reply to this Message

AJC - 28 Oct 2005 17:31 GMT

No password change but I did notice that the account had modified. I also
noticed that the way the account show up in services was different on the
Cluster and on the share backup server. On the Cluster it was
"mvipasql@mvipa.ad", whereas on the backup server it was listed as
"mvipa.ad\mvipasql"? I went into the services on both servers and edited the
accounts by Browsing and choosing the domain mvipasql account (there is not a
local account anyway) and retyping the password. Am running a test now but it
appears that those changes have fixed the issue. Looks like either stale
credentials, otherwise the service would not have started with an incorrect
password.

Thanks for all your help.

Al

> RDP to the server works just fine for testing. You should not be getting
> the invalid backup directory error. For some reason, the SQL service is
[quoted text clipped - 99 lines]
> >> >> >
> >> >> > Thanks, Al

Reply to this Message

Geoff N. Hiten - 28 Oct 2005 18:59 GMT

BTW, it is a better to use Enterprise Mangler to change SQL Service
passwords. It is essential to use EM to change accounts for a Clustered SQL
Server.

Signature

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

> No password change but I did notice that the account had modified. I also
> noticed that the way the account show up in services was different on the
[quoted text clipped - 132 lines]
>> >> >> >
>> >> >> > Thanks, Al

Reply to this Message

Linchi Shea - 30 Oct 2005 04:37 GMT

> BTW, it is a better to use Enterprise Mangler to change SQL Service

Enterprise Mangler? Hmmm... that must be a better tool to use :-)

Linchi

> BTW, it is a better to use Enterprise Mangler to change SQL Service
> passwords. It is essential to use EM to change accounts for a Clustered
[quoted text clipped - 144 lines]
>>> >> >> >
>>> >> >> > Thanks, Al

Reply to this Message

Geoff N. Hiten - 31 Oct 2005 01:07 GMT

I have a bad habit using that name. :)

Signature

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

>> BTW, it is a better to use Enterprise Mangler to change SQL Service
>
[quoted text clipped - 155 lines]
>>>> >> >> >
>>>> >> >> > Thanks, Al

Reply to this Message