Friday, July 15, 2011

TSQL : Tidy logical filenames

Works for the first data and log file for a database, renaming the logical filenames to match the database name with either _Data or _Log appended as appropriate.

-- Sort Logical filenames
DECLARE  @dbname NVARCHAR(255) 
DECLARE  @groupid INTEGER 
DECLARE  @logicalfilename NVARCHAR(255) 
DECLARE  @newlogicalfilename NVARCHAR(255) 
DECLARE  @tsql VARCHAR(1000)
DECLARE databasefiles CURSOR FORWARD_ONLY FOR 
SELECT 
	 DB_NAME(dbid) AS DBName
	,groupid 
	,RTRIM(name) AS LogicalFileName
FROM sysaltfiles
WHERE DB_NAME(dbid) NOT IN ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
AND groupid IN (0,1) AND fileid IN (1,2)

OPEN databasefiles 
WHILE (1 = 1) 
	BEGIN 
	FETCH NEXT FROM databasefiles 
	INTO @dbname, @groupid, @logicalfilename

	IF @@FETCH_STATUS <> 0 
	BREAK; 
	IF @groupid = 1 SET @newlogicalfilename = @dbname + '_Data'
	IF @groupid = 0 SET @newlogicalfilename = @dbname + '_Log'
	 
	IF (@logicalfilename <> @newlogicalfilename) AND (DATABASEPROPERTY(@dbname,'IsOffline')=0)
	BEGIN
		SET @tsql = 'ALTER DATABASE [' + @dbname + '] MODIFY FILE (NAME=N''' + @logicalfilename +''', NEWNAME=N'''+ @Newlogicalfilename +''');'
		RAISERROR (@tsql , 10, 1) WITH NOWAIT 
		EXECUTE (@tsql)
	END
	
	END 

CLOSE databasefiles 
DEALLOCATE databasefiles 

No comments: