March 14, 2018 at 5:22 am
A strange title I know but that’s what I’ve come across today.
We have a developer who is backing his database up every morning using an SQL Job.
The Job consists of two steps.
1. Backup Log
2. Backup Database
Backup Log runs this command:
BACKUP LOG MyDatabaseName TO DISK = 'E:\Backup\Database\MyDatabase_LOG.bak' WITH INIT
Whilst Backup Database runs this command
DECLARE @BackupPath nvarchar(255)
SET @BackupPath= 'E:\Backup\Database\MyDatabaseName_'+DATENAME(dw,GetDate() )+'.bak'
BACKUP DATABASE MyDatabaseName TO DISK = @BackupPath WITH INIT
This morning the developer needed his backup only to see his job had been failing every day on Step 2 EXCEPT Thursdays.
The error raised is:
Cannot open backup device 'E:\Backup\Database\MyDatabaseName_Wednesday.bak'. Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
But I couldn’t understand why Step 1 ran successfully and why it runs OK on a Thursday.
So I’ve played in a query window with the Backup statement.
This fails with the same error reported by the SQL Job:
BACKUP DATABASE MyDatabaseName TO DISK = 'E:\Backup\Database\MyDatabaseName_Wednesday.bak'
This is successful:
BACKUP DATABASE MyDatabaseName TO DISK = 'E:\Backup\Database\MyDatabaseName_Thursday.bak'
What is bizarre is Monday, Tuesday, and Friday all fail but if I try the following it works!
BACKUP DATABASE MyDatabaseName TO DISK = 'E:\Backup\Database\MyDatabaseName_WigglyPig.bak'
As does:
BACKUP DATABASE MyDatabaseName TO DISK = 'E:\Backup\Database\WigglyPig_Wednesday.bak'
If I treat the error independently and granted Write access to the folder to the SQL Agent it did work but I can’t explain why Thursday and WigglyPig worked but other days did not.
Can anyone suggest what might have caused this anomaly as I'd love to be able to explain it?
Thanks
Giles
March 14, 2018 at 5:44 am
Sounds like a file permissions problem. Check that the SQL Server service account has the appropriate consistent permissions across all those files.
Thomas Rushton
blog: https://thelonedba.wordpress.com
March 15, 2018 at 3:57 am
Thanks Thomas, that sounds consistant with it working now the SQL Agent account has the relevant permission to the folder.
Of course the proof will be tomorrow when it attempts to do Fridays backup!
March 15, 2018 at 5:09 am
giles.clapham - Thursday, March 15, 2018 3:57 AMThanks Thomas, that sounds consistant with it working now the SQL Agent account has the relevant permission to the folder.
Of course the proof will be tomorrow when it attempts to do Fridays backup!
I'm sure you know what our follow-up question will be!
If it's permissions related, why does it sometimes work?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 15, 2018 at 6:00 am
In my tests the Friday... etc backups failed before I granted the Write permission to the folder for the account that SQL Server Agent was running under.
After I granted the Write permission my tests worked for all of the backups.
I'd certainly agree that it is very strange that one file would work whilst the others didn't but the Error raised and the fact that addressing the permissions indicates it was indeed a permissions issue.
But as I mentioned earlier, the proof will be tomorrow and the Friday backup.
March 19, 2018 at 4:22 am
Fridays backup failed with the same error as before.
I've checked the Header information of the backup files for Thursday and Friday and they're all the same Database and Server version.
I deleted the Friday backup and ran it again and it worked fine, so NEW files can be created and once created those New files can be overwritten.
So that prompted me to look at the backup files themselves, the older files have the owner “Administrators”, the Thursday file has the SQL Server account as the owner.
I can't find how to check if the SQL Server Service account was changed on or around the 16th November 2017, the Event Log only goes back to 24th November 2017 (which looks a bit suspicious too)
SQL Server was restarted at the beginning of February (taken by the creation date of TempDB) so I can’t tell if the account SQL Server uses was changed mid November but I’m thinking that must have been the case.
Anyway I've changed the permission on the folder from allowing the Agent to write to allow the SQL Server Engines account (I misread Thomas' reply) to have Write access and backups ran OK all weekend and Monday morning too.
So problem resolved.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply