March 22, 2012 at 9:40 am
Hello
I want to backup a database to a network share.
I cannot do that because sql agent service is running as "Local System" and the job is not allowed to write on the network share.
I wonder if it's possible to find a workaround to this, without changing the sql agent "Local System" logon user.
Thanks
March 22, 2012 at 9:45 am
Hmm, have you tried granting write access to the share to the computer account (ie MACHINENAME$ )
March 22, 2012 at 9:50 am
it is,
you will have to map the network share as a local drive using command prompt and net use with a user and password which has access then you can backup to the share.
i had the same problem and i think it was Lowell who provided the answer, trying to find the topic again for reference.
March 22, 2012 at 9:59 am
CMDExec step NET USE Z: \\UNCPath\d$ /user:domain\user password
T-SQL step BACKUP DATABASE TO DISK = 'Z:\database.bak'
CMDExec step NET USE Z: /DELETE
March 22, 2012 at 10:22 am
I was working on same problem when i joined with my new job.
If you have time constraint than take a backup to local machine and use XCOPY command put that script in SQL agent saves lot of time .
or you can do with maintenance plan and you can place backup copy directly on UNC path.
March 22, 2012 at 11:38 am
You certainly can back it up directly to a UNC path.. However you need to have a security context to get there and LocalSystem isn't going to give it to you..
I have to generally warn people not to use LocalSystem, my main issue is that it grants rights over the entire box to SQL and commands executed in xp_cmdshell can do literally anything to the box. I have gone to straight user accounts. Not even local admins..
I'm curious what the origin of the backup locally then copy remotely requirement is. It sounds like someone couldn't find a way to do a direct backup and codified their issue as a requirement..
I don't do local backups as a regular course, I do all my backups to a network server. If I have a choice I try to get it onto a different SAN than backs the machines. Some environments you are luck to have one SAN, others have more. I take this direction because it separates the backups from the servers in a meaningful way.
CEWII
March 22, 2012 at 11:47 am
I overcame a similar situation by mapping a Windows user ID to a credential, then created a proxy account pointing to that credential, and set the job to use the proxy account. Keep in mind that the Windows ID needs the appropriate access permissions to the network folder, and make sure the password is changed on the credential whenever the Windows ID password changes.
Hope this helps.
"El" Jerry.
March 22, 2012 at 11:48 am
Elliott Whitlow (3/22/2012)
You certainly can back it up directly to a UNC path.. However you need to have a security context to get there and LocalSystem isn't going to give it to you..I have to generally warn people not to use LocalSystem, my main issue is that it grants rights over the entire box to SQL and commands executed in xp_cmdshell can do literally anything to the box. I have gone to straight user accounts. Not even local admins..
I'm curious what the origin of the backup locally then copy remotely requirement is. It sounds like someone couldn't find a way to do a direct backup and codified their issue as a requirement..
I don't do local backups as a regular course, I do all my backups to a network server. If I have a choice I try to get it onto a different SAN than backs the machines. Some environments you are luck to have one SAN, others have more. I take this direction because it separates the backups from the servers in a meaningful way.
CEWII
The problem with doing backups across the network to a file server comes down to network issues. SQL backups do not handle network hiccups very well and a short delay writing to a network resource can result in a failed backup. Backing up locally then moving (or copying) the backup prevents network errors from causing a backup to fail.
This has been the source of numerous questions on the forums when network backups have failed. If you have a very stable network, then great. Several of the environments I have been in, our networks were always very stable. Doesn't help when you also have network services people coming in late at night an messing with the network because we aren't an official 24x7 shop even though there is activity 24x7 on the servers and from internet facing applications.
March 22, 2012 at 11:59 am
Lynn Pettis (3/22/2012)
The problem with doing backups across the network to a file server comes down to network issues. SQL backups do not handle network hiccups very well and a short delay writing to a network resource can result in a failed backup. Backing up locally then moving (or copying) the backup prevents network errors from causing a backup to fail.This has been the source of numerous questions on the forums when network backups have failed. If you have a very stable network, then great. Several of the environments I have been in, our networks were always very stable. Doesn't help when you also have network services people coming in late at night an messing with the network because we aren't an official 24x7 shop even though there is activity 24x7 on the servers and from internet facing applications.
Lynn,
I see your point, I have worked in places that took their networking very seriously, and NOC network work was scheduled and advertised. I've had very few issues in this regard, but yes, network hiccups can be troubling. Most of my backups complete in under 10 minutes. I have a couple longer, and one that runs about 12 hours.
The backup and copy methodology *may* be acceptable (IMNSHO) in environments where the network is less stable. I hope that even if the office space networking is sketchy that the NOC networking is solid.. We can hope..
CEWII
March 22, 2012 at 12:06 pm
Elliott Whitlow (3/22/2012)
Lynn Pettis (3/22/2012)
The problem with doing backups across the network to a file server comes down to network issues. SQL backups do not handle network hiccups very well and a short delay writing to a network resource can result in a failed backup. Backing up locally then moving (or copying) the backup prevents network errors from causing a backup to fail.This has been the source of numerous questions on the forums when network backups have failed. If you have a very stable network, then great. Several of the environments I have been in, our networks were always very stable. Doesn't help when you also have network services people coming in late at night an messing with the network because we aren't an official 24x7 shop even though there is activity 24x7 on the servers and from internet facing applications.
Lynn,
I see your point, I have worked in places that took their networking very seriously, and NOC network work was scheduled and advertised. I've had very few issues in this regard, but yes, network hiccups can be troubling. Most of my backups complete in under 10 minutes. I have a couple longer, and one that runs about 12 hours.
The backup and copy methodology *may* be acceptable (IMNSHO) in environments where the network is less stable. I hope that even if the office space networking is sketchy that the NOC networking is solid.. We can hope..
CEWII
Going back a few employers, not too far back time wise though, when I first got there full backups for the finance system ran almost an hour. HR and SIS (Student Information System) weren't too far behind at about 30 to 45 minutes.
When we got HyperBac those backups dropped to about 30 minutes.
When we moved to our blade servers and new SAN, the backups dropped to under 10 minutes with the native compression using SQL Server 2008 EE. We still backed up locally, then copied the backup files to a file share that was backed up to tape and to a file server located at a remote location. We kept two weeks of backups locally on the servers.
March 22, 2012 at 12:14 pm
Thanks guys.
My problem's origin is the lack of proper support in MsWin2008/MsSql2008 for an iSCSI tape device. HP StorageWorks 1000i Virtual Library System in my case.
Even HP support couldn't help me on this.
I'll try some solutions stated above and figure out which one's the best here.
Disk space for keeping all backups local is not enough so I'll back up databases sequencialy and the copy and delete local backup.
Thanks again.
May 30, 2018 at 4:40 am
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply