topic db from .mdf and ldf files after db deleted in Archive

db from .mdf and ldf files after db deleted

ArturKis — Sun, 26 Mar 2017 13:01:42 GMT

Someone completely deleted my SQL Server 7 database. I retrieved the .LDF and .MDF files from a network backup. Now I am trying to attach the database. I have tried this without creating an instance of the database by using the attach stored procedure to attache the .LDF and .MDF files I retrieved from the network backup. Also, I have tried it by creating an instance of the database and doing a detach of the newly created db .LDF and .MDF files and an attach of the .LDF and .MDF files I retrieved from the network backup. Neither of these approaches have worked.

Re: db from .mdf and ldf files after db deleted

ArturKis — Sun, 26 Mar 2017 13:02:11 GMT

Here is what I have tried in the Query Analyzer, but to no avail:

For the following example, I created a database instance called 'qarun_diamond_48_brett' with brand new .ldf and .mdf files and then tried to detach and then attach the .ldf and .mdf files from the network backup:

use master

sp_detach_db

'qarun_diamond_48_brett', 'F:\APPS\SQL 7.0\Database\Data\qarun_diamond_48_brett_log.ldf'

Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]

use master

sp_detach_db

'qarun_diamond_48_brett', 'F:\APPS\SQL 7.0\Database\Data\qarun_diamond_48_brett.mdf'

Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]

I tried the following attaches of the retrieved/recoverd .ldf and .mdf from the network to the newly created db instance. That didn't work, so I tried attaching to a db that had not yet been created.

use master

sp_attach_db

'qarun_diamond_48_brett2', 'F:\APPS\SQL 7.0\Database\Data\qarun_diamond_48_brett2_log.ldf'

Message: Server: Msg 1801, Level 16, State 3, Line 1

Database 'qarun_diamond_48_brett' already exists.

If I use a different db name I get the following error:

Server: Msg 5105, Level 16, State 13, Line 1

Device activation error. The physical file name 'qarun_diamond_48_brett' may be incorrect.

use master

sp_attach_db

'qarun_diamond_48_brett2', 'F:\APPS\SQL 7.0\Database\Data\qarun_diamond_48_brett2.mdf'

Message: Server: Msg 1801, Level 16, State 3, Line 1

Database 'qarun_diamond_48_brett' already exists.

I have also tried these statements wtih the EXEC sp_attach_db and EXEC sp_detach_db commands from within the Master db in Query Analyzer.

Any help would greatly be appreciated.

Re: db from .mdf and ldf files after db deleted

ArturKis — Tue, 28 Mar 2017 16:33:53 GMT

guys, have any thoughts?

Re: db from .mdf and ldf files after db deleted

kianbarry — Wed, 29 Mar 2017 11:14:02 GMT

First just try replacing the mdf and ldf files with the recovered files. You can stop SQL Server, and move the files in on the O/S level, then restart SQL Server. You might want to do a DBCC CheckDB after that. If this doesn't work...
Your Detaches above have syntax errors. To detach the files, use:

sp_detach_db <DatabaseName>

Your DB Name appears to be 'qarun_diamond_48_brett', so

exec sp_detach_db 'qarun_diamond_48_brett'

Your attach needs to specify both files in one command, MDF first. Thus, change it to:

sp_attach_db @DBName = N'qarun_diamond_48_brett2', @filename1 = N'F:\APPS\SQL 7.0\Database\Data\qarun_diamond_48_brett2.mdf',
@filename2 = N'F:\APPS\SQL 7.0\Database\Data\qarun_diamond_48_brett2_log.ldf'

Re: db from .mdf and ldf files after db deleted

kianbarry — Wed, 29 Mar 2017 11:16:58 GMT

In any case, if these suggestions don't work, you can try to repair .mdf file. You can try MDF Repair Kit. It's not free, but reliable.

http://www.mdf.repair/

Re: db from .mdf and ldf files after db deleted

ArturKis — Wed, 29 Mar 2017 18:11:11 GMT

Thanks for the advice. Your link was very useful for me! Thank you!