!!!!!!! - Wish you all a very Happy Ganesh Chaturthi - !!!!!!!
(Couple of new things happened in last few weeks. Started working with new some onshore Clients where I’ll get exposed to some new technology to learn along with SQL Server. Also my favorite Series 24 Season 5, where Jack Bauer is back to find the reason behind assassination of former President David Palmer and today is Ganesh Chaturthi as well.)
Requirement: One of my client asked to us to design a script which should take backup of all Databases including system DB, Verify those Backups from corruption & also should clean up old backups depending upon retention period.
Solution: Below you can find the script which satisfies the above requirement. This code is compatible from 2005 on wards.
Following is the detailed description of the code:
Parameters:
*@Directory => Mention the Backup location to take. It is compulsory.
*@No_of_hours => Specify how much older backup you want to delete. It is again compulsory.
@BackupType => Type of Backup Full (F), Differential (D) and Log (L) Backup. By default it will take Full Backup.
@Verify => It will verify the Backup against corruption. By default it will not verify the backup [Y => Yes; N => No].
Example how the code will work:
E.g. Suppose you pass SP_Backup ‘D:\Backup\’, 25, ‘D’,’Y’
1. Identify the list of Databases which Differential Backup needs to be done.
2. Databases backup will be done on D:\Backup\*.diff drive.
3. It will simultaneously verify the Backups; as the parameter passed is Y.
4. Finally; it will delete the 25 Hours Backup Files from the same directory.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[SP_Backup] Script Date: 8/28/2014 12:53:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[SP_Backup_ALL] --SP_Backup_ALL 'C:\Backup\',1
@Directory NVARCHAR(256), -- Mention Path to take the Backup [E.g. C:\Backup\]
@No_of_hours INT, -- Specify no of hours to clean old backups [E.g. 5 means it will delete 5 Hours old Backup]
@BackupTYpe NVARCHAR(10) ='F', -- Specify Backup Type; By default it will take "Full" Backup [F => Full; D => Differential; L => Log]
@Verify NVARCHAR(256)='N' -- Specify Verify Type; By default it will not verify the backup [Y => Yes; N => No]
As
Begin
Declare @Name NVARCHAR(100) -- Database Name
Declare @FileDate NVARCHAR(100) -- Used for file name
Declare @FileName NVARCHAR(256) -- Filename for Full & Diff backup
Declare @FileName_Log NVARCHAR(256) -- Filename for Log backup
Declare @DeleteDate NVARCHAR(100)
Declare @DeleteDateTime DATETIME
Set @FileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
Set @DeleteDateTime = DateAdd(hh, -@No_of_hours, GetDate())
Set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
/***************Start Full Backup*********************/
IF @BackupTYpe='F'
BEGIN
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE state_desc='ONLINE' and name NOT IN ('tempdb') -- Exclude these databases (Specify the list of databases which you want to exclude from backup)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @Directory + @name + '_' + @fileDate + '.bak'
BACKUP DATABASE @name TO DISK = @fileName with stats=25
/***************Verify the backup*********************/
IF @Verify = 'Y'
BEGIN
Restore verifyonly from disk = @fileName
END
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
ELSE
/***************Start Differential Backup*********************/
IF @BackupTYpe='D'
BEGIN
DECLARE db_cursor CURSOR FOR
SELECT a.name
FROM master.sys.databases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
where b.type='D' and a.name <> 'master'and a.state_desc='ONLINE'
GROUP BY a.name, b.type
ORDER BY a.name, b.type
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @Directory + @name + '_' + @fileDate + '.diff'
--print @fileName
BACKUP DATABASE @name TO DISK = @fileName with Differential,stats=25
/***************Verify the backup*********************/
IF @Verify = 'Y'
BEGIN
Restore verifyonly from disk = @fileName
END
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
/***************Start Log Backup*********************/
ELSE
IF @BackupTYpe='L'
BEGIN
DECLARE db_cursor CURSOR FOR
/******* DB's with Simple Recovery Model & DB's with no Full Backup will be skip from Log Backup *******/
SELECT a.name
FROM master.sys.databases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
where a.recovery_model_desc <> 'SIMPLE' and b.type='D'and a.state_desc='ONLINE'
GROUP BY a.name, b.type
ORDER BY a.name, b.type
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName_Log = @Directory + @name + '_' + @fileDate + '.trn'
--print @fileName_Log
BACKUP log @name TO DISK = @fileName_Log with stats=25
/***************Verify the backup*********************/
IF @Verify = 'Y'
BEGIN
Restore verifyonly from disk = @fileName_Log
END
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
/*********** Start Deletion of OLD Backups *****************/
IF @BackupTYpe = 'F'
BEGIN
EXECUTE master.dbo.xp_delete_file 0,@Directory,N'bak',@DeleteDate,1 -- It will delete the "Full" Backup
END
ELSE
IF @BackupTYpe = 'D'
BEGIN
EXECUTE master.dbo.xp_delete_file 0,@Directory,N'diff',@DeleteDate,1 -- It will delete the "Differential" Backup
END
ELSE
BEGIN
EXECUTE master.dbo.xp_delete_file 0,@Directory,N'trn',@DeleteDate,1 -- It will delete the "Log" Backup
END
END
GO
Let me know if any concerns.
Ganpati Bappa Moriya!!!!!
