DECLARE @FILEPATH NVARCHAR(255) -- servername
SET @FILEPATH = '[FILEPATH LIKE: \\SERVERNAME\SHARENAME\FOLDERNAME]'
DECLARE @name NVARCHAR(256) -- database name
DECLARE @bakLocation NVARCHAR(255) -- Backup location
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
DECLARE @backupHistoryDate NVARCHAR(40) -- used for file name
DECLARE @fileExtention NVARCHAR(10) -- file extension
DECLARE @sendSucceeded NVARCHAR(3)
DECLARE @errorexists bit
DECLARE @succeededbaks NVARCHAR(MAX)
DECLARE @failedbaks NVARCHAR(MAX)
DECLARE @csubject NVARCHAR(255)
DECLARE @cbody NVARCHAR(MAX)
DECLARE @delDate Datetime
DECLARE @recipients NVARCHAR(50)
-- customizable variables
SET @sendSucceeded = 'no' -- decide to send mail everytime [yes] or only on errors [no]
SET @fileExtention = 'full.bak' -- [full.bak] or [diff.bak]
-- specify the backup location path / share until last static folder
SET @bakLocation = @FILEPATH +'\' + (CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'MSSQL\' ELSE '\' END)
SET @recipients = '[RECIPIENTS-MAIL-ADRESSES LIKE: user.example@mail.com]'
-- you can change the date difference between today and last created backups (second parameter must be negative)
SET @delDate = DATEADD(DAY, -15, GETDATE())
-- delete backup_history older than XX days
SET @backupHistoryDate = FORMAT(DATEADD(DAY, -22, GETDATE()), 'yyyy-MM-dd_hh-mm-ss')
-- don't touch these variables!
-- mail html body
SET @cbody = '
'
SET @failedbaks = 'Fehlerhafte Aktionen
'
SET @succeededbaks = 'Erfolgreiche Aktionen:
'
SET @errorexists = 1
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
-- specify filename format
SET @fileDate = FORMAT(GETDATE(), 'yyyy-MM-dd_hh-mm-ss')
-- specify database backup directory @name = databasename as last folder
SET @path = @bakLocation + @name + '\'
BEGIN TRY
-- create subfolders until path end
EXECUTE master.dbo.xp_create_subdir @path
END TRY
BEGIN CATCH
SET @errorexists = 1
SET @failedbaks += ' Pfad [' + @path + '] konnte nicht erstellt werden
'
END CATCH
SET @fileName = @path + @name + '_' + @fileDate + '.' + @fileExtention
BEGIN TRY
BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, NOINIT, NAME = @name, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10
-- changeable backup procedure
-- EXECUTE master.dbo.xp_delete_file @FileType = 0 [backup=0 or log=1], @FolderPath = @path, @FileExtension = N'bak', @Date = @DeleteDate, @Subfolder = 1
EXECUTE master.dbo.xp_delete_file 0, @path, @fileExtention, @delDate, 1
SET @succeededbaks += ' Full Backup [' + @name + '_' + @fileDate + '.' + @fileExtention + ']
' +
' Bereinigung Backupdateien älter ' + CONVERT(VARCHAR(25), @delDate, 120) + '
' +
' Bereinigung Backup Log älter ' + CONVERT(VARCHAR(25), @backupHistoryDate, 120) + '
' +
' Pfad: ' + @path + '
'
SET @errorexists = 0
END TRY
BEGIN CATCH
SET @errorexists = 1
SET @failedbaks += ' Full Backup [' + @name + '_' + @fileDate + '.' + @fileExtention + ']
' +
' Bereinigung Backupdateien älter ' + CONVERT(VARCHAR(25), @delDate, 120) + '
' +
' Bereinigung Backup Log älter ' + CONVERT(VARCHAR(25), @backupHistoryDate, 120) + '
' +
' Pfad: ' + @path + '
'
END CATCH
FETCH NEXT FROM db_cursor INTO @name
END
IF (@errorexists = 0)
BEGIN
SET @failedbaks += ' Keine Fehler'
END
SET @failedbaks += '
'
SET @succeededbaks += '
'
SET @cbody = @failedbaks + @succeededbaks +
'