September 1, 2014 at 6:24 pm
I'm trying to create a maintenance plan to backup one of my databases however it's failing and I'm receiving the below erorr.
I am able to back up other databases without issue to the same location and I have sysadmin permissions.
Would anyone know what I should be checking that can cause this error?
Message
Executed as user: W2K8SQL\SYSTEM. ...ion 9.00.5000.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:13:53 AM Progress: 2014-09-02 10:13:54.01 Source: {A7AE97F0-9186-4385} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2014-09-02 10:13:54.35 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\W2K8SQLBAC".: 100% complete End Progress Error: 2014-09-02 10:13:54.39 Code: 0xC002F210 Source: Back Up Database (Full) Execute SQL Task Description: Executing the query "BACKUP DATABASE [wslogdb70_28] TO DISK = N'\\W2K8SQLBACKUP\SQLBackup\wslogdb70_28\wslogdb70_28_backup_201409021013.bak' WITH RETAINDAYS = 7, NOFORMAT, NOINIT, NAME = N'wslogdb70_28_backup_20140902101354', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "Cannot ... The package execution fa... The step failed.
September 2, 2014 at 1:53 am
Unfortunately the full error is truncated from the message.
But as a start the SQL engine account needs access to the share its writing to, to perform the backup.
Check that W2K8SQL\SYSTEM have write access to \\W2K8SQLBACKUP\SQLBackup\wslogdb70_28
If you could get the full error out we should be able to see whats going on. This link may help http://www.sqlservercentral.com/articles/SQL+Server+Agent/67726/
September 2, 2014 at 3:48 am
the actual share will be \\W2K8SQLBACKUP\SQLBackup and not \\W2K8SQLBACKUP\SQLBackup\wslogdb70_28.
Check the permissions at the share level and also the NTFS permissions too on the subfolder wslogdb70_28.
Check the account that sql agent is running under and ensure that the correct access is granted
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 2, 2014 at 7:58 pm
The SQLServer and SQLAgent login are both under login "local system" so I can't give those logins access to the backup server as far as I'm aware.
I'm able to backup my other databases to same location though so this shouldn't be the reason why or all my backups would fail.
September 2, 2014 at 11:58 pm
cstrati (9/2/2014)
The SQLServer and SQLAgent login are both under login "local system"
This violates all Windows and SQL server security Best Practice.
cstrati (9/2/2014)
so I can't give those logins access to the backup server as far as I'm aware.
You grant either the computer account or NT AUTHORITY\System permission to the share and NTFS ACLs 😉
So let's start by logging onto the remote computer and finding the folder defined as the share root. On the properties window go to the share tab and list the permissions there. Then go to the security tab and list the permissions there too.
cstrati (9/2/2014)
this shouldn't be the reason why or all my backups would fail.
Well, error 5 is access denied so clearly there is a security issue somewhere. What's different about the way the other backups are taken, using a different account maybe?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply