December 14, 2006 at 6:12 am
Hi,
I want to replace a DB called DB_ACC with the data from a similar DB (same data model) named DB_PROD and coming from a different server. For this, I have a a full backup of DB_PROD.
I've tricked SQL manager by generating a backup of DB_ACC and then by replacing it with the backup file of DB_PROD. And then I tried a restore of DB_ACC with this backup of DB_PROD.
This procedure worked in the past but when the 2 DBs had the same name (although from 2 different servers). But now with the different names I get the following errors:
---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Device activation error. The physical file name 'e:\sqldata\MSSQL$APPLICATION\data\DB_PROD.mdf' may be incorrect.
File 'DB_PROD_DAT' cannot be restored to 'e:\sqldata\MSSQL$APPLICATION\data\DB_PROD.mdf'. Use WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name 'e:\sqldata\MSSQL$APPLICATION\data\DB_PROD_log.ldf' may be incorrect.
File 'DB_PROD_LOG' cannot be restored to 'e:\sqldata\MSSQL$APPLICATION\data\DB_PROD_log.ldf'. Use WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.
---------------------------
OK
---------------------------
I'm new to SQL Server, so this procedure might seem very silly to you, but I would be very grateful if you could explain me how to restore DB_ACC with a backup of DB_PROD, without renaming DB_ACC of course. And if this is not possible, could you explain me how to proceed to replace DB_ACC with the data of DB_PROD, maybe with something else than backups?
Thx a lot in advance
December 14, 2006 at 6:39 am
David
The clue is in the error message. The database you are restoring from has different file names and/or paths from the one you are restoring to. So you will need to add something like this to your restore statement for each file in the database:
WITH 'MOVE DB_PROD_LOG' TO 'e:\sqldata\MSSQL$APPLICATION\data\DB_ACC.mdf'
The full syntax for the RESTORE statement is in Books Online. If you are using Enterprise Manager you can change the file names/paths in the GUI instead.
John
December 14, 2006 at 9:10 am
David,
I believe the RESTORE option you need to use is WITH REPLACE. This will delete the existing database and restore the backup with the same name as the existing database. See "RESTORE (described)" in BooksOnLine.
If you only want to replace the data in DB_ACC with data from DB_PROD, and the table structures are the same, you can use DTS. Start the Import/Export Wizard in Enterprise Manager and select "Copy the table(s) and view(s) from the source database". You wouldn't have to mess with copying backup files between servers.
Greg
Greg
December 14, 2006 at 9:44 am
Thank you John and Greg.
I am indeed using Enterprise Manager but I don't see where I can change/move DB_PROD names to DB_ACC.
The only place where I can change file names is in "Restore as" > "Options" > "Restore as" column where I could change *_DAT.MDF and *_LOG.LDF file names ; but both "Original File Names" column and "Restore as" column have "*DB_ACC*" file names so that doesn't help to change from DB_PROD to DB_ACC.
As for the WITH REPLACE option, I don't see how to set it in Enterprise Manager.
Thank you for the DTS tip, I will look into this but I would still try to use the DB_PROD I have now, because I don't have access to DB_PROD and it would take many days before I could get something else from it.
Can you help me with the Enterprise Manager GUI then?
Thx,
David
December 14, 2006 at 10:26 am
I don't always do restores in Enterprise Manager, but I think what you need to do is check "Force restore over existing database" on the Options tab.
So I'm clear about what you want to do, do you want to retain the database name DB_ACC or do you want to change the name to DB_PROD?
Greg
Greg
December 15, 2006 at 1:28 am
Thank you Greg.
I want to retain the name DB_ACC.
David
December 15, 2006 at 2:52 am
David
The WITH MOVE and WITH REPLACE options are used if you are restoring using T-SQL, for example in Query Analyzer. Their respective Enterprise Manager equivalents are changing the file names in the Restore As field and ticking the Force restore over existing database box.
If the names and the paths of the source and destination database files are the same, you don't need to change anything in Restore As. Just make sure that in the Restore as database box on the General tab, it says DB_ACC.
John
December 15, 2006 at 3:07 am
I have managed to do it! 🙂
I have saved a copy of the DB_PROD backup file on the local disk, which allowed me to initiate a "Restore database from device" for DB_ACC with the device being this DB_PROD backup file. Then indeed I selected "Force restore over existing database" as option and corrected the "Move to physical file name" information (path and file names) so that it points to my DB_ACC mdf and ldf files.
Thank a lot for your help,
David
December 15, 2006 at 2:14 pm
If you get the backup from defferent place or taken by some one and don't have any info...
Always better to make use of the following commands to get the logical and physical file name and backup info...
RESTORE FILELISTONLY
RESTORE HEADERONLY
MohammedU
Microsoft SQL Server MVP
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply