Friday, August 29, 2014

Database Backup Script...

!!!!!!! - 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!!!!!