Restoring SQL Server databases from .mdf files

Question

Restoring SQL Server databases from .mdf files

Cedric Spence-296062

SSC Rookie

Points: 47
More actions
February 13, 2006 at 9:10 pm

#111479

I recently was given control of an SQL server that stop working.
We had no past backups of the database and the only file we had was the .mdf
I decided to install a fresh version of SQL 2000 on another server and created a database using enterprise manager with the same name as my database called SWATraining
I then stop the sql server
the first thing that I notice is that the .mdf that enterprise manager created had _DATA at the end of the name. Thus I renamed the the orginal .mdf SWATraining_Data and
copied it to the
C:\program files\microsoft sql server\mssql\data\
when I started SQL the database was greyed out and had (suspect)labeled
How can I recover the database when the only file I have is the .mdf file??

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

Trying to trick sql server does not always work, and you may create more problems down the track.

Since you only have the .mdb file, you can simply reattach that file to the new database that you created; try using the sp_attache_single_file_db system stored procedure. You can find more in BOL.

And there is no need to restart SQL!

Cedric Spence-296062 SSC Rookie Points: 47 More actions · Answer 2

I tried your suggestion

sp_attach_single_file_db @dbname = 'SWATraining',

@physname = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\SWATraining.mdf'

but reason this error

Server: Msg 5172, Level 16, State 15, Line 1

The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\SWATraining.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

Paul Mu SSCertifiable Points: 5547 More actions · Answer 3

Assuming that the .mdf file is a sql server database file, then this error would suggest that the database was not a 'clean' detached database and/or it is corrupted.

have a read of this thread from another site:

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/4a7b7b0a8534670e/d22591f55806fed4%23d22591f55806fed4?sa=X&oi=groupsr&start=1&num=3

It would appear that you may need professional help to recover the database.

Cedric Spence-296062 SSC Rookie Points: 47 More actions · Answer 4

thanks for your input... that server only had SQL 2000 loaded on it. their is a possiblity that it's a MSDE database but that should not make a difference...

Balmukund SSCertifiable Points: 6648 More actions · Answer 5

SQL Edition doesnot make a difference.

It looks to me that file header is corrupt

sp_attach_single_file_db will work if files are taken out cleanly and it was "graceful" shutdown of SQL Server.

From BOL:

Used sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.

SQLBill SSC Guru Points: 51440 More actions · Answer 6

SQL Server edition can make a difference. I have had to move a database and the new server had to have not only the same edition, but the same patch level.

-SQLBill

Rhys Crawford Grasshopper Points: 20 More actions · Answer 7

Hey,

This script worked great for me for 9 of my 10 DB, however this database returned the following error:

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

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

Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\HA_DB_Log.LDF' may be incorrect.

This is the exact Script:

sp_attach_single_file_db @dbname = 'HA_DB',

@physname = 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\HA_DB_DATA.mdf'

What does this mean?

It seems to mean "Cannot create database" But I dont understand. It worked fine up until this database, and there is not nor has there ever been a database with the same name on this server. Same situation as with all others, so whats the deal? Thanks for the help!