Retore to create a different DB

Question

Retore to create a different DB

kd11

Hall of Fame

Points: 3447
More actions
January 19, 2011 at 11:47 am

#99538

I can create a new DB with a different name using a full backup from another database correct?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Yup. You'll probably have to use the WITH MOVE syntax to create new files for the new DB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd11 Hall of Fame Points: 3447 More actions · Answer 2

I tried this way "with move", and it didn't like it:

RESTORE database vieval3x_testiis from disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\testA\testA_db_2011.BAK'

WITH MOVE 'testA_TestIIS_Data' to 'D:\Program Files\Microsoft SQL Server\MSSQL\data\testA_TestIIS_Data.MDF',

MOVE 'testA_TestIIS_Log' to 'D:\Program Files\Microsoft SQL Server\MSSQL\data\testA_TestIIS_Log.LDF',

REPLACE

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Define 'didn't like it'

I can't read your mind, I can't see your screen and I can't run the command to see what's wrong as I don't have (or want) your backup file.

If you're restoring to a new DB, you don't need REPLACE. REPLACE is for when you want to overwrite an existing database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd11 Hall of Fame Points: 3447 More actions · Answer 4

I applogize, it said:

Logical file 'testa_TestIIS_Data' is not part of database 'testa_testiis'. Use RESTORE FILELISTONLY to list the logical file names.

I did an sp_helpdb databasename, I can see that testa_testIIS_Data is the logical file.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

kd11 (1/19/2011)
Logical file 'testa_TestIIS_Data' is not part of database 'testa_testiis'. Use RESTORE FILELISTONLY to list the logical file names.

It didn't say use sp_help DB. Use RESTORE FILELISTONLY to list the file names in that backup. I suspect (but not sure) that you don't need the quotes around the logical file names

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kd11 Hall of Fame Points: 3447 More actions · Answer 6

There is light at the end-of the tunnel,

The database to be restored was named 'AABC'. Reissue the statement using the WITH REPLACE option to overwrite the 'vieval3x_testiis' database.

When I replace "with move" with "replace" it tells me that replace is not a recognized RESTORE option, (which is, in BOL) I'm on 2000- 8.00.2039.

kd11 Hall of Fame Points: 3447 More actions · Answer 7

kd11

Hall of Fame

Points: 3447

January 19, 2011 at 1:16 pm

#1275540

I've got it, thanks. Typing error.