Restore Db from transaction Logs

Question

Restore Db from transaction Logs

Viewing 12 posts - 16 through 26 (of 26 total)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

The OP seems to be posting hastily without taking the time to properly explain the problem or the attempted solutions. If it's important, perhaps he/she will try harder.

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

GilaMonster (5/5/2010)
You can't. CheckDB can only be run against an actual database, not a backup. I belive Paul R worked on something to allow CheckDB to run against a backup, but it never made it into the product. Will ask him for details.

And this was Paul's short reply (quoted and linked because I don't know how long twitter messages live for)

Paul Randal
Patent I have w/ MS. Invented it back in 2003. Hasn't been built, probably won't be 🙁 CHECKDB of backup without restoring.

http://twitter.com/PaulRandal/statuses/13452022648

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

mckinnj1 Mr or Mrs. 500 Points: 548 More actions · Answer 3

Lynn Pettis (5/5/2010)
Didn't know you could run DBCC CHECK DB against a .bak file.
Were you able to restore the database itself as under a differnet name using the .bak file?

I restored the file to a new db w/out the ldf and it was still corrupt. Hence, calling in the specialists.

mckinnj1 Mr or Mrs. 500 Points: 548 More actions · Answer 4

homebrew01 (5/5/2010)
The OP seems to be posting hastily without taking the time to properly explain the problem or the attempted solutions. If it's important, perhaps he/she will try harder.

Wow. Trollers in an SQL forum. You would think this is last place they are needed.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

GilaMonster (5/6/2010)
GilaMonster (5/5/2010)
You can't. CheckDB can only be run against an actual database, not a backup. I belive Paul R worked on something to allow CheckDB to run against a backup, but it never made it into the product. Will ask him for details.
And this was Paul's short reply (quoted and linked because I don't know how long twitter messages live for)
Paul Randal
Patent I have w/ MS. Invented it back in 2003. Hasn't been built, probably won't be 🙁 CHECKDB of backup without restoring.
http://twitter.com/PaulRandal/statuses/13452022648

I just don't understand how you ran a restore "without the the ldf." Can you post a script?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

mckinnj1 (5/6/2010)
homebrew01 (5/5/2010)
The OP seems to be posting hastily without taking the time to properly explain the problem or the attempted solutions. If it's important, perhaps he/she will try harder.
Wow. Trollers in an SQL forum. You would think this is last place they are needed.

Oh, that's not a troll. You'll see if you hang around for a while. We occasionally get some serious trolls. Just like in all other circumstances, avoid feeding them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mckinnj1 Mr or Mrs. 500 Points: 548 More actions · Answer 7

Going forward, I will try to be more detailed, but yesterday we had many things going on at once. I appreciate everyone's assistance in the matter who gave an effort to help.

We were able to get a partial db restore, but have not heard the details as the guy who was primarily working on it was up here until midnight. When he comes in later, I'll find out what fix was used.

Thanks!

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

mckinnj1 (5/6/2010)
Lynn Pettis (5/5/2010)
Didn't know you could run DBCC CHECK DB against a .bak file.
Were you able to restore the database itself as under a differnet name using the .bak file?
I restored the file to a new db w/out the ldf and it was still corrupt. Hence, calling in the specialists.

It's not possible to restore a database without an ldf. Can you please (as I've requested already) explain step by step exactly what you did.

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

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

mckinnj1 (5/6/2010)
homebrew01 (5/5/2010)
The OP seems to be posting hastily without taking the time to properly explain the problem or the attempted solutions. If it's important, perhaps he/she will try harder.
Wow. Trollers in an SQL forum. You would think this is last place they are needed.

Noone's trolling here, however you're claiming to have done things that are impossible. Hence we need a more detailed explanation of precisely what it is that you have done. It's near-impossible to help when all we've got are vague descriptions of what was done, especially when those things are impossible.

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

mckinnj1 Mr or Mrs. 500 Points: 548 More actions · Answer 10

GilaMonster (5/6/2010)
mckinnj1 (5/6/2010)
homebrew01 (5/5/2010)
The OP seems to be posting hastily without taking the time to properly explain the problem or the attempted solutions. If it's important, perhaps he/she will try harder.
Wow. Trollers in an SQL forum. You would think this is last place they are needed.
Noone's trolling here, however you're claiming to have done things that are impossible. Hence we need a more detailed explanation of precisely what it is that you have done. It's near-impossible to help when all we've got are vague descriptions of what was done, especially when those things are impossible.

As previously re-explained, I restored the the BAK to a new db without the updated ldf file. This created a new ldf file.

I then ran DBCC CHECKDB against the new database with the brand new ldf file. It came back with the same I/O error message as previously received by running the DBCC CHECKDB against the db with the updated ldf file.

As to the confusion of yesterday, literally as soon as I would post something, someone wanted to try something new and thus create another level of complexity to the issue. It was NOT a fun day. So, I will say this ONE LAST TIME... I apologize for not being clear and going forward I will try to be as detailed as possible when posting questions.

Again, I have not heard what the solution was to getting a partial restore was, but I will attempt to get a detailed account for all of your inquiring minds.

Thanks!

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

mckinnj1 (5/6/2010)
As previously re-explained, I restored the the BAK to a new db without the updated ldf file. This created a new ldf file.

So a normal database restore. The 'updated ldf' is useless by itself, only with its original mdf file can you take log backups (and hence recover those transactions) and even that requires full recovery model and no log truncations.

You cannot mix and match ldf and mdf files. The ldf and mdf both record latest LSN (log sequence number) and if those don't match, SQL will refuse to open the database (suspect or fail to attach)

I then ran DBCC CHECKDB against the new database with the brand new ldf file. It came back with the same I/O error message as previously received by running the DBCC CHECKDB against the db with the updated ldf file.

That means the backup you used to restore from contains the corruption. You can repair or try to go further back to find a clean backup, but you're looking at data loss either way, as I very much doubt that you'll be able to recover transactions from the original database

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

mckinnj1 (5/6/2010)
Lynn Pettis (5/5/2010)
Didn't know you could run DBCC CHECK DB against a .bak file.
Were you able to restore the database itself as under a differnet name using the .bak file?
I restored the file to a new db w/out the ldf and it was still corrupt. Hence, calling in the specialists.

How are you restoring the database without the ldf, they should restore as a pair from the backup file.