In our environment all jobs are scripted, we do not use maintenance plan.

Is there a script that I can add to my jobs that will run daily and delete
jobs that arre 7 days old?
--
KPoku

KPoku,

One easy way is to use your backup history to drive the process. For
example:

-- SQL Server 2005 data type
DECLARE @DeleteFiles NVARCHAR(MAX)

SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ physical_device_name + '"''' +CHAR(13)+CHAR(10)
FROM msdb.dbo.backupmediafamily ms
JOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id
WHERE backup_finish_date < GETDATE() - 7
AND backup_finish_date > GETDATE() - 9
and database_name = 'Admin2000FCDisaster'

EXEC (@DeleteFiles)

The two dates is just to allow for the job having been missed once or twice.
(Depending on the exact time.) You can make it more complicated, but this
is one way of working.

The other is, if you name your files consistently with date in the name,
such as:
DatabaseName_db_YYYYMMDDHHMM.BAK
DatabaseName_log_YYYYMMDDHHMM.TRN

Then you could run a script to search for existing files with names earlier
than 7 days ago. E.g.

CREATE TABLE #BackupFiles
(FullPathName NVARCHAR(256))

DECLARE @Command NVARCHAR(256)

SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ '\\BackupServer\BackupPath\' + '*.* /B /S"'
INSERT INTO #BackupFiles EXEC (@Command)

DECLARE @DeleteFiles NVARCHAR(MAX)
SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ FullPathName + '"''' +CHAR(13)+CHAR(10)
FROM #BackupFiles
WHERE (FullPathName LIKE '%_%.BAK'
OR FullPathName LIKE '%_%.TRN')
-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,16) <
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')

EXEC(@DeleteFiles)

Beware of any typos. Test in a test enviroment, etc.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote

KPoku,

One easy way is to use your backup history to drive the process. For
example:

-- SQL Server 2005 data type
DECLARE @DeleteFiles NVARCHAR(MAX)

SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ physical_device_name + '"''' +CHAR(13)+CHAR(10)
FROM msdb.dbo.backupmediafamily ms
JOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id
WHERE backup_finish_date < GETDATE() - 7
AND backup_finish_date > GETDATE() - 9
and database_name = 'Admin2000FCDisaster'

EXEC (@DeleteFiles)

The two dates is just to allow for the job having been missed once or twice.
(Depending on the exact time.) You can make it more complicated, but this
is one way of working.

The other is, if you name your files consistently with date in the name,
such as:
DatabaseName_db_YYYYMMDDHHMM.BAK
DatabaseName_log_YYYYMMDDHHMM.TRN

Then you could run a script to search for existing files with names earlier
than 7 days ago. E.g.

CREATE TABLE #BackupFiles
(FullPathName NVARCHAR(256))

DECLARE @Command NVARCHAR(256)

SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ '\\BackupServer\BackupPath\' + '*.* /B /S"'
INSERT INTO #BackupFiles EXEC (@Command)

DECLARE @DeleteFiles NVARCHAR(MAX)
SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ FullPathName + '"''' +CHAR(13)+CHAR(10)
FROM #BackupFiles
WHERE (FullPathName LIKE '%_%.BAK'
OR FullPathName LIKE '%_%.TRN')
-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,16) <
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')

EXEC(@DeleteFiles)

Beware of any typos. Test in a test enviroment, etc.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote

KPoku,

One easy way is to use your backup history to drive the process. For
example:

-- SQL Server 2005 data type
DECLARE @DeleteFiles NVARCHAR(MAX)

SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ physical_device_name + '"''' +CHAR(13)+CHAR(10)
FROM msdb.dbo.backupmediafamily ms
JOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id
WHERE backup_finish_date < GETDATE() - 7
AND backup_finish_date > GETDATE() - 9
and database_name = 'Admin2000FCDisaster'

EXEC (@DeleteFiles)

The two dates is just to allow for the job having been missed once or twice.
(Depending on the exact time.) You can make it more complicated, but this
is one way of working.

The other is, if you name your files consistently with date in the name,
such as:
DatabaseName_db_YYYYMMDDHHMM.BAK
DatabaseName_log_YYYYMMDDHHMM.TRN

Then you could run a script to search for existing files with names earlier
than 7 days ago. E.g.

CREATE TABLE #BackupFiles
(FullPathName NVARCHAR(256))

DECLARE @Command NVARCHAR(256)

SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ '\\BackupServer\BackupPath\' + '*.* /B /S"'
INSERT INTO #BackupFiles EXEC (@Command)

DECLARE @DeleteFiles NVARCHAR(MAX)
SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ FullPathName + '"''' +CHAR(13)+CHAR(10)
FROM #BackupFiles
WHERE (FullPathName LIKE '%_%.BAK'
OR FullPathName LIKE '%_%.TRN')
-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,16) <
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')

EXEC(@DeleteFiles)

Beware of any typos. Test in a test enviroment, etc.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote

KPoku,

One easy way is to use your backup history to drive the process. For
example:

-- SQL Server 2005 data type
DECLARE @DeleteFiles NVARCHAR(MAX)

SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ physical_device_name + '"''' +CHAR(13)+CHAR(10)
FROM msdb.dbo.backupmediafamily ms
JOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id
WHERE backup_finish_date < GETDATE() - 7
AND backup_finish_date > GETDATE() - 9
and database_name = 'Admin2000FCDisaster'

EXEC (@DeleteFiles)

The two dates is just to allow for the job having been missed once or twice.
(Depending on the exact time.) You can make it more complicated, but this
is one way of working.

The other is, if you name your files consistently with date in the name,
such as:
DatabaseName_db_YYYYMMDDHHMM.BAK
DatabaseName_log_YYYYMMDDHHMM.TRN

Then you could run a script to search for existing files with names earlier
than 7 days ago. E.g.

CREATE TABLE #BackupFiles
(FullPathName NVARCHAR(256))

DECLARE @Command NVARCHAR(256)

SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ '\\BackupServer\BackupPath\' + '*.* /B /S"'
INSERT INTO #BackupFiles EXEC (@Command)

DECLARE @DeleteFiles NVARCHAR(MAX)
SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ FullPathName + '"''' +CHAR(13)+CHAR(10)
FROM #BackupFiles
WHERE (FullPathName LIKE '%_%.BAK'
OR FullPathName LIKE '%_%.TRN')
-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,16) <
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')

EXEC(@DeleteFiles)

Beware of any typos. Test in a test enviroment, etc.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote

KPoku,

One easy way is to use your backup history to drive the process. For
example:

-- SQL Server 2005 data type
DECLARE @DeleteFiles NVARCHAR(MAX)

SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ physical_device_name + '"''' +CHAR(13)+CHAR(10)
FROM msdb.dbo.backupmediafamily ms
JOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id
WHERE backup_finish_date < GETDATE() - 7
AND backup_finish_date > GETDATE() - 9
and database_name = 'Admin2000FCDisaster'

EXEC (@DeleteFiles)

The two dates is just to allow for the job having been missed once or twice.
(Depending on the exact time.) You can make it more complicated, but this
is one way of working.

The other is, if you name your files consistently with date in the name,
such as:
DatabaseName_db_YYYYMMDDHHMM.BAK
DatabaseName_log_YYYYMMDDHHMM.TRN

Then you could run a script to search for existing files with names earlier
than 7 days ago. E.g.

CREATE TABLE #BackupFiles
(FullPathName NVARCHAR(256))

DECLARE @Command NVARCHAR(256)

SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ '\\BackupServer\BackupPath\' + '*.* /B /S"'
INSERT INTO #BackupFiles EXEC (@Command)

DECLARE @DeleteFiles NVARCHAR(MAX)
SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ FullPathName + '"''' +CHAR(13)+CHAR(10)
FROM #BackupFiles
WHERE (FullPathName LIKE '%_%.BAK'
OR FullPathName LIKE '%_%.TRN')
-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,16) <
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')

EXEC(@DeleteFiles)

Beware of any typos. Test in a test enviroment, etc.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote

KPoku,

One easy way is to use your backup history to drive the process. For
example:

-- SQL Server 2005 data type
DECLARE @DeleteFiles NVARCHAR(MAX)

SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ physical_device_name + '"''' +CHAR(13)+CHAR(10)
FROM msdb.dbo.backupmediafamily ms
JOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id
WHERE backup_finish_date < GETDATE() - 7
AND backup_finish_date > GETDATE() - 9
and database_name = 'Admin2000FCDisaster'

EXEC (@DeleteFiles)

The two dates is just to allow for the job having been missed once or twice.
(Depending on the exact time.) You can make it more complicated, but this
is one way of working.

The other is, if you name your files consistently with date in the name,
such as:
DatabaseName_db_YYYYMMDDHHMM.BAK
DatabaseName_log_YYYYMMDDHHMM.TRN

Then you could run a script to search for existing files with names earlier
than 7 days ago. E.g.

CREATE TABLE #BackupFiles
(FullPathName NVARCHAR(256))

DECLARE @Command NVARCHAR(256)

SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ '\\BackupServer\BackupPath\' + '*.* /B /S"'
INSERT INTO #BackupFiles EXEC (@Command)

DECLARE @DeleteFiles NVARCHAR(MAX)
SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ FullPathName + '"''' +CHAR(13)+CHAR(10)
FROM #BackupFiles
WHERE (FullPathName LIKE '%_%.BAK'
OR FullPathName LIKE '%_%.TRN')
-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,16) <
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')

EXEC(@DeleteFiles)

Beware of any typos. Test in a test enviroment, etc.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote

KPoku,

One easy way is to use your backup history to drive the process. For
example:

-- SQL Server 2005 data type
DECLARE @DeleteFiles NVARCHAR(MAX)

SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ physical_device_name + '"''' +CHAR(13)+CHAR(10)
FROM msdb.dbo.backupmediafamily ms
JOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id
WHERE backup_finish_date < GETDATE() - 7
AND backup_finish_date > GETDATE() - 9
and database_name = 'Admin2000FCDisaster'

EXEC (@DeleteFiles)

The two dates is just to allow for the job having been missed once or twice.
(Depending on the exact time.) You can make it more complicated, but this
is one way of working.

The other is, if you name your files consistently with date in the name,
such as:
DatabaseName_db_YYYYMMDDHHMM.BAK
DatabaseName_log_YYYYMMDDHHMM.TRN

Then you could run a script to search for existing files with names earlier
than 7 days ago. E.g.

CREATE TABLE #BackupFiles
(FullPathName NVARCHAR(256))

DECLARE @Command NVARCHAR(256)

SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ '\\BackupServer\BackupPath\' + '*.* /B /S"'
INSERT INTO #BackupFiles EXEC (@Command)

DECLARE @DeleteFiles NVARCHAR(MAX)
SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ FullPathName + '"''' +CHAR(13)+CHAR(10)
FROM #BackupFiles
WHERE (FullPathName LIKE '%_%.BAK'
OR FullPathName LIKE '%_%.TRN')
-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,16) <
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')

EXEC(@DeleteFiles)

Beware of any typos. Test in a test enviroment, etc.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote

KPoku,

One easy way is to use your backup history to drive the process. For
example:

-- SQL Server 2005 data type
DECLARE @DeleteFiles NVARCHAR(MAX)

SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ physical_device_name + '"''' +CHAR(13)+CHAR(10)
FROM msdb.dbo.backupmediafamily ms
JOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id
WHERE backup_finish_date < GETDATE() - 7
AND backup_finish_date > GETDATE() - 9
and database_name = 'Admin2000FCDisaster'

EXEC (@DeleteFiles)

The two dates is just to allow for the job having been missed once or twice.
(Depending on the exact time.) You can make it more complicated, but this
is one way of working.

The other is, if you name your files consistently with date in the name,
such as:
DatabaseName_db_YYYYMMDDHHMM.BAK
DatabaseName_log_YYYYMMDDHHMM.TRN

Then you could run a script to search for existing files with names earlier
than 7 days ago. E.g.

CREATE TABLE #BackupFiles
(FullPathName NVARCHAR(256))

DECLARE @Command NVARCHAR(256)

SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ '\\BackupServer\BackupPath\' + '*.* /B /S"'
INSERT INTO #BackupFiles EXEC (@Command)

DECLARE @DeleteFiles NVARCHAR(MAX)
SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ FullPathName + '"''' +CHAR(13)+CHAR(10)
FROM #BackupFiles
WHERE (FullPathName LIKE '%_%.BAK'
OR FullPathName LIKE '%_%.TRN')
-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,16) <
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')

EXEC(@DeleteFiles)

Beware of any typos. Test in a test enviroment, etc.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote

KPoku,

One easy way is to use your backup history to drive the process. For
example:

-- SQL Server 2005 data type
DECLARE @DeleteFiles NVARCHAR(MAX)

SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ physical_device_name + '"''' +CHAR(13)+CHAR(10)
FROM msdb.dbo.backupmediafamily ms
JOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id
WHERE backup_finish_date < GETDATE() - 7
AND backup_finish_date > GETDATE() - 9
and database_name = 'Admin2000FCDisaster'

EXEC (@DeleteFiles)

The two dates is just to allow for the job having been missed once or twice.
(Depending on the exact time.) You can make it more complicated, but this
is one way of working.

The other is, if you name your files consistently with date in the name,
such as:
DatabaseName_db_YYYYMMDDHHMM.BAK
DatabaseName_log_YYYYMMDDHHMM.TRN

Then you could run a script to search for existing files with names earlier
than 7 days ago. E.g.

CREATE TABLE #BackupFiles
(FullPathName NVARCHAR(256))

DECLARE @Command NVARCHAR(256)

SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ '\\BackupServer\BackupPath\' + '*.* /B /S"'
INSERT INTO #BackupFiles EXEC (@Command)

DECLARE @DeleteFiles NVARCHAR(MAX)
SET @DeleteFiles = ''

SELECT @DeleteFiles = @DeleteFiles +' exec xp_cmdshell ''DEL "'
+ FullPathName + '"''' +CHAR(13)+CHAR(10)
FROM #BackupFiles
WHERE (FullPathName LIKE '%_%.BAK'
OR FullPathName LIKE '%_%.TRN')
-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,16) <
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')

EXEC(@DeleteFiles)

Beware of any typos. Test in a test enviroment, etc.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote