Restore SQL Backup to new database with same logical file name

I have a SQL 2000 database in production (TMS). I have a backup archive of this database from 2011 that I need to restore as a new database. I have to restore to the same server as it is SQL 2000 and we only have one SQL 2000 server.

When I go to restore the database I wanted to change the filename and logical name, to be sure that I don't break anything with the current database in production. I receive the following error message:

Logical file 'TMS_Archive2011_Log' is not part of database 'TMS_Archive2011'. Use RESTORE FILELISTONLY to list the logical file names.
RESTORE DATABASE is terminating abnormally.

How do I proceed with restoring this database, as a new database with a different name, to the same SQL Server, without breaking what's currently in production?

(Am I correct that I cannot restore this backup to a SQL 2008 server?)
KThraceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Use RESTORE FILELISTONLY to list the logical file names. RESTORE DATABASE is terminating abnormally.
You may facing a corrupted backup file here. If so there's nothing you can do.

(Am I correct that I cannot restore this backup to a SQL 2008 server?)
You can restore a SQL Server 2000 database into SQL Server 2008 or 2008R2. Only on SQL Server 2012 or 2014 you can't.
0
Scott PletcherSenior DBACommented:
You definitely can and want to change the physical file names.

However, you cannot change the logical file names during the restore.  In theory you might be able to change them after the restore, but there is absolutely no reason to try to do so. The logical names are completely arbitrary anyway, they just have to be unique within the same db.  But, if you really wanted to, you could use the same logical file names for every db on the server and SQL itself wouldn't care.

So, restore the existing db with existing logical file names to a new db name with the same logical file names but different physical names.  Then use the restored db however you need to.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KThraceAuthor Commented:
Questions answered adequately. I was able to get the database restored to a SQL 2008 server after a few attempts.
 
Useful information regarding the logical filenames.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.