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.
----------------------
EXECUTE master.dbo.Amit_DB_Backup_Script
@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
CREATE PROCEDURE [dbo].[Amit_DB_Backup_Script]
@BackupType char(1),
@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