SQL Server Performance Forum – Threads Archive
Restoring on a new database: logical file names
Hi there guys… Imagine u have the following scenario : Database_A with a backup and A_data, A_log logical file namesDatabase_B with file names B_data, B_log the aim would be to restore Database_A on Database_B keeping the logical names of Database_B (not changing them, as it is happening to me all the time) … I have been trying for a while with the SQLEM and I can’t sort it out …. any clue? thanks a lot
Not sure, if I understand you correct, but you can always use ALTER DATABASE to modify the logical file name. I don’t think you can do this in EM. HTH
–Frank
http://www.insidesql.de
hey Frank, within the command ALTER DATABASE, the params are like this… ALTER DATABASE database
{ ADD FILE < filespec > [ ,…n ] [ TO FILEGROUP filegroup_name ]
| ADD LOG FILE < filespec > [ ,…n ]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE < filespec >
| MODIFY NAME = new_dbname
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,…n ] [ WITH < termination > ]
| COLLATE < collation_name >
} the only option I see is to use "MODIFY FILE" but then u should do something like :
ALTER DATABASE Database_B
MODIFY FILE
(NAME = A_Data ……??? and here u should refer to your NEW logical name "B_Data", but … No way to refer to A_Data and change it to B_Data, for instance … Nevertheless, am I being understood?
During restore you can’t change the logical file name. It doesn’t matter to SQL Server if you have same logical file names in multiple databases, it only has to be unique in the database itself. But if you just want it "to look good" or it matters for some of your admin script then in SQL Server 2000 you can change it with the ALTER DATABASE command as mentioned. In SQL Server 7 there is no supported way to change the logical file names. SQL 2000:
alter database MyDatabase modify file (NAME = ‘oldMdfName’, NEWNAME = ‘NewMdfNAme’)
alter database MyDatabase modify file (NAME = ‘oldLdfName’, NEWNAME = ‘NewLdfNAme’)
Thank you so much Argyle, it just WORKS FINE …
and yes, the aim was the database structure "look good", sometimes just a question of "fashion", "control", "convention" or whatever, maybe too concerned about those topics … thanks a lot and Im still wondering WHY there are such statements NOT documented in SQL Books On-line, for instance, ……. is that topic somethin I am missing ??? thanks again
]]>