Tuesday, May 09, 2006

How to change the logical file names in MSSQL

Just incase this is valuable to anyone else, here is a script to change the logical file names in an mssql database.

declare @n1 varchar(100)
select @n1 = name from sysfiles where name not like '%_log%'
declare @n2 varchar(100)
select @n2 = name from sysfiles where name like '%_log%'

exec ('ALTER DATABASE [Test] modify file (NAME =''' + @n1 + ''', NEWNAME = ''Test_Data'')')
exec ('ALTER DATABASE [Test] modify file (NAME =''' + @n2 + ''', NEWNAME = ''Test_Log'')')

select name from sysfiles -- just to check

Assumptions:
  • 'Test' is the database name
  • You have two files
  • The log one ends in _log
  • The other one doesn't
Post a Comment

GitHub Projects