Lost TLOG drive. Help restoring using just mdf files. Trying sp_attach_single_file

Lost TLOG drive. Help restoring using just mdf files. Trying sp_attach_single_file_db...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

druncula - 02 Mar 2008 07:06 GMT

Morning everybody,

I'd appreciate if anybody could give any help with problems getting
databases back online when i just have the .mdf file

I lost the TLOG virtual drive yesterday (had a fatal problem with the
array) I have good .mdf files located on another virtual drive and
have recreated the TLOG device. The failure was yesterday (sat
morning) with the last good backup from thursday night.

I have a backup from Thursday night, however i would assume this would
overwrite my existing good mdf which has data that was commited during
friday, if restored.

I have has some success with the sp_attach_single_file_db procedure
however some databases will not detach due to not being shutdown
cleanly.

Is there any way to get the database that sp_attach_single_file_db to
detach cleanly?

Shall i just bite the bullet and restore from the last good and accept
the days data loss?

Many regards

John

Reply to this Message

druncula - 02 Mar 2008 07:11 GMT

Must add sql server 2000 on win2003

thanks

Reply to this Message

Uri Dimant - 02 Mar 2008 07:33 GMT

To detach database use sp_detach_db systenm stored procedure. If it does not
help(using sp_attach_single_file_db) restore the db from last good backup

> Morning everybody,
>
[quoted text clipped - 23 lines]
>
> John

Reply to this Message

druncula - 02 Mar 2008 08:04 GMT

Morning Uri,

This is what we have been doing against the suspect databases

exec sp_detach_db @dbname = 'TRAVEL'

exec sp_attach_single_file_db @dbname = 'TRAVEL',
@physname = N'E:\MSSQL\Data\TRAVEL_Data.MDF';

Some of the database this works, the others report unable to detach
due to database not shutdown cleanly.

Do you feel that i might as well ignore the above and just get on with
restoring from tape?

John

Reply to this Message

Uri Dimant - 02 Mar 2008 09:35 GMT

> Do you feel that i might as well ignore the above and just get on with
> restoring from tape?

Well ,it depends on you business. If it does not care about the data,
definenetly, you can restored from last backup. Also , search on internet
for any third part software to recover .MDF files from such cases.

> Morning Uri,
>
[quoted text clipped - 12 lines]
>
> John

Reply to this Message

Tibor Karaszi - 02 Mar 2008 11:34 GMT

The most likely reason for SQL Server not allowing you to detach those databases is because SQL
Server need to do recovery work for them, and for that the log file(s) are needed. So, even if you
find some hacks to get to the database without proper attach, you really should restore from a clean
backup for those databases. If you at all care about the consistency of you database, that is.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Morning Uri,
>
[quoted text clipped - 12 lines]
>
> John

Reply to this Message

druncula - 02 Mar 2008 20:05 GMT

Thanks Tibor,

I was able to get back 15 db's that were shutdown cleanly before
failure using sp_attach_single_file_db. The other 6 i restored from
last available backup.

Have learnt some healthy lessons past few days.

Regards

John

Reply to this Message