Changing LogicalFileNames in Production Databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Changing LogicalFileNames in Production Databases

Author

Topic

buzzi
Starting Member

48 Posts

Posted - 2008-06-27 : 13:50:04

Hello all,
Quick question.
I have few databases(sql 2005) that are in production, where i would like to change the LogicalFileNames to be consistent with the physical file names.
Can i do this on production db's with out affecting anything, the last thing that i want is that i screw up something(like backups etc)

ex: I have a db with name testdb

logical file names as :adcd_data,adcd_log
physical file names as:testdb_data,testdb_log

i want the logicalfilenames to look like :testdb_data,testdb_log

Thanks for the help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-27 : 13:58:04

You can use ALTER DATABASE.

Here's a script that I wrote a while back to switch my database names and files from Admin to DBA:

DECLARE @dataLogical sysname, @dataPhysical sysname, @logLogical sysname, @logPhysical sysname, @sql varchar(4000)

SELECT @dataLogical = name, @dataPhysical = physical_name
FROM master.sys.master_files
WHERE database_id = DB_ID('Admin') AND file_id = 1

SELECT @logLogical = name, @logPhysical = physical_name
FROM master.sys.master_files
WHERE database_id = DB_ID('Admin') AND file_id = 2

SET @sql = 'ALTER DATABASE Admin MODIFY FILE (NAME = ' + @dataLogical + ', FILENAME = ''' + SUBSTRING(@dataPhysical, 1, LEN(@dataPhysical) - CHARINDEX('\', REVERSE(@dataPhysical)) + 1) + 'DBA_Data.mdf' + ''')'
EXEC (@sql)

SET @sql = 'ALTER DATABASE Admin MODIFY FILE (NAME = ' + @logLogical + ', FILENAME = ''' + SUBSTRING(@logPhysical, 1, LEN(@logPhysical) - CHARINDEX('\', REVERSE(@logPhysical)) + 1) + 'DBA_Data.mdf' + ''')'
EXEC (@sql)

SET @sql = 'ALTER DATABASE Admin MODIFY FILE (NAME = ' + @dataLogical + ', NEWNAME = DBA_Data)'
EXEC (@sql)

SET @sql = 'ALTER DATABASE Admin MODIFY FILE (NAME = ' + @logLogical + ', NEWNAME = DBA_Log)'
EXEC (@sql)

ALTER DATABASE Admin MODIFY NAME = DBA

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-27 : 13:59:53

ALTER DATABASE databasename
MODIFY FILE (NAME = logical_name, NEWNAME = Physical_name)

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-27 : 15:06:24

Yes sodeep example is the one to follow.
now how the get the logical_name :

use databasename
Select * from sysfiles and check the name column.
Or sp_helpdb ADTTRC

Maninder

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-27 : 15:10:47

quote:
Originally posted by maninder

Yes sodeep example is the one to follow.
now how the get the logical_name :

use databasename
Select * from sysfiles and check the name column.
Or sp_helpdb ADTTRC

Maninder

You shouldn't be using sysfiles anymore for SQL Server 2005. See my code for how to grab the info properly for 2005.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-06-27 : 15:58:31

quote:
Originally posted by tkizer
You shouldn't be using sysfiles anymore for SQL Server 2005. See my code for how to grab the info properly for 2005.

I really enjoy learning something new everyday like this! This will make my life tons easier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources