January 7, 2004 at 8:33 am
I often restore database backups to new databases. I want the restored database to have its own logical name, not the one related to the backup. I've tried changing the logical name in the Restore File dialog box's Options tab. However, it gives me an error referring to a diffferent database on the server...
"The file D:\..\thirdDb_data.mdf cannot be overwritten. The file is in use by database "ThirdDb".
My first thought was that the third database and the one to which I'm restoring have the same logical names, but they don't. I can get the restore to be successful if I accept the backup's logical name. However, this means the new database's name, mdf will not match the logical name.
To avoid this, perhaps I should be creating a new database and importing objects into it rather than restoring to it. This will work when the source db is active. However, I often have only the backup, so I have to restore.
Any help would be appreciated. Thanks.
smv929
January 8, 2004 at 2:48 am
You can change the logical filename after the database has been restored.
ALTER DATABASE database MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name)
( see BOL: ALTER DATABASE )
/rockmoose
You must unlearn what You have learnt
January 8, 2004 at 6:01 am
Example TSQL from BOL
RESTORE DATABASE MyNwind2_Test
FROM MyNwind_2
WITH
MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',
MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'
Far away is close at hand in the images of elsewhere.
Anon.
February 13, 2004 at 9:44 am
Another question regarding this topic. Are there any side effects if the logical file name is left the same after a restore. In other words if two active databases have the same logical fiel name?
Thanks
December 13, 2005 at 2:53 am
Thanx for this. The alter database statements works great. Got it working on the first try.
Thanx again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply