May 9, 2017 at 5:01 am
If you run your restore through SSMS, it'll run as the SQL Server service account. If you do it as part of a job step, it'll use the SQL Server Agent account (unless it's set up to use a proxy).
John
May 9, 2017 at 5:08 am
Ah that makes complete sense. I'll see what access the service account has and alter that if necessary.
Hopefully we've found a solution!
Cheers for your help everyone.
Lins
May 9, 2017 at 9:23 am
From checking, it sounds like the service account does have read/write permission on the backup folder unfortunately.
So that doesn't sound like the cause (despite the error message)
🙁
May 9, 2017 at 9:50 am
lindsayscott23 - Tuesday, May 9, 2017 9:23 AMFrom checking, it sounds like the service account does have read/write permission on the backup folder unfortunately.
So that doesn't sound like the cause (despite the error message)
🙁
You say "sounds like" it has permissions. Do you have a way to verify this?
Do you have the SQL Server Agent service account as a login on your database? If so, a modified version of the command I suggested above:EXECUTE AS LOGIN = <SQL Server Agent Service account>
GO
EXEC xp_cmdshell 'dir \\BackupServer\Folder\SQLBackupFolder\RestoreCheckpointDB7.CKP'
GO
REVERT
GO
That will ensure that the account has permissions. I know where I work I've seen times where the account should have permissions, but DNS is busted or one of the parent folders has an explicit deny which causes issues.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 10, 2017 at 2:08 am
bmg002 - Tuesday, May 9, 2017 9:50 AMlindsayscott23 - Tuesday, May 9, 2017 9:23 AMFrom checking, it sounds like the service account does have read/write permission on the backup folder unfortunately.
So that doesn't sound like the cause (despite the error message)
🙁You say "sounds like" it has permissions. Do you have a way to verify this?
Do you have the SQL Server Agent service account as a login on your database? If so, a modified version of the command I suggested above:
EXECUTE AS LOGIN = <SQL Server Agent Service account>
GO
EXEC xp_cmdshell 'dir \\BackupServer\Folder\SQLBackupFolder\RestoreCheckpointDB7.CKP'
GO
REVERT
GOThat will ensure that the account has permissions. I know where I work I've seen times where the account should have permissions, but DNS is busted or one of the parent folders has an explicit deny which causes issues.
Good Point - I hadn't actually checked to see myself. To verify it I ran your SQL using the SQL Server Service account login. The results set came back with the file path and 'file not found' (which makes sense if these ckp files only exist briefly during the restore). So the service account can at least access that location to look for the file.
(The result was the same using my login as well.)
So I think permissions are ok and the issue is *something* else.
Cheers
Lins
May 10, 2017 at 5:22 am
This is a hard one. All the possibilities I can think of that would cause a DB restore to fail would throw different messages.
How about putting a fake CKP file out in the directory (create an empty text file and rename it) and trying running the xp_cmdshell code again. What does it say this time?
You never did say if this was a primary database you were trying to restore a previous backup over or a database "copy" you were trying to restore with the primary DB backup (or if you did, I missed it). Can you please identify which it is?
May 10, 2017 at 5:52 am
Brandie Tarvin - Wednesday, May 10, 2017 5:22 AMThis is a hard one. All the possibilities I can think of that would cause a DB restore to fail would throw different messages.How about putting a fake CKP file out in the directory (create an empty text file and rename it) and trying running the xp_cmdshell code again. What does it say this time?
You never did say if this was a primary database you were trying to restore a previous backup over or a database "copy" you were trying to restore with the primary DB backup (or if you did, I missed it). Can you please identify which it is?
Hi Brandie,
Apologies I didn't confirm - It was the former; I'm trying to restore a previous backup over a primary db.
I tried your suggestion of creating a fake CKP file and reran the xp_cmdshell code again.
It worked! the service account was able to see the file and has enough access to be able to analyse its size:
10/05/2017 12:59 0 RestoreCheckPointTest.CKP
1 File(s) 0 bytes
0 Dir(s) 8,482,623,705,088 bytes free
Any ideas?
May 10, 2017 at 6:43 am
Is this a production database or non-production? If non-production, do you have another backup with which you can test to see if the same thing happens?
May 10, 2017 at 6:50 am
Non Production. It's part of a new development server that I've just built. Only I'm using it currently.
There's just a single user database on there at the moment, but I can create another to see if the error re-occurs yeah - Sounds like a good plan cheers!
Lins
May 10, 2017 at 9:47 am
I was doing a bit more digging into this and I have a potentially better thought on the error:
DESCRIPTION: During restore restart, an I/O error occurred on checkpoint file '\\BackupServer\Folder\SQLBackupFolder\RestoreCheckpointDB7.CKP' (operating system error 5(Access is denied.)). The statement is proceeding but cannot be restarted. Ensure that a valid storage location exists for the checkpoint file.
I had to read that a few times to get a good idea as to what the problem was. So what it is saying is that it cannot create the chekpoint file. So I am not surprised that it errored.
What happens if you run this:EXECUTE AS LOGIN = <SQL Server Agent Service account>
GO
EXEC xp_cmdshell 'ECHO hello world > \\BackupServer\Folder\SQLBackupFolder\RestoreCheckpointDB7.CKP'
GO
EXEC xp_cmdshell 'DEL \\BackupServer\Folder\SQLBackupFolder\RestoreCheckpointDB7.CKP'
GO
REVERT
GO
I believe that the error is saying it cannot write the checkpoint file not that it can't read it. When the backup starts it makes a restore checkpoint file so if the restore gets interrupted you can continue from where it stopped. This file is what allows that. Since it cannot create it (for some reason), it gives you that warning but continues anyways as it can still do the restore, you just have to restart from the beginning in the event something interrupts the restore.
What the above script does is creates the file with the contents "hello world" in it and then tries to delete it. If you get any errors running the above code, it may not be re-runnable from the same session until you run the REVERT command.
EDIT - copy paste screwed up the SQL Command text. Fixed this
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 10, 2017 at 10:13 am
The write is what I had thought as well. I believe the service account actually needs full control to that directory. Same as how it's set on the default backup folder. And the inheritance set from that folder on down. Pretty much set just like the backup folder.
Sue
May 15, 2017 at 9:07 am
Hi,
Thanks for your help on this last week bmg002 and Sue_H .
I just picked this work up again and ran your hello world script above bmg002. It worked fine: created and dropped the .ckp file no problem. So it seems that the SQL service account can do what's required via ssms.
I also tried restoring the .bak from a different server location. It worked first time. No error raised. So I'm taking from that that the restore process is working okay and the problem relates to the backup locationI looked at the security on the folder and I think (as you say Sue) I need to add my SQL service account to it and give it Full Control access.
Cheers for your help!
Lins
May 16, 2017 at 9:32 am
Hi,
Just to finish the tale...in the end the way I fixed this was to move the backup location to a different server and try again. It worked fine - no error.
Not really sure what the issue was with the previous location, but moving it seemed to fix it.
Thanks for everyone's help and support with this tricky puzzle.
Cheers
Lins
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply