Categories
Scripts

Backup Script with Retention

Introduction:

SQL Backup script is available all over most of the online resources. I couldn’t find a script to take care of retention also, so Im writing this script as procedure to take care of retention too. This script will take full backup of all the databases.

Applies to:

* SQL Server 2000
* SQL Server 2005
* SQL Server 2008 & R2

Pre-requisites:

* Xp_cmdshell procedure should be enabled both in SQL 2005 & SQL 2008

Script:

CREATE PROCEDURE usp_backup  
@path VARCHAR(400), -- Includefinally. Eg. 'D:'  
@retentionperiod TINYINT -- retention period in days  
AS  
SET NOCOUNT ON  
DECLARE @date VARCHAR(100)  
DECLARE @dbname VARCHAR(50)  
DECLARE @bkup VARCHAR(500)  
DECLARE @retention VARCHAR(500)  
--Server Details  
PRINT '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$'  
PRINT '$ SERVERNAME: '+CONVERT(VARCHAR(50),SERVERPROPERTY('SERVERNAME'))+'DATE: '+ CONVERT(VARCHAR(50),GETDATE()) + ' $'  
PRINT '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$'  
PRINT ''  
--Adding backslash  
IF SUBSTRING(REVERSE(@path),1,1) <> '\'  
SET @path=@path + '\'  
--Retention  
SET @date= CONVERT(VARCHAR(10),GETDATE()-@retentionperiod,112)  
SET @retention='EXEC master.dbo.xp_cmdshell ''dir /b "' +@path+'*'+@date+'*.bak"''' 
PRINT 'Retention'  
PRINT '^^^^^^^^^'  
PRINT 'Deleting the below backup files as part of retention plan...'  
EXEC (@retention)  
SET @retention='EXEC master.dbo.xp_cmdshell ''del /Q "' +@path+'*'+@date+'*.bak"'''  
EXEC (@retention)  
PRINT ''  
PRINT 'Database Backups Started'  
PRINT '^^^^^^^^^^^^^^^^^^^^^^^^'  
--Backup Script  
SET @date= CONVERT(VARCHAR(10),GETDATE(),112)  
DECLARE bkup_cursor CURSOR FOR SELECT NAME FROM master.dbo.sysdatabases WHERE dbid <> 2 and DATABASEPROPERTYEX(name,'status') = 'ONLINE'  
OPEN bkup_cursor  
FETCH NEXT FROM bkup_cursor INTO @dbname  
IF @@FETCH_STATUS <> 0  
PRINT 'No database to backup...!!'  
WHILE @@FETCH_STATUS = 0  
BEGIN  
SET @bkup='BACKUP DATABASE '+@dbname+' TO DISK = '''+@path+@dbname+'_'+@date+'.bak'' WITH INIT'  
PRINT '************Processing '+@dbname+' Backup... **************'  
EXEC (@bkup)  
PRINT 'Backed up to ' + @path+@dbname+'_'+@date+'.bak'  
PRINT '********************************************************'  
PRINT ''  
FETCH NEXT FROM bkup_cursor INTO @dbname  
END  
CLOSE bkup_cursor  
DEALLOCATE bkup_cursor  
PRINT '============Backup Completed Successfully============'


Usage:

EXEC master.dbo.usp_backup 'F:\Bkup',1

Output:

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$ SERVERNAME: SAGARSYS DATE: Jun 28 2008 10:00AM $
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Retention
^^^^^^^^^
Deleting the below backup files as part of retention plan
output

master_20080627.bak
model_20080627.bak
msdb_20080627.bak
NULL

output

NULL

Database Backups Started
^^^^^^^^^^^^^^^^^^^^^^^^
************Processing master Backup **************
Processed 352 pages for database master, file master on file 1.
Processed 3 pages for database master, file mastlog on file 1.
BACKUP DATABASE successfully processed 355 pages in 0.747 seconds (3.887 MB/sec).
Backed up to F:Bkupmaster_20080628.bak
********************************************************

************Processing model Backup **************
Processed 152 pages for database model, file modeldev on file 1.
Processed 2 pages for database model, file modellog on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.344 seconds (3.655 MB/sec).
Backed up to F:Bkupmodel_20080628.bak
********************************************************

************Processing msdb Backup **************
Processed 608 pages for database msdb, file MSDBData on file 1.
Processed 2 pages for database msdb, file MSDBLog on file 1.
BACKUP DATABASE successfully processed 610 pages in 0.874 seconds (5.712 MB/sec).
Backed up to F:Bkupmsdb_20080628.bak
********************************************************

============Backup Completed Successfully===========

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*