May 29, 2014 at 3:08 am
Hi,
Up until a couple of days ago all my maintenance plans were working.
Now they are all failing with the following errors -
Message
Executed as user: MHS2\sql2008. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:07:02 Progress: 2014-05-29 10:07:03.20 Source: {CE851AA2-045F-429A-885E-E60E75A38639} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2014-05-29 10:07:03.62 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\mhsmgmt01\..." failed with the following error: "xp_create_subdir() returned error 161, 'The specified path is invalid.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2014-05-29 10:07:03.70 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [master] TO DISK = N'\\mhsmgmt01\..." failed with the following error: "Cannot open backup device '\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\master\master_backup_2014_05_29_100703_5485799.bak'. Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2014-05-29 10:07:03.78 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [model] TO DISK = N'\\mhsmgmt01\S..." failed with the following error: "Cannot open backup device '\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\model\model_backup_2014_05_29_100703_5595810.bak'. Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2014-05-29 10:07:03.86 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [msdb] TO DISK = N'\\mhsmgmt01\SQ..." failed with the following error: "Cannot open backup device '\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\msdb\msdb_backup_2014_05_29_100703_5685819.bak'. Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Warning: 2014-05-29 10:07:03.86 Code: 0x80019002 Source: System Databases Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:07:02 Finished: 10:07:03 Elapsed: 1.638 seconds. The package execution failed. The step failed.
Now I have changed the SQL Server Agent service account to use a network account called "sql2008".Stopped and started the Agent service.
The account has access to the folders it is trying to back up to. It's a full admin on the sql box and the box where I am trying to backup to?
Totally out of ideas. Any ideas welcome please.
May 29, 2014 at 3:11 am
When I'm also logged on the management studio and I run the TSQL on the following (logged on as sql2008 user) and I run this code -
EXECUTE master.dbo.xp_create_subdir N'\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\master'
GO
EXECUTE master.dbo.xp_create_subdir N'\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\model'
GO
EXECUTE master.dbo.xp_create_subdir N'\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\msdb'
GO
BACKUP DATABASE [master] TO DISK = N'\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\master\master_backup_2014_05_29_101150_9343156.bak' WITH NOFORMAT, NOINIT, NAME = N'master_backup_2014_05_29_101150_9343156', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO DISK = N'\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\model\model_backup_2014_05_29_101150_9433165.bak' WITH NOFORMAT, NOINIT, NAME = N'model_backup_2014_05_29_101150_9433165', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO DISK = N'\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\msdb\msdb_backup_2014_05_29_101150_9623184.bak' WITH NOFORMAT, NOINIT, NAME = N'msdb_backup_2014_05_29_101150_9623184', SKIP, REWIND, NOUNLOAD, STATS = 10
I get the following error -
Msg 22048, Level 16, State 1, Line 0
xp_create_subdir() returned error 161, 'The specified path is invalid.'
Msg 22048, Level 16, State 1, Line 0
xp_create_subdir() returned error 161, 'The specified path is invalid.'
Msg 22048, Level 16, State 1, Line 0
xp_create_subdir() returned error 161, 'The specified path is invalid.'
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\master\master_backup_2014_05_29_101150_9343156.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\model\model_backup_2014_05_29_101150_9433165.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\mhsmgmt01\SQL BACKUPS\MHSVI-OHDBLIVE\LIVE\msdb\msdb_backup_2014_05_29_101150_9623184.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
May 29, 2014 at 3:15 am
Have you actually logged on as that account and physically tried creating folders and files under that path?
I can't think of anything at the SQL side that would cause that error to be thrown if it did actually have access...
May 29, 2014 at 3:18 am
Sorry, misread your post. It's the SQL Server service account that needs access, not the user you're connected as, or the Agent service account.
May 29, 2014 at 3:19 am
Ahhh I see, I thought it was just the agent that needed access. I will changed the SQL Server Service to also use this account (but will have to do this out of hours).
I'll update tomorrow no if that fixes my issue.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply