Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL server 2005 administration (restoring mdf file without ldf) Expand / Collapse
Author
Message
Posted Friday, February 8, 2008 11:14 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
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
Posted Friday, February 8, 2008 1:00 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607

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
Posted Friday, February 8, 2008 1:15 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
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
Posted Sunday, August 26, 2012 11:31 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:23 AM
Points: 585, Visits: 1,295
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
Naga.Rohitkumar
Post #1350200
Posted Monday, August 27, 2012 12:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 40,194, Visits: 36,599
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 »

Add to briefcase

Permissions Expand / Collapse