SQL Network Backups

[CBell]

Hi, I've inherited a SQL problem. I'm a VMS system manager with a little SQL background. THe problem is a Log FIle backup thats goes to a Mapped network drive. It always fails with a device error or device offline. If I look in the event viewer, the error there is "Operating System error = 5 (access denied)". Another problem is this, if I go to create a new job, the mapped network drive ("I" in this case) does not show up in the list of places to put the backup. Is there something I need to do in order for ENterprise Manager to see the mapped drive? This is a NT 4.0 server with SQL 7.

Thanx in advance,

Chris...

 

[tlbroadbent]

SQL Server runs with its own set of credentials and cannot see drives mapped by the Windows login at the server console. You'll need to backup using UNCs (\\servername\path) and make sure the SQL Agent login has requisite permissions on the server\path where you want to create the backup file. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CBell]

We tried using the UNC in the backup and it still fails to verify the existence of the network drive we want to backup too. We also treid to create a new Backup Device with the UNC and backup filename and it fails to create with a "unable to verify existance" of the network area where we want the backup to go. Also the SQL server and the system we want the backups to go to are in different domains.

Thanx,

Chris...

 

[RickCole]

Make sure the area you are trying to make your backup to has an appropriate share name set up and that your SQL server has the correct permissions.

As a test log onto you SQL Server with the account name which your SQL Server runs under. Navigate to the share via explorer and make sure you can write a text file to the share.

Rick.

 

[CBell]

Rick,

THe SQL server runs under the Local System account. Is this the correct way to run this or should it be run under a domain account with privs?

CHris...

 

[tlbroadbent]

In order to backup on a diferent server, SQL Agent and SQL Server must run on domain accounts. They must have permissions to read and write on the other server. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CBell]

Thanx for the info so far guys... I kinda figured hte service would need privs in order to run over the network. I also just found out the reason they account was set to use the Local System is because they were having problems with the "full text search" building when the service was set to run under a domain account. What they did to cure this was to set the SQL Server service to run under the local system account. If I change this to run under the domain account, will the full test search build continue to work?

Chris...

 

[RickCole]

Chris,

I've not done too much in the area of full text search but I reckon most sites run their servers under a domain account. By that logic I can't believe full text search won't work. If it doesn't then there must be a work around.

Rick.

 

[CBell]

I really don't understand why it wouldn't work either. These problems arose before I got here. I'm gonna change the login to use a domain account tonite and than have the operators reboot the server during "quit time" and than try the backups tomorrow. I'll keep ya posted...

Chris...

 

[tlbroadbent]

I beleive the usual recommendation is to run SQL Server on a Domain Account that is a Local Administrator. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CBell]

I changed the SQL server service to use the domain account (the agent uses the same account). THis account has admin privs attached to it also. We tried the backup this morning and it still fails when we try to create a backup device with the UNC to the network machine. Keeps coming back with the "unable to verify existance" error. Or if we try a manual backup with the UNC in the script, it fails with a device error or device offline error.

Chris...

 

[tlbroadbent]

Have you verified that the domain account has access to the server and path specified? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CBell]

Terry,
Yes. I can map a network drive to the network machine.

Chris...

 

[RickCole]

Have you verified that you can log onto you SQL Server with the account name which your SQL Server runs under. And then create and write a txt file to the same shared folder where your backup is going too??

Your UNC name used in filename when creating the backup device should be something like this:

\\DS01\Sqlbackups\DS01_Diskdumps.bak

DS01 = Servername you are going to.

Sqlbackups = The share name of the folder you are writing too with approapriate permissions.

DS01_Diskdumps.bak = The actual file name within the shared folder where the backups will be held.

Rick.

 

[Dilyias]

Hmm, you should be able to use a mapped drive for backups in SQL 7.

I've done this and have been able to choose the mapped drive. Does it work if you manually type in the path (i.e. I:\BACKUPS) while creating the backup device?

We also run SQL as the domain admin, also part of the local administrator group on the servers.. We just upgraded our servers from NT4 to 2000 (still using SQL 7) so I can't duplicate your problem..but I'm fairly certain that I was able to use mapped drives while on NT 4.

Also, it is possible in your case as a last resort to save the backup locally and then run a script (in another job step..) to copy that file to the networked drive?

 

[CBell]

I just did a test. The backup job that fails uses the sql SA account. I logged onto SQl with a domain users acct with admin privs (acct. is superuser). Started the backups and everything worked fine. But why won't the backup work with the SQL sa acct.? My boss told me they have always used the sa acct. for this backup.

Chris...

 

[Dilyias]

I suppose you could go the the database/Users, and compare the properties (membership roles, etc..) between administrator and dbo(sa). Also check out security/logins.

Might find something out there...

Hope this was helpful info!

-Eric

 

[tlbroadbent]

Try a couple of tests. Open Query Analyzer and execute commands like the following. I assume you xp_cmdshell is enabled.

--This will demonstrate if SQL Server can see the path you want to write backups to.
exec xp_cmdshell 'dir \\servername\backuppath\'
go

--This will display the SET of credentials SQL Server uses.
exec xp_cmdshell 'set'
Go

This information should help you determine where the failure occurs. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CBell]

I got it to work. THanx for all the info, it really helped. What I did was on the account we want to use for the backups, I went into the Security/Logins and noticed the Server Roles had every role chcked off. So I removed all the checks except the System Admin one. THan I checked the DB Access and noticed the db_owner was not checked for the DB we were trying to back up. CChecked the db_owner and all is well...

THanx again....

Chris...

 

[BeckahC]

I was just curious, I'm going to be learning VB soon -

My app is currently running on ASP with a SQL Server 7.0 database. It has been decided that the next version should be written in VB (probably 6) using the same databse. I need to learn the VB language, more than what I learned for the ASP (VB Script) and in more detail.

Would any of you suggest these books, or similar for someone with a programming backgroud, but just starting to get into VB?

Thanks in advance! BeckahC