Solved

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

Posted on 2013-05-30
8
980 Views
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

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

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
0
Comment
Question by:NCSA SCADA
  • 4
  • 2
  • 2
8 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
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.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39211892
Please have a look at:

http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

Or:

You can try what is posted at:
http://www.sqlteam.com/forums/topic.asp?TOPIC%5FID=78376

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
0
 
LVL 39

Accepted Solution

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

--RECOVERY WHEN A .LDF FILE IS DELETED
-- 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
go
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
go
reconfigure with override
go
0
 
LVL 69

Expert Comment

by:ScottPletcher
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.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:NCSA SCADA
ID: 39212359
Thanks .. i am giving it a go
0
 

Author Comment

by:NCSA SCADA
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
0
 

Author Comment

by:NCSA SCADA
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
0
 

Author Closing Comment

by:NCSA SCADA
ID: 39281985
it worked great thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now