SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC Timewarp


DBCC Timewarp

Author
Message
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5526 Visits: 6900
Few people are aware of DBCC TIMEWARP, which despite being undocumented remains one of the better utilities to recover data which has not been not been backed up prior to being corrupted.

Even less well known are the 137 optional and undocumented parameters necessary to use DBCC Timewarp correctly.

This forum will serve as an information exchange for people with serious interest in this important topic.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7863 Visits: 9971
Dixie - those parameters are not optional, the 137 parameters are required parameters - and from what I recall the next 255 are optional parameters that can be used to modify how the 137 required parameters are interpreted.

But, that was last year (or is that next year?) - there may be a whole lot more this year, or maybe they added them last decade? I always get confused when things happen w00t

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87784 Visits: 41121
Actually, if you can restore the next hour's worth of backups now, you can get away with as few as 42 of the parameters.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5526 Visits: 6900
Jeff, is that new in 2008, or would it work for 2005 as well?

If memory serves, parameter one is PI to 8k decimal places.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32875 Visits: 18560
It's new in 2008 R2D2.

Until you upgrade to that version, you must provide the minimum of 137 parameters. It is also highly recommended to use some of the 255 optional parameters to ensure correct data is restored.

In R2D2, you are also permitted to restore from a planned future backup if you provide the correct parameters in an undocumented order. One catch though is that your server must be a DELL and you must type at 88 words per minute.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88745 Visits: 45284
The Dixie Flatline (2/5/2010)
Even less well known are the 137 optional and undocumented parameters necessary to use DBCC Timewarp correctly.


One thing to note is that you should be especially careful in setting the parameters that dictate the origin and designation's spacetime co-ordinates (7-14) and the vector of motion between the two (100-120). Get them wrong and it could result in a disruption of the spacetime continuum.

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


LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10499 Visits: 13559
GilaMonster (2/6/2010)

One thing to note is that you should be especially careful in setting the parameters that dictate the origin and designation's spacetime co-ordinates (7-14) and the vector of motion between the two (100-120). Get them wrong and it could result in a disruption of the spacetime continuum.


As long as the origin spacetime co-ordinates is correct and the designation's co-ordinates are valid and correspond to the vector of motion it still will work.
The bad part of it: the backup will be restored to the designated server which may differ from the server desired if the space part does not match the one of designation.

The biggest advantage I see when using the addtl. parameter (IIRC 11,13,17, and 19) is to use DBCC Timewarp for fast transfer of a database to a different server, especially helpful for large systems (>1TB).



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10502 Visits: 13687
Lutz, microsoft don't recommend it but with careful use of the additional parameters you mention plus 101 - 110 to recover corruption in system databases DBCC timewarp can be used to seamlessly move an entire instance to a new server.

I've never been able to get it to work for VMs though, I don't think their spacetime coordinates are stable enough.

---------------------------------------------------------------------
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32875 Visits: 18560
I typically use 13 and 111 in order to perform transcontinental 1TB database transfers to SQL Server VMs.


I am still working on integrating flag 138 to properly initialize the database on an Oracle server via a Toaster USB connection. I understand it should work with a neural network synapse and psychic transponder - but the synchronization is slightly out of phase.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10502 Visits: 13687
Thanks Jason, I'll give parm 111 a try.

thats why I love SSC, you can always learn something new and useful.

BTW, if copying to Oracle, you need -T666 in the startup parameters.

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search