SQL 2000 restore problem

Question

SQL 2000 restore problem

terry ball

SSChasing Mays

Points: 635
More actions
September 14, 2006 at 10:21 am

#173942

I am doing SQL 2000 backups using the HP Data Protector software to a Tape Library. The backups work great and restores to the original database work also. However, when I do a restore to an alternate database name, which the software allows me to do, I get the following error.
Error has occurred while executing a SQL statement.
Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):ca2>
[Microsoft][ODBC SQL Server Driver][SQL Server]Logical file 'test_dat' is not part of database 'test'. Use RESTORE FILELISTONLY to list the logical file names.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is terminating abnormally.'

I have tried creating a blank database as well as deleting the test database so that the restore can create it anew. I have looked on HP's web site without any luck, and wanted to see of someone here has seen this problem. This looks like a SQL error and not a HP restore problem, but not sure. Any insight would be greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

Terry,

I assume you're restoring to the same instance where the original database exists. It looks like you're trying to give new logical names to the database files, which you should not do.

The restore statement should look like this:

RESTORE DATABASE test

FROM backupfile

WITH RECOVERY

MOVE 'original logical data file' TO 'file path\test_data.mdf',

MOVE 'original logical log file' TO 'file path\test_log.ldf'

The logical names stay the same but the physical file names are new. I hope this makes sense.

Greg

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

Like Greg said you cannot change the loagical name.

Here is how u should have done it.

Use Master

go

--sp_helpfile

RESTORE DATABASE NewDBName

FROM DISK = '\\Path\OldDB.bak'

WITH MOVE 'OldDB_Data' TO 'D:\SQLData\MSSQL\Data\NewDB\NewDB.mdf' ,

MOVE 'OldDB_Log' TO 'E:\SQLLogs\MSSQL\LOG\NewDB\NewDB_log.ldf'

go

terry ball SSChasing Mays Points: 635 More actions · Answer 3

Thanks, that did the trick. BTW, the actual SQL steps were being handled by Data Protector and I had no access to that SQL code. However the GUI did give me the options to specifythe logical name, file name and database name. Your answer was correct and I was able to do the restore completely.

As usual, you guys rock!

Thanks much for all the help!!!!!

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

The information about changing the 'logical name' of the file is partially correct. I state this because you cannot change it on the restore, however you can perform an ALTER on the restored database afterwards.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."