Database files were deleted. How do I recover ?

[NathanGriffiths]

Someone has managed to delete the .mdf and .ldf files that contained a database on our system. I have managed to restore what appear to be copies of these files, but I am having problems recreating the database from them.

I've tried:

CREATE DATABASE MyDatabase
ON PRIMARY (FILENAME = 'c:\MyDatabase\MyDatabase_data.mdf')
FOR ATTACH
go

but this gives me an error message:

Server: Msg 906, Level 22, State 2, Line 1
Could not locate row in sysobjects for system catalog 'Sysobjects' in database 'MyDatabase'.
This system catalog should exist in all databases.
Run DBCC CHECKTABLE on sysobjects in this database.

Connection Broken



Obviously I can't run DBCC CHECKTABLE on the database until I've recovered it.
Does anyone have any other suggestions on how I can use the .mdf to recover a database?

thanks,

Nathan Griffiths

 

[aalmeida]

Can't you just restore a Backup? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[dbmsguy]

if you have the database files back, you should just have to do a sp_attach_db and you should be back in business.

Syntax
sp_attach_db [@dbname =] 'dbname',
[@filename1 =] 'filename_n' [,...16]

Arguments
[@dbname =] 'dbname'
Is the name of the database to be attached to the server. The name must be unique. dbname is sysname, with a default of NULL.
[@filename1 =] 'filename_n'
Is the physical name, including path, of a database file. filename_n is nvarchar(260), with a default of NULL. There can be up to 16 file names specified. The parameter names start at @filename1 and increment to @filename16. The file name list must include at least the primary file, which contains the system tables that point to other files in the database. The list must also include any files that were moved after the database was detached.
Paul

http://www.dbmsguy.com

 

[NathanGriffiths]

I haven't explained this very well...

I have been able to restore a backup of the database, but some major changes had been made to the database since the last backup was created and the database files were deleted before a new backup could be created.

The .mdf file I'm using appears to be more recent than the backup so I was hoping to be able to recover the database using that and so avoid having to make these changes all over again. I'm not usually involved with this database so I'm sort of working in the dark. The .mdf file I'm working with appears to be a copy of the live .mdf (which was not backed up and so I can't recover that) so this is what I'm trying to use to recover the database.

Any help greatly appreciated,

Nathan

 

[NathanGriffiths]

Paul,

Thanks for the advice, however I tried running sp_attch_db as well only to receive the same error as before. Do you think these files are corrupt somehow?

It seems the database files were not detached using sp_detach_db before they were deleted, which probably explains why sp_attach_db doesn't work.

thanks,

Nathan

 

[aalmeida]

Ohh!! Ok!
Let's say that your now 'Good Files' are called \\SavePlace\DbProd.mdf and \\SavePlace\DbProd.ldf.
On Enterprise Manager
1) Create a New Db and on the field for primary data file type d:\MSSQL7\DATA\DbProd.mdf and on the Primary Log File d:\MSSQL7\DATA\DbProd.ldf
2) Stop SQL Server
3) create a copy of: d:\MSSQL7\DATA\DbProd.mdf and d:\MSSQL7\DATA\DbProd.ldf to another path
4) Repalce d:\MSSQL7\DATA\DbProd.mdf with \\SavePlace\DbProd.mdf and d:\MSSQL7\DATA\DbProd.ldf with \\SavePlace\DbProd.ldf
5) Start SQL Server

Should Work, Let me know!
AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[NathanGriffiths]

AL Almeida,

Thanks for your help. Unfortunately, when I do as you suggest, the database appears as 'Suspect' in EM. This leads me to think that the files are probably corrupt in some way.

Looks like I'll just have to make those changes over again!

Thanks anyway,

Nathan

 

[aalmeida]

First verify that the attributes on the files are not set to read only, if that is the case change the attibutes. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"