These file names are often changed when refreshed from a different database adn will ensure the name will still follow any standards. This script modifies all dtabase .mdf and .ldf to have a standardized name of DatabaseName_Data and DatabaseName_Log. This can be changed to your company standards.
-- Modify Data Files Set Nocount on Declare @sqlstm varchar(200) Declare @dbname sysname Declare @LogicalLogFileName sysname Declare rolldbname cursor for select a.name, b.name--, type from master.sys.databases a INNER JOIN MASTER.sys.master_files b ON A.database_id = b.database_id and a.database_id >=5 and b.type=0--Doesnt include master, msdb, model --select * from sys.master_files Open rolldbname fetch next from rolldbname into @dbname, @LogicalLogFileName While @@fetch_status=0 BEGIN SET @sqlstm=' ALTER DATABASE ['+@dbname+'] MODIFY FILE (NAME=['+@LogicalLogFileName+'], NEWNAME='+@dbname+'_Data);' EXEC (@sqlstm) fetch next from rolldbname into @dbname, @LogicalLogFileName END close rolldbname deallocate rolldbname go -- Modify Log Files Set Nocount on Declare @sqlstm varchar(200) Declare @dbname sysname Declare @LogicalLogFileName sysname Declare rolldbname cursor for select a.name, b.name--, type from master.sys.databases a INNER JOIN MASTER.sys.master_files b ON A.database_id = b.database_id and a.database_id >=5 and b.type=1--Doesnt include master, msdb, model --select * from sys.master_files Open rolldbname fetch next from rolldbname into @dbname, @LogicalLogFileName While @@fetch_status=0 BEGIN SET @sqlstm=' ALTER DATABASE ['+@dbname+'] MODIFY FILE (NAME=['+@LogicalLogFileName+'], NEWNAME='+@dbname+'_Data);' EXEC (@sqlstm) fetch next from rolldbname into @dbname, @LogicalLogFileName END close rolldbname deallocate rolldbname go
Capitalize the first letter of each new word in the string (clean, efficient, and easy to modify).
2008-08-14
326 reads
