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

T-SQL for Delete backups

How to write T-SQL to compare two existing sql backups (.bak) in a given location and delete old backups?
This is sql server 2016?
0
Vijay
Asked:
Vijay
  • 4
  • 2
  • 2
  • +2
3 Solutions
 
Kelvin SparksCommented:
I use Powershell for jobs like that. Can delete files in a folder (can also drill down to sub folders) to delete foles older thata set number of days.

Happy to provide the applet if you're interested.

Kelvin
1
 
Ajay ChananaMCSE-2003/08|RHCSA| VCP5/6 |vExpert2018Commented:
Some what i agree with Kelvin, as SQL will only delete if the database is mounted to SQL.

Although you may create procedure in SQL with below script and schedule it for deleting old backup.


CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles] @path NVARCHAR(256),
      @extension NVARCHAR(10),
      @age_hrs INT
AS
BEGIN
      SET NOCOUNT ON;

      DECLARE @DeleteDate NVARCHAR(50)
      DECLARE @DeleteDateTime DATETIME

      SET @DeleteDateTime = DateAdd(hh, - @age_hrs, GetDate())

        SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

      EXECUTE master.dbo.xp_delete_file 0,
            @path,
            @extension,
            @DeleteDate,
            1
END
0
 
VijayAuthor Commented:
Hi Ajay,

Let me try and get back to you.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Mark WillsTopic AdvisorCommented:
Why not set up a maintenance plan to "set and forget" once you are happy with it of course...

Read : https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard

Or walkthrough : https://dataginger.com/2013/08/07/sql-server-automating-backup-files-cleanup-task-using-maintenance-plans/

Includes disk cleanup...
0
 
VijayAuthor Commented:
Mark  Yes. We already configured maintenance plans . But this requirement  is for some testing.
0
 
Mark WillsTopic AdvisorCommented:
No worries, misunderstood the requirements.

Will leave you in the hands of the experts :)

One question, if the backups are going to the same location, I assume the only real comparison is Date of the file on disk ? Or is it time period ?

You might want to get a list of BAK files before doing : EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDate,0;

It is an undocumented procedure so you do need to take care....

Check out : https://voiceofthedba.com/2017/10/09/using-xp_delete_file/
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What do you want to compare?
If they are the same backup file? Then check the file names, sizes and date and time.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Vijay, please let us know if this issue has been solved or if you need further help.
0
 
VijayAuthor Commented:
Thank you very much Virot
0
 
VijayAuthor Commented:
Thank you Vitor.
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

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now