Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error trying to backup Expand / Collapse
Author
Message
Posted Monday, September 1, 2014 6:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 4:26 PM
Points: 21, Visits: 58
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.
Post #1609415
Posted Tuesday, September 2, 2014 1:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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/




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1609482
Posted Tuesday, September 2, 2014 3:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 6,758, Visits: 14,407
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"
Post #1609519
Posted Tuesday, September 2, 2014 7:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 4:26 PM
Points: 21, Visits: 58
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.
Post #1609868
Posted Tuesday, September 2, 2014 11:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 6,758, Visits: 14,407
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"
Post #1609877
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse