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===========
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.
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. 🙂
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============’