Backup to UNC/ Network share

Question

Backup to UNC/ Network share

SQL33

SSCrazy

Points: 2044
More actions
March 11, 2011 at 11:55 am

#147599

I need to backup to an off server network share (UNC) where my domain account has write permissions. When I run the backup I get an error message indicating per this article http://support.microsoft.com/kb/207187 that I need to have the MSSqlServer services logged in using a domain account versus the local service account it's currently logged in on. My question then, when I shift this login from the local service login to my domain login are their any ramifications to doing this? The setup in general is that the Win2k Server running SQL2000 SP3 with various databases supporting clients running on various workstations using database level logins to access the data tables in the various databases..
Thanks...

Viewing 5 posts - 1 through 4 (of 4 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 1

First, don't shift to your domain account. That's a poor security practice, and depending on how your account is managed, it could be an issue.

Create a new domain account, with just Everyone permissions. Use Enterprise Manager to select that as the service account for SQL Server so appropriate permissions are setup. I would also create an AD group and add the service account to this group. Add the permissions to the folder for this group to write.

Now, be aware that network hiccups will cause your backup to fail. A network backup is not like a file copy, and there are no retries. If the network doesn't handle all the writes, the backup just ends. The recommendation is to back up on your server, then copy to the network.

SQL33 SSCrazy Points: 2044 More actions · Answer 2

Thanks for the advice... I've tried periodicially to find an add-on or tool to do just what you recommend here.. a file copy with error checking and delete of the source on the server, but not had any luck so far. Can you recommend a tool for doing that?

Thanks...

Steve Jones - SSC Editor (3/12/2011)
First, don't shift to your domain account. That's a poor security practice, and depending on how your account is managed, it could be an issue.
Create a new domain account, with just Everyone permissions. Use Enterprise Manager to select that as the service account for SQL Server so appropriate permissions are setup. I would also create an AD group and add the service account to this group. Add the permissions to the folder for this group to write.
Now, be aware that network hiccups will cause your backup to fail. A network backup is not like a file copy, and there are no retries. If the network doesn't handle all the writes, the backup just ends. The recommendation is to back up on your server, then copy to the network.

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 3

We have lots of scripts here that will do that. Look at some of these:

http://www.sqlservercentral.com/search/?q=backup+and+copy&t=s

Craig Purnell SSCrazy Points: 2720 More actions · Answer 4

After shifting to a domain service account, you should also register the SPN in Active Directory using the setspn utility so that SQL uses Kerberos. Here is an article that explains it: