Friday, March 9, 2012

Restoring from unknown .mdf file

My company received a drive with SQL 2000? .mdf and .ldf files. I don't know if they were detached or just copied. I've been unable to reattach the files and get the error bellow when I try sp_attach_db.

Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\ocwp.mdf' may be incorrect.

Is there anyway to get this data back short of asking for a new backup?

Thanks,

Tim

The restore is attemping to put the files back in the same location they were located on the original server.

Use the [ WITH MOVE ] option for RESTORE. (From Books Online.)

RESTORE DATABASE { database_name }

FROM <backup_device>

WITH MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ]

|||

I was able to get this working by creating a new database with the same name, stopping the SQL instance, replacing the newly created .mdf and .ldf files with the ones with data and restarting the instance. Then I found out they were for 2005 and had to redue to process.

Thanks though.

Tim

No comments:

Post a Comment