db from .mdf and ldf files after db deleted
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Highlight
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Highlight
- Report Inappropriate Content
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
go
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
go
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
go
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
go
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Highlight
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Highlight
- Report Inappropriate Content
- 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'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Highlight
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Highlight
- Report Inappropriate Content
Thanks for the advice. Your link was very useful for me! Thank you!
