Database Restore Failing - SQL Server Forums

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)

Database Restore Failing

Author

Topic

Bertrandkis
Starting Member

8 Posts

Posted - 2004-10-14 : 05:04:56

Hi all, I am restoring a database from a backup file and everything goes well right until the end but instead of getting the confirmation message,I get this Error Message:
COULD NOT FIND ROW IN SYSINDEXES FOR DATABASE ID 8,OBJECT ID 1,INDEX ID 1.RUN DBCC CHECKTABLE ON SYSINDEXES
When I click OK, the restore database dialog box does not go away and give the OK and Cancel option.If I click OK, the restore process restarts, if I click cancel, the database is left in Loading State and no database object is restored. Is there a way to tell SQL server to ignore the message and complete the restore, so that I can fix the Index problem?

Kristen
Test

22859 Posts

Posted - 2004-10-14 : 06:47:02

Sounds like your backup might be broken ... any chance that it has got truncated or mucked-up along the way? (has it been transfered from somewhere else, which would probably increase the "risk", or was it created on the machine your are restoring on?)

You could try a RESTORE from Query Analyser ... but if the DB came from another server you'll have to get to grips with the intricacies of the Logical Names and Paths ...

The command is along the lines of:


USE master	-- (Can't sit in the database whilst its being restored!)
GO

RESTORE DATABASE NewDBName
	FROM DISK = 'D:\MSSQL\BACKUP\OriginalDBName.BAK'
	WITH
		REPLACE,	-- Overwrites any existing database, if one exists
		RECOVERY,	-- Set database to be ready to use
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'OriginalDBName_data' TO 'D:\MSSQL\DATA\NewDBName.mdf', 
	MOVE 'OriginalDBName_log' TO 'D:\MSSQL\DATA\NewDBName.ldf'
GO

The "MOVE" command uses the Logical Names of the Data/Log and you'll need to know what these are - which you can find out using:

RESTORE FILELISTONLY FROM DISK = 'D:\MSSQL\BACKUP\OriginalDBName.BAK'

You can also do a :

RESTORE VERIFYONLY FROM DISK = 'D:\MSSQL\BACKUP\OriginalDBName.BAK'

which will be bad news if it says the backup is bust, but won't tell you for definite that the backup file is OK

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-14 : 13:39:39

I would run DBCC CHECKDB on the source database to verify that the database is not corrupt.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-14 : 14:45:42

You can set the database into emergency mode which might allow you to access it from query analyser but you won't be able to fix anything.
see
http://www.nigelrivett.net/RecoverCorruptDatabase.html
object id 1 is sysobjects though so you probably can't do much.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bertrandkis
Starting Member

8 Posts

Posted - 2004-10-15 : 05:55:34

Thanks guys for your help.After many attemps, i gave up and concluded that the file was corrupt. I managed to do a clean restore though from an older file. We just have to deal with the data loss.

The call him Bertrandkis, the one, the Only.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources