SQL 2000 Atach DB without log file - Error Could not open FCB for invalid file ID 13

Posted on 2013-05-30
Medium Priority
Last Modified: 2013-06-27
I have a sql 2000 DB. The server crashed.  I managed to recover the mdb and log file.
After sql reinstall I tried to reatach the files.  I could not get it to work.  I think the log file has issues.  I attempted to try to create a new blank DB shut down sql copy the mdb and delete the new log file.

I then tried the following

reconfigure with override
update sysdatabases set status = 32768 where name = 'WindMan'
sp_configure 'allow updates', 0
reconfigure with override

DBCC REBUILD_LOG(WindMan,'D:\VO_Database_Log\WindMan_Log.LDF')

I get the following error

Server: Msg 5180, Level 22 1, Line 1
Could not open FCB for invalid file ID 13 in database 'WindMan'

I have tried anything that I can think of...... if anyone has any ideas i would love the help
Thanks in advance experts
Question by:NCSA SCADA
  • 4
  • 2
  • 2
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39211314
File ID 13 isn't a log file unless the database had multiple log files.

I don't think you can ever rebuild a log from scratch on a database that has multiple log files: that process only works for a single log file.

If the db had only a single log file, then easiest would be to attach copies of the original .mdf files (not the .mdfs that you've already attempted attaches with), but without the log file: for example, sp_attach_db, specifying all .mdfs but no .ldf.
LVL 40

Expert Comment

ID: 39211892
Please have a look at:



You can try what is posted at:

Briefly here are the steps:
 1.Rename existing .mdf file to .mdf_old
 2.Create a new database with same .mdf and .ldf file as old one.
 3.Stop the sql server
 4.Rename .mdf and .ldf files of the new db to .mdf_old and .ldf_old
 5.Rename .mdf_old to .mdf
 6.Start sql server
 7.You should see db in suspect mode
 8.Change the database context to Master and allow updates to system tables
 9.Set the database in Emergency (bypass recovery) mode.
 10.Stop and restart SQL server.
11.Rebuild the log.
12.Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency.
 13.Turn off the updates to system tables
LVL 40

Accepted Solution

lcohan earned 2000 total points
ID: 39211905
Found my old scripts from SQL 2000 age (that I used quite a few times actually):

-- 1. If the db shows as suspect in EM and you can see it in sysdatabases
-- go to step 5.
-- 2. If the db does not show in EM but you have the .mdf file run following:
EXEC sp_attach_single_file_db @dbname = 'YourDBnameHere', @physname = 'D:\DataFiles\MSSQL\Data\YourDBnameHere_Data.mdf'
-- 3. If this does not create the .ldf (and it probably won't) file for you
-- Rename the .mdf file and create a brand new database the same way
-- the original one was created (the same name, same partitions)
-- Run following to be sure it got created.
select dbid, status, name from sysdatabases
-- stop SQL
-- 4. Delete the .mdf file you just created and rename back the original one.
-- Restart the sql server
-- The database should show up in EM manager as SUSPECT
-- 5. Now, put the db into EMERGENCY BYPASS STATUS (32768)
-- so you can rebuild the log
sp_configure 'allow', 1
reconfigure with override

update sysdatabases
set status = 32768 --16 --32768
where dbid=6 -- check this is the dbid of your db

-- 6. Stop sql server - rename log file (You will delete it later)

select dbid, status, name
from sysdatabases

-- 7. Start sql server. Rebuild the log.

dbcc rebuild_log ('databasename ', 'f:\mssql\log\logfilename.ldf')

-- 8. Put db back from EMERGENCY BYPASS STATUS (32768) to status 16
-- For more about status in sysdatabasys - see below.

update sysdatabases
set status = 16 --32768
where dbid=7 -- check this is the dbid of your db

-- 9. Clean up
sp_configure 'allow', 0
reconfigure with override
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

LVL 70

Expert Comment

by:Scott Pletcher
ID: 39211913
sp_attach_single_file_db works only if the db has a single file (hence the proc name :-) ).

However, since "File ID 13" was referenced in the original q, I figured it was extremely unlikely that the db was a single, primary file, so I referenced sp_attach_db instead.

Still, if the db does in fact have only a single file, attach_single_file is indeed the best method.

Author Comment

ID: 39212359
Thanks .. i am giving it a go

Author Comment

ID: 39216627
thanks experts.. still have not had a chance to give it a go... dead server over the weekend..
should have an update today

Author Comment

ID: 39228297
I gave it a go, but when i get to step 7 I get the same error "Could not open FCB for invalid file ID 0 in database 'Windman'      connection Broken

any ideas

Author Closing Comment

ID: 39281985
it worked great thanks

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question