how to attach a db without a log? 1

[Tegie]

Hi,

is it possible to attach a database when a log file is missing?

My database had one .mdf file and 2 .ldfs. I lost one of the .ldfs, is it possible to recover any data?

Tegie

 

[JayKusch]

exec sp_attach_single_file_db @dbname = 'MyDB',
@Physname = 'C:\MyDB.MDF'

Thanks

J. Kusch

 

[Tegie]

Ops, I did so and:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'MyDB'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'F:\sqlserv\MSSQL\Data\MyDB_log.LDF' may be incorrect.
Device activation error. The physical file name 'F:\sqlserv\MSSQL\Data\MyDB2' may be incorrect.


Actually I dont have one of these log files any longer? What else could I try?

 

[JayKusch]

You need to change the MyDB to the name of the database you are trying to attach. look at the name of the MDF to get an idea as to what it is.

Thanks

J. Kusch

 

[Tegie]

thanks,
but i did change the name, the original prompt was:
>>
Could not open new database 'PRB_PIASKOWSKI'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'F:\sqlserv\MSSQL\Data\PRB_PIASKOWSKI_log.LDF' may be incorrect.
Device activation error. The physical file name 'F:\sqlserv\MSSQL\Data\log2' may be incorrect.
<<

The problem is, that the mdf file which is being attached is binded with the log files. I do not have the recent file called PRB_PIASKOWSKI_log.LDF which (assuming from the size) was the main log file...

Thanks!

 

[JayKusch]

Right after I sent that last thread I wondered if you had not relayed the error message you recieved w/ the alias MyDB - LOL ... Sorry about that!

An sp_attach_single_file_db will recreate the missing LDF and then attaches the DB. Now w/ multiple LDFs in play, I am not sure if that sp will work.

Sometimes databases will not reattach due to issues like not being properly detached and sometimes the Detach/Attach DB just plain out fails for no know reason.

Thanks

J. Kusch

 

[SQLBill]

Did your database have multiple log files?

-SQLBill

 

[JayKusch]

Hey Bill ...

Looks like they did have multiple LDFs ...

My database had one .mdf file and 2 .ldfs.

Thanks

J. Kusch

 

[SQLBill]

Thanks Jay, I was half-asleep. It is Friday after all.

This solved the problem for someone else, it might help you. DISCLAIMER: I HAVE NEVER USED THIS...IT COMES WITH NO GUARANTEES.

-SQLBill

from cchitanu on

http://www.sqlservercentral.com

This will work !!! Restoring databases when only data file available • shutdown sql • move the current database file or rename it • restart sql server • create a new database of the same name and log file and location as the old database and log file • get rid of the old database(you may be able to right click delete it in this situation or used sp_removedb) • create a new database of the right size and shape with correct log and data file locations • stop sql • rename the new databases.mdf or delete it if you don't have enough space - do not touch the .ldf • move back in the old database .mdf file or rename it back again • restart sql server(it should come up suspect) ------------------------------------------------------------------------------------------------------------------- • From a query window, set the status so that you can update the system tables by running the following query: use Master go sp_configure &quot;allow&quot;, 1 go reconfigure with override go • Then set the status of the DB that is giving you the problem (XXXXX) into Emergency Mode by running the following query: update sysdatabases set status = 32768 where name = '<DBName>' go checkpoint go shutdown with nowait go • Go into the data directory (MSSQL7\DATA) and rename the log file associated the DB in question (XXXX.ldf) to some temporary name, such as XXXX.TMP. • Exit the query window. • Start up SQL Server from a DOS command window by issuing: sqlservr -c -T3608 -T4022. • Bring up another query window and verify that the DB is in emergency mode by issuing: select Name, Status from Sysdatabases where name = '<DB_Name>' • Verify that the status is 32768. If it is, then issue the query: dbcc traceon(3604) dbcc rebuild_log('<DB_Name>','<log_filename>') <--- You will need the quotation marks REBUILD_LOG should take less than 5 minutes even on a very large database. It should complete with the message “DBCC execution completed” • Take the database out of bypass recovery mode by issuing the command : update sysdatabases set status = 0 where name = '<DBName>' • Exit the query window and then shutdown (Ctrl-C in the DOS window) and • restart SQL server. • Verify the status of the database by running DBCC NEWALLOC and DBCC CHECKDB on the database