Problems restoring backup MDF file

Stephen Miller

unread,

Jun 29, 2002, 12:09:36 AM6/29/02

to

Hi,

I had a server crash and I'm trying to restore a backed-up MDF file
(no LDF file is available).

I am using the following to attach the MDF file:

EXEC sp_attach_single_file_db @dbname = 'myDB', @physname =
'c:\myDB.mdf'
EXEC sp_detach_db @dbname = 'myDB'
EXEC sp_attach_db @dbname = N'test', @filename1 = N'c:\myDB.mdf',
@filename2 = N'c:\myDB.ldf'

However the following error is generated:

Server: Msg 5172, Level 16, State 15, Line 1
The header for the file 'c:\myDB.mdf' is not a valid database file
header. The FILE SIZE property is inncorrect

I've tried (and failed) with three separate backups, created using SQL
Server's database maintenance plans. These backups were auto saved
with a '.bak' extension and have been simply retrieved from CD, copied
to location and renamed as the original MDF file name. They were
approximately the same size as the original MDF file and I have
assumed would be adequate to recover from disaster. What am I doing
wrong?

- Stephen

Atrax _

unread,

Jun 29, 2002, 1:14:32 AM6/29/02

to

> They were
approximately the same size as the original MDF

by this you mean the WERE different sizes, right? approximately as in
almost, but not really?

function String.prototype.r(){// Javascript rot13 en/decipherment. run
me for Atrax's signature
var a='nopqrstuvwxyz';var b='abcdefghijklm';var j='/:.'+a+b;var
k='/:.'+b+a;var l='';
for(var
x=0;x<this.length;x++){l+=k.charAt(j.indexOf(this.charAt(x)));}return l;
} alert("uggc://jjj.ernqgurshpxvatznahny.pb.hx/".r()); // Atrax, MVP
2002.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Stephen Miller

unread,

Jun 29, 2002, 6:00:41 AM6/29/02

to

Atrax wrote:

> > They were approximately the same size as the original MDF
> by this you mean the WERE different sizes, right? approximately as in
> almost, but not really?

A backup is a static point in time, a database tend to grow or
contract as it is used, hence they are different. I don't quite know
what you're trying to say Atrax, but I doubt it adds any value.

Greg D. Moore (Strider)

unread,

Jun 29, 2002, 10:33:09 AM6/29/02

to

"Stephen Miller" <jsau...@hotmail.com> wrote in message
news:cdb404de.0206...@posting.google.com...

> Hi,
>
> I had a server crash and I'm trying to restore a backed-up MDF file
> (no LDF file is available).
>
> I am using the following to attach the MDF file:
>
> EXEC sp_attach_single_file_db @dbname = 'myDB', @physname =
> 'c:\myDB.mdf'
> EXEC sp_detach_db @dbname = 'myDB'
> EXEC sp_attach_db @dbname = N'test', @filename1 = N'c:\myDB.mdf',
> @filename2 = N'c:\myDB.ldf'
>
> However the following error is generated:
>
> Server: Msg 5172, Level 16, State 15, Line 1
> The header for the file 'c:\myDB.mdf' is not a valid database file
> header. The FILE SIZE property is inncorrect
>
> I've tried (and failed) with three separate backups, created using SQL
> Server's database maintenance plans. These backups were auto saved
> with a '.bak' extension and have been simply retrieved from CD, copied
> to location and renamed as the original MDF file name.

Umm, a backup is not an MDF file. That's why it has a different extension.

Look up the RESTORE database command in Books On-Line.

Atrax _

unread,

Jun 30, 2002, 1:39:29 AM6/30/02

to

> A backup is a static point in time, a database tend to grow or
> contract as it is used, hence they are different. I don't quite know
> what you're trying to say Atrax, but I doubt it adds any value.

I'm trying to establish if perhaps some header information or trailing
data was truncated off the file. I guess you missed that.

Stephen Miller

unread,

Jul 1, 2002, 11:18:17 AM7/1/02

to

"Greg D. Moore \(Strider\)" wrote:

> "Stephen Miller" <jsau...@hotmail.com> wrote:
> > I've tried (and failed) with three separate backups, created using SQL
> > Server's database maintenance plans. These backups were auto saved
> > with a '.bak' extension and have been simply retrieved from CD, copied
> > to location and renamed as the original MDF file name.
>
> Umm, a backup is not an MDF file. That's why it has a different extension.
>
> Look up the RESTORE database command in Books On-Line.

Thanks Greg, that worked a treat. For future reference, try:

-- This works!!!
RESTORE DATABASE myDB
FROM DISK = 'C:\Backup\db_20020512.bak'
WITH MOVE 'myData' TO 'C:\Production\Data.MDF',
MOVE 'myLog' TO 'C:\Production\Log.LDF'

- Stephen