Script_Backup databases iteratively and truncate name

Problem

For the database backup script, if the name @dbname is too long, longer than 128, there will be an error since the length of name column in msdb.dbo.backupset is nvarchar(128).

BACKUP DATABASE @name_small TO DISK = @fileName_small WITH NOFORMAT, NOINIT,
NAME = @dbName, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10

Script logic

• Backup all databases except for some required databases
• Truncate necessary characters when the length is larger than 128

DECLARE @name_small NVARCHAR(128) 
DECLARE @path_small NVARCHAR(512)  
DECLARE @fileName_small NVARCHAR(512) 
DECLARE @fileDate_small NVARCHAR(40) 
DECLARE @errorMessage NVARCHAR(256)
DECLARE @dbName NVARCHAR(128) 
DECLARE @counter int

-- specify database backup directory
SET @path_small = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\'   

-- specify filename format
SELECT @fileDate_small = replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') 

-- Cursor for small databases 
DECLARE db_cursor_small CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name 
NOT IN ('WSS_Content_xchange','tempdb')  -- Fill in databases you want to exclude here
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

PRINT('The backup script will skip the following databases as required:')
PRINT N'WSS_Content_xchange'
PRINT N'tempdb'
PRINT('The backup script will skip the following databases as they are not ONLINE:')
DECLARE @xmltmp xml = (SELECT name, state_desc FROM sys.databases where state_desc <> 'ONLINE' FOR XML AUTO)
PRINT CONVERT(NVARCHAR(MAX), @xmltmp)

OPEN db_cursor_small   
FETCH NEXT FROM db_cursor_small INTO @name_small   

SET @counter = 0
 
WHILE @@FETCH_STATUS = 0   
BEGIN
   SET @counter = @counter + 1
   -- Truncate database name if too long
   IF len(@name_small+'_'+@fileDate_small) > 128
		SET @dbName = LEFT(@name_small,len(@name_small)-len('_'+@fileDate_small))+'_'+@fileDate_small
   ELSE 
		SET @dbName = @name_small + '_' + @fileDate_small
   PRINT('-------------------')
   PRINT('Start backup database: ' + @name_small + ' AS ' + @dbName)
			
   SET @fileName_small = @path_small + @dbName + '.BAK'
   SET @errorMessage = 'Verify failed. Backup information for database ' + @name_small + ' not found.'
   BACKUP DATABASE @name_small TO DISK = @fileName_small WITH NOFORMAT, NOINIT,
   NAME = @dbName, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10
   DECLARE @backupSetId AS int
   SELECT @backupSetId = position FROM msdb..backupset 
   WHERE database_name = @name_small 
   AND backup_set_id=(SELECT max(backup_set_id) FROM msdb..backupset WHERE database_name = @name_small)
   IF @backupSetId IS NULL BEGIN RAISERROR(@errorMessage, 16, 1) END
   RESTORE VERIFYONLY FROM  DISK = @fileName_small WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
  
   FETCH NEXT FROM db_cursor_small INTO @name_small   
END   
PRINT('-------------------')
PRINT('Total backups:')
PRINT(@counter)

CLOSE db_cursor_small   
DEALLOCATE db_cursor_small

This was the very first T-SQL script I write when I interned as a Support Engineer at Microsoft.