Why would SQL throw a Severity 016 alert‌‌ following a successful db restore?

  • 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

  • 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

  • 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)
    🙁

  • lindsayscott23 - Tuesday, May 9, 2017 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)
    🙁

    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.

  • bmg002 - Tuesday, May 9, 2017 9:50 AM

    lindsayscott23 - Tuesday, May 9, 2017 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)
    🙁

    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.

    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

  • 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?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin - Wednesday, May 10, 2017 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?

    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?

  • 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?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • 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.

  • 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

  • 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

  • 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 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply