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
outputmaster_20080627.bak
model_20080627.bak
msdb_20080627.bak
NULLoutput
–
NULLDatabase 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