• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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