Help with special characters

[gmagerr]

Hi all
I've been working with MS to help me shrink my trans logs on a sharepoint mirrored farm. They sent me this stored proc. I added it to all of the databases and it worked great for all but one of the databases. The database name has some dashes in it. The MS tech had me mod the code that shrinks the log files but now when the trans log is backed up it has the square brackets as part of it's name

Here's the file to execute and below that is the stored proc. Can someone help me fix this? It saves the trans log like this

[SharePoint_AdminContent_8aa49568-88a5-44cd-9a71-5517285d6f5f].trn

Thanks

USE [SharePoint_AdminContent_8aa49568-88a5-44cd-9a71-5517285d6f5f]
exec dbo.sp_shrink_log_mirrored_database '[SharePoint_AdminContent_8aa49568-88a5-44cd-9a71-5517285d6f5f]', 500, '\\smspbackup\Backups\MOSS SQL Production\Logs'
GO



Create Procedure [dbo].[sp_shrink_log_mirrored_database] 
	@database_name Varchar(100),
	@dbSize Int,
	@dbPath Varchar(1000)
AS
Begin
	SET NOCOUNT OFF
	Declare @DBPathFIle Varchar(4000)
	Declare @filename sysname
	Declare @filesize int
    Declare @sql nvarchar(4000)
         
	Declare dbLogBackup cursor for 
	  select [name] from sys.master_files where type=1 and database_id = db_id (@database_name)

	SET @DBPathFIle = @dbPath + '\' + @database_name + '.Trn'
	SET @DBPathFIle = 'BACKUP LOG ' + @database_name + ' TO  DISK = ''' + @dbPath + '\' + @database_name + '.Trn'''
	Exec (@DBPathFIle)
	
	open dbLogBackup
	
	FETCH next from dbLogBackup into @filename
	
    While @@fetch_status=0
    Begin
		Set @sql = 'DBCC Shrinkfile(' + @filename + ',' + CAST(@dbSize As Varchar(10)) + ')'
		
		Exec ( @sql )
		
		select @filesize = Size from sys.master_files where type=1 and database_id = db_id (@database_name)

		Set @filesize = ( @filesize + 1 ) * 8
		
		Set @sql='alter database [' + @database_name + '] modify file ( name=' 
             + @filename + ', size=' + cast(@filesize as nvarchar) + 'kb )'
             
        EXECUTE sp_executesql @sql
        FETCH next from dbLogBackup into @filename
    End
	close dbLogBackup
    deallocate dbLogBackup
	SET NOCOUNT OFF
End

 

[mrdenny]

Change this line

SET @DBPathFIle = 'BACKUP LOG ' + @database_name + ' TO  DISK = ''' + @dbPath + '\' + @database_name + '.Trn'''

To this

SET @DBPathFIle = 'BACKUP LOG ' + @database_name + ' TO  DISK = ''' + @dbPath + '\' + replace(replace(@database_name, '[', ''), ']', '') + '.Trn'''

This should correct the filename of the backup.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog