SQL-Articles
On Premise and Cloud Database Knowledge Base

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

3 comments

  1. I was trying to implement the script “Backup Script with retention” in this website in one of my test environment.

    When executing the script in the below fashion, it runs successfully.
    EXEC master.dbo.usp_backup ‘D:sqlbackup\’,1

    But when I change the path to different folder, it throws me error of invalid path error
    EXEC master.dbo.usp_backup ‘D:Program Files\Microsoft SQL Server\MSSQL\BACKUP\’,1

    I have cross checked the path, it is present. Not sure of @path parameter how to define here in this stored proc.
    Please assist.

  2. Thanks Shaik for pointing this. Actually I have done a mistake in sample command. You can find I have missed the back slash after drive letter which causes the error. You have done the same thing hence you faced that error. I’ve re-written the query with little improvements, you can use the updated one which works fine. 🙂

  3. Hello VIDHYASAGAR

    I’m probably commenting on an old post, but maybe you’ll see this and can help me.
    It looks like a former collegue used your script as basis for our backups in my company. I’m having a little bit of an issue, the script executes fine but every 2 -3 days it only backsup about 20% of the databases and leaves the others out. I still get an success at the end but only for those 10-15 databases, might you have an idea what could cause this?
    Kind regards

    Metin

    USE [master]
    GO
    /****** Object: StoredProcedure [dbo].[daily_rotating_backup_simple] Script Date: 07/25/2014 09:26:08 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[daily_rotating_backup_simple]
    @path VARCHAR(400), — path to backup location (w/o db name!!!). Eg. ‘x:\backups\simple’
    @databasenamelist as varchar(500) — list of databases to exclude in this backup eg: ‘tempdb,other_db_to_exclude’
    AS
    SET NOCOUNT ON
    DECLARE @date VARCHAR(100)
    DECLARE @dbname VARCHAR(100)
    DECLARE @bkup VARCHAR(500)
    DECLARE @doscommand VARCHAR(8000)
    DECLARE @mypath VARCHAR(400)
    DECLARE @doscommandreturnvalues VARCHAR(8000)
    DECLARE @recoveryscript VARCHAR(8000)
    DECLARE @recoveryscriptname VARCHAR(200)

    DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

    –Fill the list passed to the stored procedure into a temp database

    DECLARE @myquery VARCHAR(8000);
    DECLARE @spot int
    DECLARE @mystring varchar(8000)
    –SET @path=’\\wntmas\SQLPOOLBACKUP\Simple’
    –SET @databasenamelist=’tempdb’

    create table #daily_tempdb_simple (excludeddbname varchar(100))

    while @databasenamelist ”
    BEGIN
    set @spot = CHARINDEX(‘,’, @databasenamelist)
    if @spot > 0
    begin
    set @mystring = left(@databasenamelist, @spot – 1)
    set @databasenamelist = right(@databasenamelist, len(@databasenamelist) – @spot)
    end
    else
    begin
    set @mystring = @databasenamelist
    set @databasenamelist = ”
    end
    set @myquery = ‘insert into #daily_tempdb_simple values(”’+convert(varchar(100), @mystring)+”’)’
    exec(@myquery)
    end

    –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 + ‘\’

    –prepare recovery script
    SET @recoveryscriptname = @path+’myrecovery.sql’
    set @recoveryscript=’echo –recovery script for all databases in simple instance > ‘+@recoveryscriptname

    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 name not in (select excludeddbname from #daily_tempdb_simple) and name not like ‘ReportServer%’ 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

    PRINT @@CURSOR_ROWS
    –SET @dbname = replace(CAST(@dbname AS VARCHAR),’-‘,’_’)

    –delete oldest backup
    SET @doscommand=’EXEC master.dbo.xp_cmdshell ”IF EXIST “‘ +@path+@dbname+’\daily.6” rmdir /Q /S “‘ +@path+@dbname+’\daily.6″”,no_output’
    PRINT ‘delete oldest daily backup’
    PRINT ‘^^^^^^^^^^^^^^^^^^^^^^^^^^’
    EXEC (@doscommand)
    PRINT @doscommand
    –rotate all other backups
    SET @doscommand=’EXEC master.dbo.xp_cmdshell ”IF EXIST “‘ +@path+@dbname+’\daily.5” move “‘ +@path+@dbname+’\daily.5” “‘ +@path+@dbname+’\daily.6″”,no_output’
    EXEC (@doscommand)
    PRINT @doscommand
    WAITFOR DELAY ’00:00:10′
    SET @doscommand=’EXEC master.dbo.xp_cmdshell ”IF EXIST “‘ +@path+@dbname+’\daily.4” move “‘ +@path+@dbname+’\daily.4” “‘ +@path+@dbname+’\daily.5″”,no_output’
    EXEC (@doscommand)
    PRINT @doscommand
    WAITFOR DELAY ’00:00:10′
    SET @doscommand=’EXEC master.dbo.xp_cmdshell ”IF EXIST “‘ +@path+@dbname+’\daily.3” move “‘ +@path+@dbname+’\daily.3” “‘ +@path+@dbname+’\daily.4″”,no_output’
    EXEC (@doscommand)
    PRINT @doscommand
    WAITFOR DELAY ’00:00:10′
    SET @doscommand=’EXEC master.dbo.xp_cmdshell ”IF EXIST “‘ +@path+@dbname+’\daily.2” move “‘ +@path+@dbname+’\daily.2” “‘ +@path+@dbname+’\daily.3″”,no_output’
    EXEC (@doscommand)
    PRINT @doscommand
    WAITFOR DELAY ’00:00:10′
    SET @doscommand=’EXEC master.dbo.xp_cmdshell ”IF EXIST “‘ +@path+@dbname+’\daily.1” move “‘ +@path+@dbname+’\daily.1” “‘ +@path+@dbname+’\daily.2″”,no_output’
    EXEC (@doscommand)
    PRINT @doscommand
    WAITFOR DELAY ’00:00:10′
    SET @doscommand=’EXEC master.dbo.xp_cmdshell ”IF EXIST “‘ +@path+@dbname+’\daily.0” move “‘ +@path+@dbname+’\daily.0” “‘ +@path+@dbname+’\daily.1″”,no_output’
    EXEC (@doscommand)
    PRINT @doscommand
    WAITFOR DELAY ’00:00:10′
    –create new actual backup directory
    SET @doscommand=’EXEC master.dbo.xp_cmdshell ”IF NOT EXIST “‘ +@path+@dbname+’\daily.0” mkdir “‘ +@path+@dbname+’\daily.0″”,no_output’
    EXEC (@doscommand)
    PRINT @doscommand
    WAITFOR DELAY ’00:00:10′

    SET @bkup=’BACKUP DATABASE [‘+@dbname+’] TO DISK = ”’+@path+@dbname+’\daily.0\’+@dbname+’.bak” WITH INIT,COMPRESSION’
    PRINT ‘************Processing ‘+@dbname+’ Backup… **************’
    EXEC (@bkup)
    PRINT ‘Backed up to ‘ + @path+@dbname+’\daily.0\’+@dbname+’.bak’
    PRINT ‘********************************************************’
    PRINT ”

    –add a file to every backup set to mark the backupset as ready for recovery
    SET @doscommand=’EXEC master.dbo.xp_cmdshell ”IF NOT EXIST “‘+@path+@dbname+’\daily.0\’+@dbname+’.ready” echo “‘+@path+@dbname+’\daily.0\’+@dbname+’.bak” > “‘+@path+@dbname+’\daily.0\’+@dbname+’.ready””,no_output’
    EXEC (@doscommand)
    –PRINT @doscommand

    –add recovery statement for this db into a file (can then be used to recovery all dbs in one time) but not for system databases!
    if LOWER(@dbname) ‘master’ and LOWER(@dbname) ‘model’ and LOWER(@dbname) ‘msdb’ and LOWER(@dbname) ‘tempdb’
    set @recoveryscript=@recoveryscript+’ && echo RESTORE DATABASE ‘+@dbname+’ >> ‘+@recoveryscriptname+’ && echo FROM DISK = ””’+@path+@dbname+’\daily.0\’+@dbname+’.bak”” >> ‘+@recoveryscriptname+’ && echo WITH RECOVERY,REPLACE >> ‘+@recoveryscriptname
    print @recoveryscript

    FETCH NEXT FROM bkup_cursor INTO @dbname
    END
    CLOSE bkup_cursor
    DEALLOCATE bkup_cursor

    SET @doscommand=’EXEC master.dbo.xp_cmdshell ”’+@recoveryscript+”’,no_output’
    print @doscommand
    –print len(@doscommand)
    –EXEC (@doscommand)

    PRINT ‘============Backup Completed Successfully============’

Leave a Reply to Shaik Nazeer Ahmed Cancel reply

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

*