January 11, 2013 at 4:03 am
Dear experts
Can any one explain or show me a good article about Restore log WITH STANDBY for inspection.
And whats is the standby file and how to use it?
Thanks lot
January 11, 2013 at 4:06 am
Typically used in log shipping when you want the secondary to be readable (restore with norecovery is not) and still allow further logs to be restored.
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
January 11, 2013 at 4:13 am
Thanks for replying
what about "for inspection" usage, how to apply it, and what is the standby file, how to use it
Thanks lot
January 11, 2013 at 4:19 am
zi (1/11/2013)
Thanks for replyingwhat about "for inspection" usage
what do you mean by 'inspection usage'?
, how to apply it,
How to apply what?
and what is the standby file, how to use it
You don't use the standby file. SQL does.
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
January 11, 2013 at 4:30 am
Thanks for replying
My book shows that we use "with standby" in two ways, first one for log shipping, the second one for inspection"how" because the book is showing a little information.
What is the standby file and how SQL use it?
Thanks lot
January 11, 2013 at 5:23 am
I would guess what they mean is that if you're restoring log backups to get to just before some disaster (like a dropped table), you can restore with standby, query the database and repeat until you've identified the point in time that you want to restore to.
From Books Online:
The standby file is used to keep a "copy-on-write" pre-image for pages modified during the undo pass of a RESTORE WITH STANDBY. The standby file allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores. After a RESTORE WITH STANDBY operation, the undo file is automatically deleted by the next RESTORE operation. If this standby file is manually deleted before the next RESTORE operation, then the entire database must be re-restored. While the database is in the STANDBY state, you should treat this standby file with the same care as any other database file. Unlike other database files, this file is only kept open by the Database Engine during active restore operations.
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
January 11, 2013 at 5:30 am
zi (1/11/2013)
Thanks for replyingMy book shows that we use "with standby" in two ways, first one for log shipping, the second one for inspection"how" because the book is showing a little information.
Let's say you need to do a point-in-time restore and you want to inspect the database after each log restore because you do not know when some data was deleted from the database and you need to recover it. Well, you can restore each tran log WITH STANDBY, look in the database after the restore (which you cannot do WITH NORECOVERY) and then if the data is still there you can restore the next log WITH STANDBY and look again. The phrase "for inspection usage" is not a common industry phrase to my knowledge.
What is the standby file and how SQL use it?
You do not "use it" per se, the database engine does. You only specify where it lives as part of your RESTORE DATABASE command and SQL Server handles the rest. It is a temporary place where SQL Server can store undo information to bring a database into a consistent state after a log restore. This "undoing" of incomplete transactions stored in a particular log file is what enables SQL Server to make the database available to us in between log restores. When you restore the database initially the standby file is created. When the first log is restored any incomplete transactions that are restored as part of that log are undone in the database and the information needed to redo those transactions later is stored in the standby file. Then when the second log restore is done those undone transactions in the standby file are re-applied to the database, setting the stage for the restore process to begin with the second set of transaction log information being restored.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 11, 2013 at 7:31 am
Thank you very much, I got it mostly
If I want to stop restoring at a certain log file. what should I do to finish
what I mean is to change the database state from standby to online or normal state or recovery state?
Thank you very much
January 11, 2013 at 7:49 am
zi (1/11/2013)
Thank you very much, I got it mostlyIf I want to stop restoring at a certain log file. what should I do to finish
what I mean is to change the database state from standby to online or normal state or recovery state?
Thank you very much
Then you restore the last log file which you want with recovery. By doing this database will be usable as normal state.
SueTons.
Regards,
SQLisAwe5oMe.
January 11, 2013 at 7:53 am
zi (1/11/2013)
Thank you very much, I got it mostlyIf I want to stop restoring at a certain log file. what should I do to finish
what I mean is to change the database state from standby to online or normal state or recovery state?
RESTORE DATABASE <db name> WITH RECOVERY
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply