RESTORE to a different server - 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

Transact-SQL (2000)

RESTORE to a different server

Author

Topic

bananafishbone
Starting Member

7 Posts

Posted - 2005-08-23 : 11:06:59

I'm trying to restore a database to a second test SQL server using the following......


RESTORE DATABASE Quality
FROM DISK = 'c:\backups\nptsvr02_full.bak'
WITH MOVE 'Quality_Data' TO 'c:\SQLData\quality.mdf',
MOVE 'Quality_Log' TO 'c:\SQLData\quality.ldf'

.....but I get the following error


Server MSg 3234, Level 16, State 2, Line 1 Logical 
file 'Quality_Data' is not part of the database 'Quality'.

I'm 100% sure the Logical file names are correct as I cut and paste them from the output of a RESTORE FILELISTONLY.

What else could be wrong?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-23 : 11:14:10

you can verify the logical filenames with the FILELISTONLY option. Try the following command and see if you are using the correct logical filenames.

RESTORE FILELISTONLY FROM DISK = 'c:\backups\nptsvr02_full.bak'

-ec

bananafishbone
Starting Member

7 Posts

Posted - 2005-08-23 : 11:16:26

The logical names are correct.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-23 : 11:23:50

I wonder if you already have these datafiles located on disk. You might try using the REPLACE argument in your RESTORE command. Normally, RESTOE won't overwrite an existing file on your system that it can't verify is part of your database. You will also need to use the REPLACE option if you are restoring from a backup where the database had a different name.

From BOL:

quote:
REPLACE

Specifies that SQL Server should create the specified database and its related files even if another database already exists with the same name. In such a case, the existing database is deleted. When the REPLACE option is not specified, a safety check occurs (which prevents overwriting a different database by accident). The safety check ensures that the RESTORE DATABASE statement will not restore the database to the current server if:

1. The database named in the RESTORE statement already exists on the current server, and

2. The database name is different from the database name recorded in the backup set.

REPLACE also allows RESTORE to overwrite an existing file which cannot be verified as belonging to the database being restored. Normally, RESTORE will refuse to overwrite pre-existing files.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources