• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

Setting up a retention policy for SQL backups

Hello,

This email is my first to the Experts Exchange so forgive me for any breach of protocol.  I am trying to clean up a server's local drive since it is running out of disk space.  The main culprit that is taking up most of the disk space are these nightly SQL backups.  I have been able to move alot of these backups to another drive but the backups are still incrementing so I will eventually have to do it again in the future.  How can I set up a retention policy to only keep backups that are current for say like a week or two?  We are also backing up all local drives for this server with NetBackup each night so I am wondering if I even need to keep these backups on the local server.  The server has SQL Server 2005.  Thanks!
0
letotad
Asked:
letotad
  • 3
1 Solution
 
RiteshShahCommented:
what you have to do is.

take transaction log backup every 5 min.

take differential backup at every 1 hour

take full backup at every 24 hours

and delete 24 hours backup.

create job for everything and sleep without tension.
0
 
RiteshShahCommented:
enable xp_cmdshell from sql server surface area configuration and set following query in job, instruction is also commented in script.

it will create backup as I mentioned in above post and you can make delete schedule as per your requirement, like week, fortnight etc.



----------------------------------------------------------------------------
-- Transaction Log Backup script
-- Should be scheduled to run every 10 min
---------------------------------------------------------------------------- 
DECLARE @BackupFolder AS varchar(250);
DECLARE @CurDT AS datetime;
DECLARE @DBName AS varchar(250);
SET @DBName = 'Adventureworks';
SET @CurDT = getdate();
IF NOT EXISTS(select * from master.dbo.sysprocesses where cmd = 'BACKUP DATABASE' and DB_NAME(dbid) = @DBName)
BEGIN
 SET @BackupFolder = 'j:\SQL-BACKUP\';
 SET @BackupFolder = @BackupFolder + @DBName + '\';
 EXEC master..Adventureworks_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + CONVERT(varchar(10), @CurDT, 110) + '\';
 EXEC master..Adventureworks_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + @DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(second,@CurDT) AS varchar(2))+'.trn'; 
 BACKUP LOG @DBName
 TO  DISK = @BackupFolder
 WITH 
 NOUNLOAD, SKIP, NOFORMAT 
END
ELSE 
 print 'Database backup in process! Skiping...'
 
----------------------------------------------------------------------------
-- Differential Backup script
-- Should be scheduled to run hourly
---------------------------------------------------------------------------- 
DECLARE @BackupFolder AS varchar(250);
DECLARE @CurDT AS datetime;
DECLARE @DBName AS varchar(250);
 
SET @DBName = 'Adventureworks';
SET @CurDT = getdate();
IF NOT EXISTS(select * from master.dbo.sysprocesses where cmd = 'BACKUP DATABASE' and DB_NAME(dbid) = @DBName)
BEGIN
 SET @BackupFolder = 'J:\SQL-BACKUP\';
 SET @BackupFolder = @BackupFolder + @DBName + '\';
 EXEC master..Adventureworks_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + CONVERT(varchar(10), @CurDT, 110) + '\';
 EXEC master..Adventureworks_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + @DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(second,@CurDT) AS varchar(2))+'.dif'; 
 BACKUP DATABASE @DBName
 TO  DISK = @BackupFolder 
 WITH DIFFERENTIAL,  
 NOUNLOAD, SKIP, NOFORMAT 
END
ELSE 
 print 'Database backup in process! Skiping...'
 
----------------------------------------------------------------------------
-- Full Backup
-- Should be scheduled to run daily
---------------------------------------------------------------------------- 
DECLARE @BackupFolder AS varchar(250);
DECLARE @Path AS varchar(250);
DECLARE @DBName AS varchar(250);
DECLARE @CurDT AS datetime;
DECLARE @Status AS int;
SET @CurDT = getdate();
SET @Path = 'J:\SQL-BACKUP\';
DECLARE crDatabases CURSOR
FOR
 select name from master..sysdatabases where name not in ('tempdb','Northwind','pubs','model','EDDVOA','ASPState')
OPEN crDatabases
FETCH NEXT FROM crDatabases INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
 SET @BackupFolder = @Path + @DBName + '\';
 EXEC master..Adventureworks_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + CONVERT(varchar(10), @CurDT, 110) + '\';
 EXEC master..Adventureworks_CreateFolder @BackupFolder
 SET @BackupFolder = @BackupFolder + @DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.0'+'.bak';
 BACKUP DATABASE @DBName
 TO  DISK = @BackupFolder 
 WITH INIT,  
 NOUNLOAD, SKIP, NOFORMAT 
   FETCH NEXT FROM crDatabases INTO @DBName
END
CLOSE crDatabases
DEALLOCATE crDatabases
 
--------------------------------------------------------------------------
-- Deleting 1 days old backups....
--------------------------------------------------------------------------
DECLARE @BackupFolder2DaysBack AS varchar(250); 
DECLARE @BackupFolder1DayBack AS varchar(250);
DECLARE @Path AS varchar(250);
DECLARE @CurDT AS datetime;
DECLARE @PrevDT AS datetime;
DECLARE @DBName AS varchar(250);
SET @Path = 'E:\BACKUP\';
SET @CurDT = DateAdd(Day,-1,getdate());
SET @PrevDT = DateAdd(Day,-2,getdate());
-- Loop through databases
DECLARE delExpDatabases CURSOR
FOR
 select name from master..sysdatabases where name not in ('tempdb','Northwind','pubs','model','ASPState') 
OPEN delExpDatabases
FETCH NEXT FROM delExpDatabases INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
 
 SET @BackupFolder2DaysBack = @Path + @DBName + '\' + CONVERT(varchar(10), @PrevDT, 110);
 SET @BackupFolder1DayBack = @Path + @DBName + '\' + CONVERT(varchar(10), @CurDT, 110); 
 DECLARE @cmdStr AS varchar(250);
 SET @cmdStr = 'rmdir /Q /S '+@BackupFolder2DaysBack
 --print @cmdStr
 EXEC master..xp_cmdshell @cmdStr, NO_OUTPUT
 SET @cmdStr = 'ERASE /Q /S '+@BackupFolder1DayBack+'\*.dif '+@BackupFolder1DayBack+'\*.trn' 
 --print @cmdStr
 EXEC master..xp_cmdshell @cmdStr, NO_OUTPUT
   FETCH NEXT FROM delExpDatabases INTO @DBName
END
CLOSE delExpDatabases
DEALLOCATE delExpDatabases
 
 
-----------------------------------------------------------------------------
---- Stored Procedures to be created in Master Database which creates folder.
----------------------------------------------------------------------------- 
 
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[Adventureworks_CreateFolder](
    @Path varchar(255)
    )
AS
BEGIN
 SET NOCOUNT ON
 
 DECLARE @retCode as int;
 CREATE TABLE #tmpRes
 (
  IsFileExists int,
  IsDirectory int,
  IsParentExists int
 )
 INSERT INTO #tmpRes
 EXEC xp_fileexist @Path
 
 IF EXISTS(SELECT * FROM #tmpRes WHERE IsParentExists = 1 AND IsDirectory = 0)
 BEGIN
  DECLARE @cmd as Varchar (250)
  SET @Path = 'MKDIR '+@Path
  
  EXEC @retCode = xp_cmdshell @Path ,NO_OUTPUT
  IF @@ERROR <> 0 AND @retCode <> 0
  BEGIN
      GOTO Failure
  END
 END
 ELSE IF EXISTS(SELECT * FROM #tmpRes WHERE IsDirectory = 0) 
 BEGIN
  DECLARE @errMgs as Varchar (250)
  SET @errMgs = 'Unable to create folder '+@Path
  RAISERROR(@errMgs, 16, 1)
 END
 DROP TABLE #tmpRes
 SET NOCOUNT OFF
 RETURN 0
Failure:
 SET ROWCOUNT 0
 DROP TABLE #tmpRes
 SET NOCOUNT OFF
 RETURN 1
END

Open in new window

0
 
letotadAuthor Commented:
RiteshShah,

Thanks for the reply.  I am not a programmer so is it possible to set up the retention policy through SQL Server Management Studio?  

0
 
RiteshShahCommented:
you don't need to programme anything, just set above script in your JOBS and you are done.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now