Restore Issue

Question

Restore Issue

Paul Mu

SSCertifiable

Points: 5547
More actions
April 26, 2006 at 11:08 pm

#111929

I am having issues with a simple restore operation on one of my servers.
Everytime I do a restore, the following error is raised irrespective of what database backup I use:
Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 2 in database '<database name>'.
Connection Broken
Anyone with a clue as to what the problem is?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

tosscrosby Hall of Fame Points: 3140 More actions · Answer 1

This doesn't sound good but you could have corruption in the database. Check out. Google can provide other hits as well. Good Luck.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=190516

or

http://database.ittoolbox.com/groups/technical-functional/sql-server-l/449455#

Terry

Paul Mu SSCertifiable Points: 5547 More actions · Answer 2

Thanks tosscrosby.

I did read the article from ITtoolbox, though helpful, it did not resolve my problem.

The backup of the databases have been restored successfully on a different server but not on this particular one. It would appear that the issue lies with the system databases (master?)! I'll continue to work on it a bit further...

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Are the SQL Server editions (7, 2000, 2005) and versions (Service Pack levels) the same on both systems (where the backup came from and where the restore is occuring)?

Are you doing a 'new' restore or are you trying to do a 'chain' restore (ie. differential to a full or transaction logs to a full backup)? If you are trying to restore a 'chain' of backups, maybe you missed one?

-SQLBill

Paul Mu SSCertifiable Points: 5547 More actions · Answer 4

I simply perform a normal full backup of a database (doesn't matter which one) with the INIT command. Then created a new database, and perform the restore. The error is raised and the database is left in a LOADING state. Using sp_helpdb simply returns no records.

As I said before, the funny thing is that the restore is OK when performed on a different server, just not on this particular one! I have tried restarting Windows and SQL Server, but without much success. It would be good to know where one can start to troubleshoot the situation - googling hasn't turned up anything so far!

tosscrosby Hall of Fame Points: 3140 More actions · Answer 5

Paul, if you run sp_configure on both servers, is everything the same (or at least similar)?

Terry

Paul Mu SSCertifiable Points: 5547 More actions · Answer 6

Hi Terry,

No they are different in a number of config values; but I can't see anything obvious that would help me to resolve the problem!

As a work around to restoring a database, I simply create the database and import the schema for all objects from the database that was backed up.

Did you have anything in mind concerning sp_configure?

SQLBill SSC Guru Points: 51440 More actions · Answer 7

Aha! quote:The error is raised and the database is left in a LOADING state.

A database left in a loading state is 'usually' waiting for more files to be restored. It sounds like this file is being restored using the WITH NORECOVERY option.

When it is showing the LOADING status and you are done restoring files, run this: RESTORE DATABASE dbname WITH RECOVERY.

Your last file to be restored should include WITH RECOVERY.

-SQLBill

Paul Mu SSCertifiable Points: 5547 More actions · Answer 8

The strange thing is that the database was restored with the RECOVERY option. The database was left in a LOADING state due to the error that was raised...

Greg Charles SSC-Forever Points: 45403 More actions · Answer 9

Paul,

You said you created a new database then restored. Try restoring without creating the database first. See "How to restore a database with a new name" in BOL.

Greg

Paul Mu SSCertifiable Points: 5547 More actions · Answer 10

Greg,

Thanks for the tip (I didn't know you could do that!); unfortunately, the problem appears to be system related (what it is I am unsure!). The error after the restore is as follows:

Processed 1784 pages for database 'gaptest', file 'gapbuster_Data' on file 1.

Processed 1 pages for database 'gaptest', file 'gapbuster_Log' on file 1.

Server: Msg 5180, Level 22, State 1, Line 1

Could not open FCB for invalid file ID 2 in database 'gaptest'.

Connection Broken

As stated previously, the restore is successful on a different server.

Paul

SQLSolutions360 Old Hand Points: 327 More actions · Answer 11

How big is the db ?
How much time does is take to restore on Server B ?
Are you restoring the backup from a N/W shared location ? There could be delay between the N/W.
Are you trying to load it on a SAN ? with \\servername\sharedlocation\datafile.mdf etc.....

There is a simple solution :-

On Server B, Where you have successfully loaded the DB.
Run a DBCC CHECKDB against this DB.
Dettach the database by killing the connections.
Copy the files over to Server A.
Attach it to Server A and let me know if it works ?
If it does not, then there could be some issues with the Master on Server A or there could be corruption on some table, (which can be reparied by dbcc checktable or by dropping and adding the clustered index )

Thanks,

- Kunal Gandre

Kindest Regards,

Kunal Gandre
>
Snr. SQL DBA

Paul Mu SSCertifiable Points: 5547 More actions · Answer 12

Thanks for your input Kunal.

I am more leaning towards a corruption (of sorts) of the master database myself. Will need some downtime to check the master database, but here is what I did to exclude the database that was backed up from being a suspect:

use master

go

create database testA

on (name=testA_data,filename='E:\SQLData\testA_data.mdf',size=1)

log on (name=testA_log,filename='G:\SQLLog\testA_log.ldf',size=1)

go

backup database testA

to disk = 'E:\testA.bak'

go

restore database testB

from disk = 'E:\testA.bak'

with recovery,

move 'testA_data' to 'E:\SQLData\testB_data.mdf',

move 'testA_log' to 'G:\SQLLog\testB_log.ldf'

go

When the script is executed, the following error is raised:

Processed 80 pages for database 'testB', file 'testA_data' on file 1.

Processed 1 pages for database 'testB', file 'testA_log' on file 1.

Server: Msg 5180, Level 22, State 1, Line 1

Could not open FCB for invalid file ID 2 in database 'testB'.

Connection Broken