DBCC Timewarp

Question

DBCC Timewarp

The Dixie Flatline

SSC Guru

Points: 53354
More actions
February 5, 2010 at 6:50 pm

#103899

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

Viewing 15 posts - 1 through 15 (of 158 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 1

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

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 SSC Guru Points: 281334 More actions · Answer 4

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[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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 SSC Guru Points: 107049 More actions · Answer 6

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

george sibbald SSC Guru Points: 104210 More actions · Answer 7

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 SSC Guru Points: 281334 More actions · Answer 8

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[/url]
Learn Extended Events

george sibbald SSC Guru Points: 104210 More actions · Answer 9

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.

---------------------------------------------------------------------

LutzM SSC Guru Points: 107049 More actions · Answer 10

CirquedeSQLeil (2/6/2010)
I typically use 13 and 111 in order to perform transcontinental 1TB database transfers to SQL Server VMs.

Would you confirm that setting parameter 11 is sufficient enough when copying data to a server no more than 1024 miles away? Seems like param 13 is used to activate an offset calculation for the space part of the spacetime co-ordinates.

Param 17 is used for taking movement of continents over time into account (required for crossing time gaps of more than 512 years, IIRC).

So, basically, the following rules will apply:

11 - parameter for transfer to a different geographic position

13 - to be used when target system is more than 1024 miles away (might be safe to turn it on starting at an estimated range of 800 or so). Not recommended to be used for short range transfer since it may have side effects on the time part leading to undesired results.

17 - as described above, having the same effect as 13 when used for short time difference

19 - seems like to be the switch to prevent data to be overwritten since timewarp could use the same physical disc range twice. I can only guess its only needed when parameter 11 is used. Not sure though...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 11

GilaMonster (2/6/2010)
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.

Many thanks, Gail. I did know that that the vector of motion had to be curvelinear and had to account for the two primary gravity wells at a minimum. But I'm not sure if my copy of the documentation is correct for this universe or not. Do you set 120 'ON' or 'OFF' in the event of a syzygy?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 12

The Dixie Flatline (2/5/2010)
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.

It was deprecated in 2k5 so it's only available in 2k8. 🙂

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

The Dixie Flatline (2/6/2010)
Do you set 120 'ON' or 'OFF' in the event of a syzygy?

Depends on the state of Schrodinger's Cat at the exact instant. I prefer using 'MAYBE'. It's not as efficient, as the quantum tangles have to be evaluated at steps of 2*PI*Plank's constant along the motion vector, but it does tend to fail less often.

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 14

Totally different question: We still have to support some old tired iron that was never upgraded with Penning Traps because management didn't want to spend the money. Thankfully, the new servers we are buying all have that feature as part of the standard configuration. My question is: Will the old parameters still function correctly for recovery from future backups (no more than 2-3 months ahead) made on the new servers? If not, what are we looking at in terms of conversion/rewriting?

Any good checklist of considerations would be greatly appreciated. Thanks.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills