Backup/Restore

[bhosalenarayan]

Hi

i have a question regarding backup and restore.

I have taken a database backup from development server and I need to restore this on production. I ll give the actuals to make understanding a lot easier.

There is a database named test_db in development environment and I have backed it up and the filename is 'test_db.bak'.

There is a database named test_db already there in production and I need to restore the backup from development on this database, without dropping it.

Move command does not work(giving an error) and replace would also not work as the destination drives for data and log are different than development.

Is there a way???

[skhanal]

MOVE and REPLACE should work, you may have syntax error in your RESTORE command. Post the RESTORE command.

[bhosalenarayan]

I am sorry, I made a little mistake with the situation I gave. Actually backup taken was of different database(development) and it needs to be restore on to a different database production.

So backup was taken of test_db and it needs to be restore on test_db_prod. And I dont want to drop test_db_prod. The command I am using is:


RESTORE DATABASE TEST_db_prod FROM DISK='E:\New Folder (2)\TEST_db.BAK'
WITH
MOVE 'TEST_db' TO 'E:\New Folder (3)\TEST_db_prod.MDF',
MOVE 'TEST_db_LOG' TO 'E:\New Folder (3)\TEST_db_prod_LOG.LDF'

The error it gives is

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'TEST_db_prod' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.



And if I use

RESTORE DATABASE TEST_db_prod FROM DISK='E:\New Folder (2)\TEST_db.BAK'
WITH replace

it gives an error and tells about using move command. The error also says that

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\sqldata\TEST_db.MDF" failed with the operating system error 2(The system cannot find the file specified.)

and this path is available on developmemnt db, not on prod.

I hope u got what i am saying.

Thanks

[rmiao]

RESTORE DATABASE TEST_db_prod FROM DISK='E:\New Folder (2)\TEST_db.BAK'
WITH replace,
MOVE 'TEST_db' TO 'E:\New Folder (3)\TEST_db_prod.MDF',
MOVE 'TEST_db_LOG' TO 'E:\New Folder (3)\TEST_db_prod_LOG.LDF'


By the way, sql will overwrite test_db_prod if server doesn't use case sensitive collation.

[bhosalenarayan]

Thanks!!
it worked