Network Backup

Question

Network Backup

ionas

Mr or Mrs. 500

Points: 551
More actions
December 6, 2010 at 5:44 am

#243604

Hi,
I need to have a network backup to an SQL 2005 db. The OS is SBS 2003 and SQL 2005 is 9.0.4912 (SP4). When I try to set SQL SERVER SERVICE to log on as "Network service" i get the error "A new member could not be added to a local group because the member has the wrong account type".
Any ideas or workaround ?

Viewing 15 posts - 1 through 15 (of 15 total)

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

I don't completely understand what you're trying to do here. SQL Server backups should be run from within SQL Server using the backup syntax:

BACKUP DATABASE X TO DISK = 'some/location'You can't simply run standard backup processes against SQL Server because it maintains locks on the files. You can use some of the transaction-aware SAN snapshot utilities, but most people simply run backups from within the server.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ionas Mr or Mrs. 500 Points: 551 More actions · Answer 2

As the topic says I need to have a network backup. I need to backup sql db to a network drive. To my knowledge, the sql server service should run as a "network service" to access a network drive. When I try to start the service with option "Log as a network sevice" i get the error i mentioned.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

No, SQL Server should run as an account on your system that has access to that drive. We usually use a common service account for most of our systems, but sometimes we'll use specific accounts on systems that require more security. Either way, it's a MyDomain/UserName type of account. That account needs access to the network resource in question, and you're good to go.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ionas Mr or Mrs. 500 Points: 551 More actions · Answer 4

Basically I have two options for SQL service:

1.Built in account (Network service, Local system, Local service)

2.This account

Cuurently, I use first option and "Local system" but the drive is not accesible. Can you, please, clarify your suggestion ?

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

First, read this: http://msdn.microsoft.com/en-US/library/ms143504%28v=SQL.90%29.aspx#Use_startup_accounts

You can use a domain account, a local account, or a built in account. I'm not sure what "this account" is.

The NETWORK SERVICE account doesn't necessarily have access to a remote drive. It can be configured, but I would not do this. Most people use a domain account, and set specific rights to specific folders. If you use Configuration Manager to change accounts, it will assign the proper rights to the account, assuming you have the rights to change the service account and administer the computer.

As another note, the backup to a network drive is not recommended. Any network hiccup, and you likely have lots of them on any network, and the backup fails. SQL Server does not function like a file copy, so any operation that requires a retry fails the backup. The way to get your backups to the network is to back them up locally, and then make a copy on the network.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 6

Yep, what Steve said. Sorry I wasn't clear. It's a domain account that I was describing without saying it properly.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ionas Mr or Mrs. 500 Points: 551 More actions · Answer 7

Ok. To be honest I didn't like to backup to a local drive and then move the files to a network drive. I tried to find an one step solution.

Anyway, thanks for your time and support.

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

As Steve and Grant mentioned, use a domain account for the SQL Server service and then provide Network drive access for this account and then provide the UNC path of the Ntk drive as the backup destination.

Thank You,

Best Regards,

SQLBuddy

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 9

ionas (12/6/2010)
Basically I have two options for SQL service:
1.Built in account (Network service, Local system, Local service)
2.This account
Cuurently, I use first option and "Local system" but the drive is not accesible. Can you, please, clarify your suggestion ?

I couldnt help but get frustrated while reading this thread. Your very questions shows a lack of expirience with both SQL and basic backup operations and network usage and security.

What Steve and Grant say is correct, it IS possible to do network backups (we do them to our SCF facility) but we have an extremely reliable infrastructure. You cannot or should not use a local account to authenticate against a network resource and i wouldnt even consider using a local system account to run a production sql service on.

Suggest you sit down and look at your setup and compare to some 'best practices'.

Adam Zacks-------------------------------------------Be Nice, Or Leave

paul.knibbs SSCoach Points: 15320 More actions · Answer 10

Just to also point out, LOCAL SYSTEM has the same network access privileges that NETWORK SERVICE does--both identify themselves via the computer account when connecting to network resources. It's in the permissions those accounts have on the *local* computer that they differ.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 11

I thought local system ONLY had local access. Network Service is supposed to use the computer account on the domain, so it has whatever access that account has.

You really ought to use a domain account, normal user, just Everyone on the domain, and then a role for specific rights to shares. Let Configuration Manager assign the local rights.

paul.knibbs SSCoach Points: 15320 More actions · Answer 12

The only differences in network access between LOCAL SYSTEM, LOCAL SERVICE and NETWORK SERVICE relate to what credentials they supply when they access the network, AFAIK. LOCAL SYSTEM and NETWORK SERVICE both use the computer account, LOCAL SERVICE (Win2k3 and later only) presents anonymous credentials. In most cases this will make little difference because people don't generally configure network resources with anonymous or computer account access, but there's nothing else preventing network access from services running under those accounts.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 13

Interesting. Wasn't aware of that, but it makes sense. Any reference you're aware of?

paul.knibbs SSCoach Points: 15320 More actions · Answer 14

MSDN description of LocalSystem account is here:

http://msdn.microsoft.com/en-us/library/ms684190%28VS.85%29.aspx

It has links to the other two service account descriptions.