March 1, 2010 at 11:38 am
Hi Folks,
I create this sp trying to back up a database to network share but it fails, can someone look at it for me?
Thanks lots.
ALTER procedure [dbo].[spBackPAAtoNetwork]
AS
execute as login = 'bns\rxie'
EXEC master.dbo.sp_addumpdevice @devtype = N'disk',
@logicalname = N'rxie backup device',
@physicalname = N'\\userdata\RXie\Paa1.bak'
BACKUP DATABASE PAA
TO [rxie backup device]
WITH DESCRIPTION = N'Backing up the database to a network share drive',
NOFORMAT, INIT, NAME = N'Backup_Testing_Full_DB_BkUp_to_Network_Share',
SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
bns\rxie has full access to folder '\\userdata\RXie', but error message says access is denied:
Msg 3201, Level 16, State 1, Procedure spBackPAAtoNetwork, Line 9
Cannot open backup device 'rxie backup device(\\userdata\RXie\Paa1.bak)'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Procedure spBackPAAtoNetwork, Line 9
BACKUP DATABASE is terminating abnormally.
March 1, 2010 at 12:24 pm
What I have found, is that you must have SQL Server Service running under a non-human AD account, or create a non-human account on each of the two boxes. Then assign that account write permissions to the share. From there, your backup will be able to write to that directory.
Andrew SQLDBA
March 1, 2010 at 1:17 pm
thank you, then how do I find out what's the REAL account the SQL instance is running under? for example, abc\def instead of something like [Local System]? because if that's the case, I need to give access to that account in the share folder.
I can't change the start account of SQL instance, I have to take whatever it is now.
March 1, 2010 at 1:43 pm
First off if your sql agent services are running as [Local system] you may wish to revisit that decision. There are a variety of valid Security reasons why this is a bad idea. You can read about them here.
However if you go forward with that you need to look at the SQL Server PRoxy Accounts. You can read about what they are and how to set them up here and here.
Hopefully that will get you pointed in the right direction.
-Luke.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply