DECLARE @FullBackup BIT = 1,
@DBList VARCHAR(128) = 'CoreStoreDev,CoreStore,CorestoreToDiablo,CRI,FNMA,MainFrame,Rental_Listing_2009,Rental_Listing_201009,RL_201009_011111',
@Folder VARCHAR(256) = 'H:\Full\'
DECLARE @SQL VARCHAR(MAX) ;
WITH DBList
AS (SELECT DISTINCT
(LTRIM(RTRIM(PARSENAME(Field, 1)))) AS DBName
FROM Util.dbo.ParseDelimited(@DBList, ',') b),
calc
AS (SELECT CONVERT(VARCHAR, GETDATE(), 109) AS GetdateStr,
'_' + CONVERT(VARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 114), ':', '') AS GetDatePostfix,
CASE WHEN @FullBackup = 0 THEN ',
@with = N''DIFFERENTIAL''' ELSE ''
END AS OPTIONS,
CASE WHEN @FullBackup = 1 THEN 'Full'
ELSE 'Differential'
END AS BackupTypeDescription)
SELECT @SQL = Util.dbo.StringConcat('DECLARE @ProcessorCount int
EXEC Util.dbo.GetProcessorCount @ProcessorCount = @ProcessorCount OUTPUT
SET @ProcessorCount = CASE WHEN @ProcessorCount > 32 THEN 32 ELSE @ProcessorCount - 1 END
EXEC master.dbo.xp_backup_database
@database = N''' + DBName + ''',
@backupname = N''' + DBName + ' - ' + BackupTypeDescription + ' Database Backup'',
@desc = N''' + BackupTypeDescription + ' Backup of ' + DBName + ' on ' + GetdateStr + ''',
@compressionlevel = 1,
@threads = @ProcessorCount,
@jobp = ''COE+O8I89Pe0KF5hZBqcyBjQXM0ciJw8ReQttNQ/1oU8vnSTkz+XOaTO7ocyq2xW'',
@filename = N''' + @Folder + DBName + '_' + BackupTypeDescription + GetDatePostfix + '.bak'',
@init = 1, @OLRMAP = 1 ,
@with = N''STATS = 10''' + OPTIONS, '
GO
')
FROM calc
CROSS JOIN DBList
PRINT @SQL
--EXEC( @SQL)