Restore database page with SMSQL backup


IHAC who want to restore database page with SMSQL backup.

I told him it is not possible as it is VDI backup and not regular backup and they can't be accessed as simple as that.

Whatever, if only we can access the virtual device the WITH SNAPSHOT clause can't be used with RESTORE DATABASE PAGE (in SQL 2005, I don't know about SQL2008).

If only we want to use the database clone (which issue a RESTORE With RECOVERY) and do a SQL backup on this database, we generate a new recovery path and we can do no more a database page restore on another recovery path.

As a workaround I propose him to use snaprestore to have the same speed of restore even if the database is detached for a few seconds. But I know he wants to keep the database online.

Do you think on any solutions I haven't thinking of to restore a database page ?



Re: Restore database page with SMSQL backup

Here is a discussion that concludes there are no kind of possibilities to do this. It seems to be a traditional DBA recovering process that can only work with SQL backup (With COPY if we work with SMSQL).

Yes, so the answer is you can’t but you have other solution.

I believe they want to restore page because they only have to read the DBCC CheckDB to restore page that are identified as inconsistent. Whereas with the clone solution, you have to identify the missing/wrong data in all the tables to insert/replace from the clone (that was the solution I proposed to avoid detach and downtime), and you can have a mix of old and new data.

They have traditional backup solution with SQL before SMSQL and they have this process, we have technical solution for a workaround. The concern is that they want a process that can replaced in the same way the RESTORE DATABASE PAGE. There are also limit for page restore if there are full maintenance on databases and pages are reorganized. So I believe like you that using clone is a better way, even if it induces more work from DBA. I suggest the workarounds to the customer and I tell you if it’s accepted. But personally, if I were a DBA, I will prefer a Snaprestore even if there are few seconds downtime… and have a reliable, consistent database to work on.


From: Welch, Justin
Sent: Tuesday, August 17, 2010 6:54 PM

The real question to ask here is Why?  Why do they need to restore the page?  If they have missing data,  use the clone and then copy the data from the clone back into the primary database. (can be done without a detach)  If the page is corrupt, chances are good they are going to need to restore the entire database anyways.  Are they using page restores as some sort of process, or are they only worried about actual recoveries?

Justin Welch