Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 681
  • Last Modified:

Modify SQL query to delete .bak older than 7 days

I have a SQL query file and am not to familiar with SQL.  I need to add "delete bak files older than 7 days" into the below script:

DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200)
SET @BackupDirectory = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\'
--Add a list of all databases you don't want to backup to this.
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'Northwind'
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0

    BEGIN
          SET @Name = @DB + '( Daily BACKUP )'
          SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
          SET @BackupFile = @BackupDirectory + + @DB + '_' + 'Full' + '_' +
                CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
          SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'

          IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
                BEGIN
                      SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
                            CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
                      --SET some more pretty stuff for sql server.
                      SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
                END      
          ELSE
                BEGIN
                      SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
                            CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
                      --SET some more pretty stuff for sql server.
                      SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
                END
                BACKUP DATABASE @DB TO DISK = @BackupFile
                WITH NAME = @Name, DESCRIPTION = @Description ,
                MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,
                STATS = 10
          FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
0
d_ww
Asked:
d_ww
  • 2
1 Solution
 
grayeCommented:
Just curious.... why not use the built-in Maintenance Plan feature to create a "Clean up" Task?
If for some reason, you don't like using Maintenance Plans, you could at least let it create the T-SQL for you, then do a cut-n-paste
0
 
d_wwAuthor Commented:
Sorry the version I am using is SQL 2008 studio express.  There is no maintenance feature.  Have to manually created the scripts.
0
 
grayeCommented:
Here is what get's automatically generated by the Maintenance Plan....
EXECUTE master.dbo.xp_delete_file 0,N'C:\Microsoft SQL Server\MSSQL\Backup',N'bak',N'2008-09-16T12:40:57'
Does the Express Edition have the xp_delete_file stored procedure?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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