Attaching a DB with multiple LDF files missing

Question

Attaching a DB with multiple LDF files missing

dkschill

Hall of Fame

Points: 3375
More actions
May 4, 2012 at 1:43 pm

#255447

I am just testing out ways to do this, but have been unsuccessful.
I have tried the sp_attach_single_file_db and the CREATE DATABASE ON (FILENAME = '') FOR ATTACH ;
They both fail.
I have also tried the create a database of the same size and then use those log files and that also failed.
I am running 2008 R2 Standard.
Anything I could try would be much appreciated!
There are no backups to restore.
I am purely doing this to learn!
I created a database with two log files. Detached the database and then deleted the log files and tried to reattach. I knew this would break, but I would like to know if there is a way to attach the database.

Viewing 10 posts - 1 through 9 (of 9 total)

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

EL Jerry SSCertifiable Points: 7063 More actions · Answer 1

dkschill (5/4/2012)
I am just testing out ways to do this, but have been unsuccessful.
I have tried the sp_attach_single_file_db and the CREATE DATABASE ON (FILENAME = '') FOR ATTACH ;
They both fail.
I have also tried the create a database of the same size and then use those log files and that also failed.
I am running 2008 R2 Standard.
Anything I could try would be much appreciated!
There are no backups to restore.
I am purely doing this to learn!
I created a database with two log files. Detached the database and then deleted the log files and tried to reattach. I knew this would break, but I would like to know if there is a way to attach the database.

Not sure if the CREATE DATABASE ... FOR ATTACH_REBUILD_LOG option helps. See http://msdn.microsoft.com/en-us/library/ms176061.aspx

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

dkschill Hall of Fame Points: 3375 More actions · Answer 2

Worked beautifully! Thanks! Now I need to test if that works if I pull the drive out, and don't properly detach it.

Thanks for the help!

Just noticed that it restored it back with only one log file...which in all sincerity was going to be my next mission.

Thanks again!

dkschill Hall of Fame Points: 3375 More actions · Answer 3

This may be a ridiculous question, but would a full recovery database have more dependency on its log file? Making it more difficult to successfully restore the data files without log files?

EL Jerry SSCertifiable Points: 7063 More actions · Answer 4

dkschill (5/4/2012)
This may be a ridiculous question, but would a full recovery database have more dependency on its log file? Making it more difficult to successfully restore the data files without log files?

Yes it does. Full recovery logs a lot more information where it makes it possible to recover data up to a certain point in time. As far as I know, if you do not have the logs for a database in this recovery model you can only recover data up to the last full backup.

Cheers,

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

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

IIRC, to do this the database needs to be cleanly shut down. If your transaction log is totally lost due to a corrupt or crashed drive, I don't believe this will work.

dkschill Hall of Fame Points: 3375 More actions · Answer 6

This looks like the information I am trying to fully understand:

http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

I am going to work through their scenarios!

Thanks for the help!

dkschill Hall of Fame Points: 3375 More actions · Answer 7

I tested the following:

I created a full recovery database and ran this code:

use A

create table a (b int)

create table b (c varchar(64))

declare @i int = 0;

declare @j-2 int = 0;

while @i < 100

begin

begin transaction

insert into a select @i;

set @j-2 = 0;

while @j-2 < 1000

begin

insert into b select '12039kjsdlfj0912kjdfljsdfa'

set @j-2 += 1;

end

commit transaction

set @i += 1;

end

Just to create some transactions, and then detached the database and deleted the logs. Then reattached using:

CREATE DATABASE A ON (FILENAME = 'C:\SQL\DATA\A.mdf') FOR ATTACH_REBUILD_LOG

And all the data was still in place, but I am going to try to fail the drive and see what the result will be.

Thanks again!

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

dkschill (5/4/2012)
This may be a ridiculous question, but would a full recovery database have more dependency on its log file? Making it more difficult to successfully restore the data files without log files?

No, not at all.

Any time the log is rebuilt, the log chain is broken and a new full backup is required, but that's the only difference between recovering a full or simple recovery model, whether you need to restart the log chain afterwards.

Attach_rebuild_log will only work if the database was shut down cleanly before the ldf was deleted. The definition of 'clean shut down' does not depend on recovery model, it is solely about whether there were any uncommitted transactions or transactions committe but not hardened on disk when the ldf file disappeared.

If the database was not cleanly shut down before the log file was lost, it cannot be reattached without the ldf.

See - http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

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

dkschill Hall of Fame Points: 3375 More actions · Answer 9

Thanks! I read your blog and it answered all the questions I was going to attempt to answer through testing. I will probably try them anyways just to do some hands-on learning.

Thanks for the reply!