Idera SQL Safe Backup Script

DECLARE @counter INT
DECLARE @maxCount INT
DECLARE @result INT
DECLARE @failed INT
DECLARE @dbname NVARCHAR(128)
DECLARE @backupSetName NVARCHAR(256)
DECLARE @backupSetDescription NVARCHAR(512)

SET @counter = 1
SET @maxCount = 1
SET @result = 0
SET @failed = 0

-- Create the temporary database table
IF object_id('tempdb..#databases') IS NOT NULL DROP TABLE #databases

CREATE TABLE #databases
(
id INT IDENTITY,
databaseName NVARCHAR(128),
backupSetName NVARCHAR(256),
backupSetDescription NVARCHAR(512)
)

-- Insert the databases to be backed up
INSERT INTO #databases VALUES ('User_DB1', 'Full backup for policy Backup_USER_DB_Full(Noencriptions)', '')
INSERT INTO #databases VALUES ('User_DB2', 'Full backup for policy Backup_USER_DB_Full(Noencriptions)', '')
SELECT @maxCount = MAX(id) FROM #databases

-- Loop through and backup each database
WHILE @counter <= @maxCount
BEGIN

SELECT @dbname = [databaseName], @backupSetName = [backupSetName], @backupSetDescription = [backupSetDescription] FROM #databases WHERE id = @counter


EXEC @result = [master].[dbo].[xp_ss_backup] @database = @dbname,@filename = 'D:\SQLData\MSSQL\BACKUP\IderaSQLBackups\USER_DB_BACKUPS\FULL\%instance%_%database%_%timestamp%.safe',@delete = '1days',@backupname = @backupSetName,@desc = @backupSetDescription,@verify = 1,@compressionlevel = 'ispeed',@threads = 3
IF (@result != 0) SET @failed = 1

SET @counter = @counter + 1

END

-- If one or more failures occurred, signal an error
IF (@failed != 0) RAISERROR('One or more backups failed to complete.', 16, 1)