SQL server 2005 administration (restoring mdf file without ldf)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » SQL server 2005 administration (restoring mdf...

SQL server 2005 administration (restoring mdf file without ldf)

Rate Topic

Display Mode

Topic Options

Author

Message

John Esraelo-498130

Posted Friday, February 08, 2008 11:14 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:10 PM
Points: 518, Visits: 920

Here is the situation:
> C drive containing the SQL server 2005 is gone
> D drive for data is available
> E drive for logs is GONE
> no backup of mdf nor the ldf file

current dondition:
> a new sql needs to be rebuilt
> the MDF files need to be restored

All the attach various commands are not the options because the DBs where shutdown ungracefully.
So, here is the Q.
How, to restore an MDF file on the new sql server without the original ldf file.
thx

BY THE WAY
I don't think there is such a command as DBCC REBUILD_LOG in 2005...just to make sure we are on the same page.

JohnE

Cheers,
John Esraelo

Post #453382

SanjayAttray

Posted Friday, February 08, 2008 1:00 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

sp_attach_single_file_db @dbname= 'dbname' ,@physname= 'physical_name'

but, make sure database got only one .mdf file or say datafile. This command doesnot work with multiple data files.

SQL DBA.

Post #453424

John Esraelo-498130

Posted Friday, February 08, 2008 1:15 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:10 PM
Points: 518, Visits: 920

Hi,
thank you for the quick reply.
I don't think this would work since:
> new sql server and master or msdb have not clue about the mdf
> the mdf was ungracefully shutdown without explicit "detach" information

RUN:
sp_attach_single_file_db @dbname = 'tool' , @physname = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Tool.mdf'

RESULT:
File activation failure. The physical file name "E:\Tool_log.ldf" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'tool'. CREATE DATABASE is aborted.

JohnE

Cheers,
John Esraelo

Post #453437

naga.rohitkumar

Posted Sunday, August 26, 2012 11:31 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 525, Visits: 1,001

1. What about restoring the old backup from tape or server will u tried this ? may be it v'l work !
2. Dont u have a dr for the server which shutdown ungracefully.! if u have any Dr(disaster recovery ) server u can restore from that ihope so.

Thanks & Regards
NAGA.ROHITKUMAR

Post #1350200

GilaMonster

Posted Monday, August 27, 2012 12:29 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 37,720, Visits: 29,973

Option 1: Restore from backup.
Option 2: Restore from backup.
Option 3: http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/ and then spend some time examining the DB for consistency errors (in data, not structure) and fixing them

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1350215

« Prev Topic | Next Topic »

Permissions