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.

I've got an MDF and LDF file from a dead server that I'm trying to attach to another instance of SQL Server. However running the following create database statement

USE [master]
GO
CREATE DATABASE [DBName] ON 
( FILENAME = N'C:\tmp\DBName.mdf' ),
( FILENAME = N'C:\tmp\DBName_log.ldf' )
 FOR ATTACH
GO

Generates this error.

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DBName'. CREATE DATABASE is aborted.
Msg 5125, Level 24, State 2, Line 1
File 'C:\tmp\DBName.mdf' appears to have been truncated by the operating system.  Expected size is 675840 KB but actual size is 47160 KB.

From reading the MSDN entry I cant see any options on the create database statement that would allow me to ignore this. Is there any other way to force a restoration ignoring the truncation to retrieve some of the data or is the mdf beyond recovery?

Failing that are there any utilities that will allow me to read the contents of the LDF File?

share|improve this question
2  
You're SOL. There is no way to guarantee any data in the file if there's less than 10% available. –  user3914 Nov 4 '10 at 16:45
    
Pretty exactly. Replace database with book and you get the idea. –  TomTom Nov 4 '10 at 20:21
    
I had a feeling that would be the case, also order of magnitude fail on my part as somehow I read 67MB vs 47MB and thought I had 70% of the data available. Had I realised at the time it was only 7% I'd have already written it off. –  Robb Nov 5 '10 at 9:21

1 Answer 1

up vote 1 down vote accepted

You can create an empty database with the same name and data file names, turn off the SQL service and replace the files with the files from the dead server. Once you bring the SQL service back online it will try to mount the files but more than likely it will come up as suspect and not be readable.

share|improve this answer
    
Thanks for this, gave it a shot and as you expected it came back up as suspect. Looks like this database is beyond recovery. –  Robb Nov 5 '10 at 9:40
    
That's too bad, it was worth a shot anyway. –  Jason Cumberland Nov 5 '10 at 14:37
    
@Robb this is to be expected when you replace the datafile. The checksum that SQL is expecting is not the same. You can put the db in emergency mode and run checkdb against it but it is a last resort. Read more on Paul Randal's blog here sqlskills.com/BLOGS/PAUL/post/… –  jgardner04 Nov 10 '10 at 19:59

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.