Restore database from Query Analyzer 1

[JtheRipper]

Hi there,

We do SQL Server backups of our databases. I can do the restores using Enterprise Manager, but I want to create a sql script or stored procedure that can do the restore for me. The backup files are i.e. on e:\restored_files, and I want to restore the database with another name.

Can this be done?

Thanks,
J.

 

[jester777]

Yes this can be done. A database dump can be restored to a database with a different name. You need to be careful of the filenames of the restored database files so that you don't overwrite the original database files. You do this by using the WITH MOVE option.

EG
If you have backed up you database to a file called
e:\restored_files\db_BAK

do

RESTORE DATABASE FRED FROM DISK ='E:\RESTORED_FILES\DB_BAK'
WITH MOVE 'logical name 1' TO 'E:\physical name 1',
MOVE 'logical name 2' TO 'E:\physical name 2',
MOVE 'logical name 3' TO 'E:\physical name 3'
etc

The logical names are the internal names within SQL for the db files and make sure the physical names are different from the original names so you don't overwrite your other database files.

 

[JtheRipper]

Hi,

Thanks for the swift response!

Will this also work if I want to restore the database to another server under a different DB name? I am thinking of the logical names that it might not pick up, but maybe I am wrong.

thnx,
J.

 

[jester777]

Yes it will work. The logical names are stored as part of the database dump so the restore knows what the names should be.

 

[JtheRipper]

Works like a charm!

Thanks again! Have a star!