May 5, 2017 at 7:01 am
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
May 5, 2017 at 7:07 am
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
May 5, 2017 at 7:21 am
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!
May 5, 2017 at 7:46 am
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
May 8, 2017 at 3:33 am
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
May 8, 2017 at 4:09 am
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?
May 8, 2017 at 4:11 am
Or check out this link. See if it helps.
May 8, 2017 at 5:27 am
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
May 8, 2017 at 5:34 am
Is there anything in the restorehistory table in msdb? Maybe not, I suspect - it's not nearly as useful as the backup tables.
John
May 8, 2017 at 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).
May 8, 2017 at 7:44 am
Brandie Tarvin - Monday, May 8, 2017 5:35 AMWhere 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:
So to me it looks like the Restore does the following:
So there IS an interruption with the 2x start attempts - but only after the error has already been sent. Any ideas?
Cheers
Lins
May 8, 2017 at 8:22 am
DBCC TRACEON(3004, 3605, -1)
GO
RESTORE DATABASE YourDatabase.....DBCC TRACEOFF(3004,3605, -1)
Sue
May 8, 2017 at 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.
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 9, 2017 at 4:55 am
bmg002 - Monday, May 8, 2017 8:34 AMAnother 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 27 total)
You must be logged in to reply to this topic. Login to reply