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

  • Hi all,

    Does anyone know why would SQL throwing a Severity 016 alert following a successful db restore, and what I could do to prevent it?

    I'm testing backup restores in a new development environment, and I'm seeing this error msg:


    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.

    The path \\BackupServerName\FolderName\SQLBackupFolder\ is the default backup location in my SQL server configuration and is on a separate server to my SQL one.

    The location I'm pulling the back up from is different to the default:

    \\BackupServerName\FolderName\SQLBackupFolder\USER\InstanceName\DatabaseName\FULL

    I tried changing the default location in the server config to the folder where the .bak is but it still threw the error.

    I'm new to db restore testing, so it could be I'm missing something obvious here! Should I even worry about these errors as the restore works?Any thoughts or ideas would be a great help!

    Cheers,

    Lins

  • What's the exact command that you're running for the restore?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for responding so quickly.

    I didn't run the restore using TSQL, I'd used the Restore Database GUI / wizard via right-clicking the Databases folder in Object Explorer.

    Your question and my answer makes me realize I'm probably not doing this in the best way from the start!

  • Use the GUI, script out the command instead of running it, cancel the gui.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Gail,

    Hope you had a cool weekend.

    I've now scripted out the restore command:

    USE [master]

    ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    RESTORE DATABASE [DatabaseName] FROM DISK = N'\\BackupServerName\FolderName\SQLBackupFolder\USER\InstanceName\DatabaseName\FULL\BackupFileName.bak' WITH FILE = 1, NOUNLOAD, STATS = 5

    ALTER DATABASE [DatabaseName] SET MULTI_USER

    GO


    How does this look? Way off point?

    Lins

  • This sounds like a checkpoint file was created for the database via an SSIS package. The restore is probably trying to restore a checkpoint file that is either corrupt or no longer valid.

    Did you check the backup location for the file in question?

    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.

  • Or check out this link. See if it helps.

    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.

  • It's harmless, according to this.  Are you actually using SQL Server 2012?

    John

  • Hi,

    Thanks for your support with this.

    Brandie, I just checked the default backup folder and there is no RestoreCheckpointDB7.CKP file there. Are these created automatically and then dropped when the restore completes?

    John, Yes definitely SQL Server 2012 (SP3) - I saw that article as well and was a bit confused that there was no reference to 2012 being affected by that bug (If that's what I'm encountering here.)

    The articles you've both linked to refer to the restore being interrupted at some point, so maybe that's something I should look into. I haven't seen any errors suggesting that... Do you know if there's a way to see if there was an interruption?

    cheers

    lins

  • Is there anything in the restorehistory table in msdb?  Maybe not, I suspect - it's not nearly as useful as the backup tables.

    John

  • Where have you been checking for errors?

    Verify both the SQL Server logs and the Event Viewer Application / System logs for the times in question.

    Are you trying to restore a past version of the primary database over the current primary database or are you trying to restore the primary database to another instance / server? If the later, try taking another backup of the primary. If the former, you might try a RESTORE HEADERONLY to see if the backup is intact or if it shows any errors. It's possible your backup has some corruption in it that is preventing a restore. Or, if you're restoring multiple files, that one of them is out of sequence (but you would have gotten a different error for that, so it's unlikely).

    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 - Monday, May 8, 2017 5:35 AM

    Where have you been checking for errors?

    Verify both the SQL Server logs and the Event Viewer Application / System logs for the times in question.

    Are you trying to restore a past version of the primary database over the current primary database or are you trying to restore the primary database to another instance / server? If the later, try taking another backup of the primary. If the former, you might try a RESTORE HEADERONLY to see if the backup is intact or if it shows any errors. It's possible your backup has some corruption in it that is preventing a restore. Or, if you're restoring multiple files, that one of them is out of sequence (but you would have gotten a different error for that, so it's unlikely).

    Hi,

    I just checked out msdb.RestoreHistory... the restart column is 0 for each of my restore tests I did.

    Also I'm trying to carry out the former in the two scenarios you give Brandie, so ran a RESTORE HEADERONLY and IsDamaged = 0, which I guess means it's okay(but this is new ground for me though so apologies if I'm missing something not looking in the right field etc!)

    I carried out another restore test just now to see what would go to the SQL Logs.

    Firstly in the log I can see the Backup job complete successfully last night - no problems there seemingly.

    To summarise the log entries for the Restore:

    • Database option SINGLE_USER is set to ON.
    • The error: 18272 Severity: 16 gets thrown .
    • Starting up database 'DatabaseName'.
    • The database 'DatabaseName' is marked RESTORING and is in a state that does not allow recovery to be run.
    • Starting up database 'DatabaseName'.
    • CHECKDB for database 'DatabaseName' finished without errors.
    • Restore is complete on database 'DatabaseName'.  The database is now available.

    So to me it looks like the Restore does the following:

    • Sets single User mode Okay
    • Seven seconds later errors with 18272.
    • One second later tries and fails to start the db.
    • In that same second tries again and succeeds in starting the db and completing the restore.

    So there IS an interruption with the 2x start attempts - but only after the error has already been sent. Any ideas?

    Cheers

    Lins

  • Try turning on the trace flag for verbose output during the restore and then check the log - that might give you more information on the error and what is happening:
    DBCC TRACEON(3004, 3605, -1)
    GO
    RESTORE DATABASE YourDatabase.....

    DBCC TRACEOFF(3004,3605, -1)

    Sue

  • Another thought is the error you reported:
     (operating system error 5(Access is denied.))

    This leads me to believe that the problem is that one of the below is true:
    1 - the SQL agent service doesn't have proper permissions to access that file
    2 - the file is locked by some other process
    3 - the SQL Agent service can't access the file (no permissions on the folder or server)

    Presuming you have xp_cmdshell access enabled, try running:
    EXEC xp_cmdshell 'dir \\BackupServer\Folder\SQLBackupFolder\RestoreCheckpointDB7.CKP'

    And see if you get any results.

    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 - Monday, May 8, 2017 8:34 AM

    Another thought is the error you reported:
     (operating system error 5(Access is denied.))

    This leads me to believe that the problem is that one of the below is true:
    1 - the SQL agent service doesn't have proper permissions to access that file
    2 - the file is locked by some other process
    3 - the SQL Agent service can't access the file (no permissions on the folder or server)

    Presuming you have xp_cmdshell access enabled, try running:
    EXEC xp_cmdshell 'dir \\BackupServer\Folder\SQLBackupFolder\RestoreCheckpointDB7.CKP'

    And see if you get any results.

    Hi bmg002

    I'm showing my ignorance here, but does SQL Agent carry out DB Restores by default then?
    I think you make a logical suggestion because of the Access Denied nature of the error, but I've been running these tests either through the SSMS object explorer wizard, or with TSQL. (I didn't think I was using the Agent here.)

    Cheers,

    Lins

Viewing 15 posts - 1 through 15 (of 28 total)

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