Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Because more than one person was meddling with the database, we have lost our transaction log files and consequently lost the database. Luckily, we have the MDF file still perfectly intact, and we're not worried about the lost transactions, since they were only 'delete' transactions.

I did some research, and found that the sp_attach_single_file_db command is pretty much meant for this, and the sql server was shutdown before the file was removed. I used this command:

EXEC sp_attach_single_file_db @dbname = 'RecoveredDatabase',
@PhysName = 'E:\SQL Data\AerationBasinsTrend_data.mdf'

Where 'RecoveredDatabase' is my new database, and 'E:\SQL DATA\AerationBasinsTrend_Data.mdf' is the phyiscal .mdf file. I get an error when I run this. The server reports this:

Server: Msg 1813, Level 16, State 2, Line 1

Could not open new database 'RecoveredDatabase'. CREATE DATABASE is aborted.

Device activation error. The physical file name 'E:\SQL Data\AerationBasinsTrend_Log.LDF' may be incorrect.

Device activation error. The physical file name 'C:\SQL Backup\TransLog' may be incorrect.

As far as I know, this command should create a new ldf file if the old one is not present. What am I missing?

share|improve this question
    
So you have the mdf in a clean shutdown on the server, or you're restoring from a backup? I'm a little confused as to the exact procedure you are trying to accomplish. –  Holocryptic May 10 '11 at 23:23
    
Simply trying to recover the data stored in the MDF file. Both of the transaction logs were removed while the server was shutdown, and no backups had been taken recently. –  crazytreeboy May 10 '11 at 23:27

1 Answer 1

up vote 4 down vote accepted

You can give this a try:

  1. Create a new database with the same name and same MDF and LDF files

  2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

  3. Start SQL Server

  4. Now your database will be marked suspect

  5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
  1. Restart sql server. now the database will be in emergency mode

  2. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.

(replace the dbname and log file name based on your requirement)

  1. Execute sp_resetstatus

  2. Restart SQL server and see the database is online.

Source

share|improve this answer
    
This worked well, and left the database functioning in 'DBO USE ONLY'. Thank you! –  crazytreeboy May 11 '11 at 1:14
1  
You're welcome! Now make a backup! ;) –  Holocryptic May 11 '11 at 1:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.