question

ktsupport avatar image
ktsupport asked

If sql server is taken down while a backup with differentials is running, will a recovery be done on startup?

If sql server is taken down while a backup with differentials is running, will a recovery be done on startup?
sqlbackup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
If the backup is interrupted, the database will still come back online during the restart of the server. That assumes it was a clean shut down which allowed transactions to complete. If you just had a power outage or something along those lines, you could be looking a potential data corruption due to incomplete transactions. But the backup itself wouldn't be the cause of the issue. In fact, if you did a normal shut down, I'm pretty sure the backup would complete first. Are you facing a particular issue or a set of errors or are you just worried about it in general?
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
The DCM on the page isn't modified by the differential backup though (just had to read that to verify it). It is changed by the full backup process, so, potentially, if the were to go out during a full backup and it was in the middle of resetting all the DCMs to 0, that's one potential for causing corruption. I'm still unsure of what the question was going after.
2 Likes 2 ·
Cyborg avatar image Cyborg commented ·
Is there any write activity occur on Differential Change Map page while a differential backup? If the DCM page is corrupted because of improper shutdown there are chances that the Differential backups will fail, correct? Please correct me if i am wrong.
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
Thank you. :-)
0 Likes 0 ·
ktsupport avatar image
ktsupport answered
Thanks for your reply Grant. Here is what is happening. We have errors being thrown saying "Service Broker needs to access the master key in the database ''. Error code:26. The master key has to exist and the service master key encryption is required.". At one point, we were getting errors on the certificate and no data was being processed so we stopped everything and deleted/recreated the Database Master Key and certs. Data started processing again, but these errors continued to be thrown. Don't know why after 4 months the certificate had an issue but we got data flowing again. Now we want to get rid of the errors that are still happening. We stopped all of the processes and had a profiler trace running. It showed that it was a background process of the service broker throwing these errors. I have the SPID but not a lot to go on as to what process. It occurs every 10 minutes on the dot. This is a db that was detached and reattached and the Database Master Key and certs were recreated. I don't know if it has something to do with the Service Master Key. All of the research I have done says that if a recovery is done, then the certs/keys need to be recreated. There has been no recovery, however there are messages in the SQL log that say "recovery complete" so trying to figure out why cert/key was invalid and how to fix the continuing errors. Thanks!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
I'm not that well versed with Service Broker. Recovery is something that happens any time you attach a database, restore a database, or, restart an instance (I think there's a couple of more places where it occurs too, but you get the idea, failover, that sort of thing). So if you're seeing recovery in the log, then one of these things has occurred. If Service Broker is messed up by recovery (again, my knowledge here is pretty weak), then you guys must need to recreate the keys.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.