Backup Script with Retention

Comments

3 responses to “Backup Script with Retention”

1. 

   Shaik Nazeer Ahmed

   May 27, 2011

   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.

   Reply
2. 

   Vidhyasagar

   May 27, 2011

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

   Reply
3. 

   Metin

   July 25, 2014

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

   Reply