KAKEE
Introducing Backup Kakee Script which will be one of our Kakee Scripts going forward.
NOTE:- I believe, I couldn't achieve a good comfort level/Confidence on its backup verification step so please re-consider this before applying.
However, I will keep you posted on this article in order to improve the same.
----------------------
@BkpPath = 'D:\MSSQL\Backup\',
@BackupType = 'D',--- D-(Full) - I-(Differential) - L (Log),
@Verify = 'Y'
---This script to set to take compressed backup by default.
---This script does not include for Azure Backups.
---Backup Retention is pre-defined and configured as 8 Day old for Full Backup, 48 Hours Old for Log Backup and 7 Days/1 week old for Differential Backup.
---Note During this Backup execution if in case any other user takes backup, this will only verify the last/Latest successful backup set for that particular Database.
---Author 'Amit Vaid' Dtd:- 25-11-2022
------------------------Create This Procedure in Master Database before Creating above Job Step
@BkpPath NVARCHAR(256),
-- path for backup files
@Verify CHAR(1)
-- This script to set to take compressed backup by default.
---Backup Retention is pre-defined and configured as 8 Day old for Full Backup, 48 Hours Old for Log Backup and 7 Days/1 week old for Differential Backup.
---This script does not include for Azure Backups.
-- Note During this Backup execution if in case any other user takes backup, this will only verify the last/Latest successful backup set for that particular Database.
---Author 'Amit Vaid' Dtd:- 25-11-2022.
AS
BEGIN
SET nocount ON
DECLARE @fileName NVARCHAR(max) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
DECLARE @lname NVARCHAR(40) -- used for file name
DECLARE @dname NVARCHAR(40) -- used for file name
DECLARE @name NVARCHAR(100) -- database name
DECLARE @DeleteDateBAK DATETIME = Dateadd(dd, -8, Getdate());
-- Purge date
DECLARE @DeleteDateTRN DATETIME = Dateadd(hh, -48, Getdate());
-- Purge date
DECLARE @DeleteDateDiff DATETIME = Dateadd(wk, -1, Getdate());
-- Purge date
DECLARE @physical_device_name NVARCHAR(max)
DECLARE @dbname NVARCHAR(100)
-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20), Getdate(), 112) + '_'
+ Replace(CONVERT(NVARCHAR(20), Getdate(), 108), ':',
'')
DECLARE db_cursor CURSOR read_only FOR
SELECT NAME
FROM master.sys.databases
WHERE NAME NOT IN ( '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
IF @BackupType = 'D'
BEGIN
SET @fileName = @Bkppath + @name + '_FULL_' + @fileDate +
'.BAK'
BACKUP DATABASE @name TO DISK = @fileName WITH compression
END
FETCH next FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
DECLARE db_cursorl 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 recovery_model < 3
AND is_in_standby = 0
-- database is not read only for log shipping
OPEN db_cursorl
FETCH next FROM db_cursorl INTO @lname
WHILE @@FETCH_STATUS = 0
BEGIN
IF @BackupType = 'L'
BEGIN
SET @fileName = @Bkppath + @lname + '_' + @fileDate +
'_LogBkp.trn'
BACKUP log @lname TO DISK = @fileName WITH compression
END
FETCH next FROM db_cursorl INTO @lname
END
CLOSE db_cursorl
DEALLOCATE db_cursorl
DECLARE db_cursord 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_cursord
FETCH next FROM db_cursord INTO @dname
WHILE @@FETCH_STATUS = 0
BEGIN
IF @BackupType = 'I'
BEGIN
SET @fileName = @Bkppath + @dname + '_' + @fileDate +
'_DIFF.Diff'
BACKUP DATABASE @dname TO DISK = @fileName WITH differential,
compression
END
FETCH next FROM db_cursord INTO @dname
END
CLOSE db_cursord
DEALLOCATE db_cursord
DECLARE backupsetbeverified CURSOR read_only FOR
WITH backup_information
AS (SELECT database_name,
physical_device_name,
rownum = Row_number()
OVER (
partition BY database_name, type,
family_sequence_number
ORDER BY backup_finish_date DESC )
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf
ON bmf.media_set_id = bs.media_set_id
WHERE device_type = 2
AND type = @BackupType
AND backup_finish_date BETWEEN
Getdate() - 2 AND Getdate()
--- Disk Backup
)
SELECT database_name AS dbname,
physical_device_name
FROM backup_information
WHERE rownum = 1
ORDER BY database_name;
OPEN backupsetbeverified;
FETCH next FROM backupsetbeverified INTO @dbname, @physical_device_name;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @verify = 'Y'
BEGIN
RESTORE verifyonly FROM DISK = @physical_device_name
PRINT 'Finished Verification ['
+ @physical_device_name + '] on database ['
+ @dbname + '] Backup Set is Valid';
END
FETCH next FROM backupsetbeverified INTO @dbname,
@physical_device_name
END
---- Purge old backup files from disk.
EXEC master.sys.Xp_delete_file
0,
@bkppath,
'BAK',
@DeleteDateBAK,
1;
EXEC master.sys.Xp_delete_file
0,
@bkppath,
'TRN',
@DeleteDateTRN,
1;
EXEC master.sys.Xp_delete_file
0,
@bkppath,
'DIFF',
@DeleteDateDIFF,
1;
--Parameters for XP_DELETE_FILE
--File Type = 0 for backup files or 1 for report files.
--Folder Path = The folder to delete files. The path must end with a backslash "".
--File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
--Date = The cutoff date for what files need to be deleted.
--Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.
CLOSE backupsetbeverified
DEALLOCATE backupsetbeverified
END
-----------------------------------------------------------------------------------------------------------------------------
Thanks with Regards
T. Amit Vaid S.
No comments:
Post a Comment