I am not a DBA, I need to delete the USER_DATABASE backups older than 2 weeks. Is this below stmt work for this scenario?

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASE', 
@Directory = 'C:\Backup',      
@BackupType = 'FULL',          
@Verify = 'Y',                
@Compress = 'Y',              
@CheckSum = 'Y',
@CleanupTime = 336          

This below script is my regular backup.

declare @Date varchar(16),
        @query nvarchar(100);

select @Date = convert(varchar,Year(getdate())) + right('00' +  convert(varchar,Month(getdate())),2) + right('00' + convert(varchar,Day(getdate())),2) + right('00'+convert(varchar,datepart(hh,getdate())),2) + right('00'+convert(varchar,datepart(Mi,getdate())),2);

select @query = N'BACKUP DATABASE db_CP_Staging TO  DISK = ''D:\backups\db_C_Staging\db_C_Staging' + @Date + '.bak' + '''';

exec sp_executesql @statement = @query;

How can I add cleanup time to this query? Can some one help me on this?


Yes, it looks like you're using Ola Hallengren's backup scripts which operate in hours, so 14 days * 24 hours = 336 hours.

Every time this is run, a full backup of the user databases will be taken and any copies older than 2 weeks will be deleted.


Based on the OP's edit, you are fine to schedule a job executing your first script as a replacement for the second script you posted. The frequency of this will depend on your backup and restore needs for your business.

As Kris said, this delete only occurs after a successful backup unless you specify the parameter @CleanupMode = 'BEFORE_BACKUP'

  • Thank you for you reply john , yes i found it through Google . Do you have any other alternative suggestions?
    – user55125
    Dec 16 '14 at 18:28
  • Nope - Ola's scripts are extremely good! I'd suggest reading his site for information on the various other options the script has to make sure you're getting the most out of it. Dec 16 '14 at 18:29
  • 2
    Worth adding that the delete only runs after the backup successfully completes. So, if there's an error during the backup, the delete operation won't complete. Another reason to test your backups often! Dec 16 '14 at 18:42
  • i can use the ola's post, but i need to create the backup file with date on it . ex: db_C_Staging201412160700.bak can some one help me to write the query ?
    – user55125
    Dec 16 '14 at 19:20
  • @user55125 Ola's script does this. The format is SERVER_DATABASE_FULL_YYYYMMDD_HHMMSS.bak for a full database backup. Dec 16 '14 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.