Error 1813 or "WHY didn't they call me earlier?"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Error 1813 or "WHY didn't they call me earlier?"

Author

Topic

JakobP
Starting Member

1 Post

Posted - 2004-08-05 : 11:41:07

Hi

I am sitting as second row SQL Server support. Today I got a problem which might have been easier to solve if I was summoned earlier but alas...:

Case story:
A server was shutdown unexpected.
The SQL Server 7.0 database on this server appear after boot as suspected.
The administrators did not try to fix the suspect problem, but took the database offline and the detached the database from the server and tried to copy the ldf and mdf files to another location (The detach completed successful). Alas the ldf file was corrupted (because of disk error) so copy could not be made.

What I have tried:

I have (as they have tried too on both the (sick) 7.0 system and a new 2000 server) to attach the mdf to a new database (SQL Server 2000 because I haven't got a 7.0 present at my location) using:

EXECUTE sp_attach_single_file_db
@dbname = N'Niku Repository' ,
@physname = N'C:\MSSQL7\DATA\Niku_Repository_Data.MDF'

but get this error:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Niku Repository'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\MSSQL7\data\Niku Repository_Log.LDF' may be incorrect.

(Note: I have create the directory, and put the mdf file in this)

In my own stupidity, I thought that maybe, I could create a new database called the same (note I am on a SQL Server 2000) and use the new healthy ldf together with the (probably corrupted) mdf file in a sp_attach statement, but I get an error telling me that ldf file and mdf file does not come from the same database (surprise surprise).

I have asked the users to try the sp_attach_single_file_db on a healthy SQL Server 7.0 system. I have not yet heard the results of this.

I have search for some SQL tool (e.g. DBCC), that migth be able to work on an detached mdf database file to correct (?) internal errors in this. But I have only found repair/DBCC statements that works on running databases (and not on a solitary file in the filesystem).

Is there any hope for solving this problem?

Is it essential that the 'sp_attach_single_file_db' is run on a 7.0 (because the MDF file origin from this)?

Any ideas to what I can do to help these people?
(except teaching them how to do a backup)

Finally: Of course they don't have a recent and OK backup ;-(

Yours truly

Jakob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-05 : 14:16:12

You use sp_attach_single_file_db when you only want to attach the MDF file, which is what you want since the LDF is corrupt. When you run that sproc, the server will create an LDF file for you in the same directory as the MDF.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources