Script to backup all (can be customized) databases in an instance using SQL Safe Idera

The following script can be used to generate and execute sql safe idera full backup on all user databases and where clause can be modified to include system databases too.
You can use the below script as a job step and can be scheduled for your server’s convenience.

/*****************************************************************/
/***************** SQL Idera Safe Full Backup Script *************/
/*****************************************************************/
/**************** info@trenovision.com ****************/
/*****************************************************************/

declare c1 cursor
for
select name from master..sysdatabases where

— excluding databases offline/torn page detection/trunc log on chkpt
status != 528 and status != 8

–excluding system databases
and name not in (‘master’,’model’,’msdb’)

DECLARE @currdate AS datetime,
@datestr AS varchar(50),
@filepath AS varchar(255),
@dbsvrname AS varchar(50),
@dbname AS varchar(50),
@path AS varchar(255),
@Description AS varchar(255),
@Delete As varchar(20),
@NoSkip As int,
@BackupType As varchar(30),
@RetVal as int

/*************************************** parameters start here **********************************/
–retention period as 30 days – change it to your requirement
SET @Delete =’30days’

–specify the path where you need to place your backups
SET @path=’L:\data\SQLServer\MSSQL.2\MSSQL\Backup\’

/*************************************** parameters End here ***********************************/

/***** defaulted parameters ******/
–get current time for suffixing backup files
SET @currdate = (select getdate())
SET @datestr = rtrim((select convert(char(8), @currdate, 112) + cast(datepart(hh, @currdate) as char(2))+ cast(datepart(mi, @currdate) as char(2))))

— to obtain the current date

SET @NoSkip=0
SET @BackupType=’FULL’
SET @Description=’Backup’

Open c1
Fetch next from c1 into @dbname
while @@fetch_Status = 0
begin

SET @filepath = @path +@dbname +’_’ + @BackupType + ‘_’ + @datestr + ‘.SAFE’
select ‘calling with dbname ‘ + @dbname + ‘ filename ‘ + @filepath + ‘ backname ‘ + @dbname + ‘ desc ‘ + @Description + ‘ bactype ‘ + @BackupType
EXEC @RetVal = master..xp_ss_backup @database = @dbname,@filename = @filepath,@backupname = @dbname, @desc = @description,@delete=@Delete,@noskip=@NoSkip,@backuptype=@BackupType

–exception on errors
if @RetVal >0
BEGIN
set @filepath=’Backup on ‘+ @dbname + ‘ Failed’
select @filepath
RAISERROR (@filepath,16,1)
END
Fetch next from c1 into @dbname
end

Close c1
Deallocate c1

if @RetVal >0
BEGIN
RAISERROR (‘User Backups Failed’,16,1)
END