Script to delete backup files which are 7 days old

#41

KPoku,

I have never written CDO code, so I am not readily equiped to help you with
that. However, you might want to know that the sp_smtp_sendmail and
xp_smtp_sendmail have been used for this purpose by many many people on SQL
Server 2000. I recommend that you look at it and see if it makes life
easier.

http://sqldev.net/xp/xpsmtp.htm

RLF

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

Quote:

Thanks, I will try that and let you know how it goes.

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

"Russell Fields" wrote:

So, the difference in SQL Server 2000 is that there is not an
NVARCHAR(MAX)
data type.

So, instead you will need to loop through the files. E.g.

DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR
SELECT '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

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @DeleteFiles

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
FETCH NEXT FROM file_cursor INTO @DeleteFiles
END

CLOSE file_cursor
DEALLOCATE file_cursor

You could apply similar logic to the other approach.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871 (AT) microsoft (DOT) com...
Thanks Russell, I am working in a SQL Server 2000 environment.
I will try the suggestions you made..

--
KPoku

"Russell Fields" 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

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 in message
news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D (AT) microsoft (DOT) com...
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

#42

KPoku,

I have never written CDO code, so I am not readily equiped to help you with
that. However, you might want to know that the sp_smtp_sendmail and
xp_smtp_sendmail have been used for this purpose by many many people on SQL
Server 2000. I recommend that you look at it and see if it makes life
easier.

http://sqldev.net/xp/xpsmtp.htm

RLF

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

Quote:

Thanks, I will try that and let you know how it goes.

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

"Russell Fields" wrote:

So, the difference in SQL Server 2000 is that there is not an
NVARCHAR(MAX)
data type.

So, instead you will need to loop through the files. E.g.

DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR
SELECT '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

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @DeleteFiles

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
FETCH NEXT FROM file_cursor INTO @DeleteFiles
END

CLOSE file_cursor
DEALLOCATE file_cursor

You could apply similar logic to the other approach.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871 (AT) microsoft (DOT) com...
Thanks Russell, I am working in a SQL Server 2000 environment.
I will try the suggestions you made..

--
KPoku

"Russell Fields" 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

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 in message
news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D (AT) microsoft (DOT) com...
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

#43

KPoku,

I have never written CDO code, so I am not readily equiped to help you with
that. However, you might want to know that the sp_smtp_sendmail and
xp_smtp_sendmail have been used for this purpose by many many people on SQL
Server 2000. I recommend that you look at it and see if it makes life
easier.

http://sqldev.net/xp/xpsmtp.htm

RLF

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

Quote:

Thanks, I will try that and let you know how it goes.

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

"Russell Fields" wrote:

So, the difference in SQL Server 2000 is that there is not an
NVARCHAR(MAX)
data type.

So, instead you will need to loop through the files. E.g.

DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR
SELECT '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

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @DeleteFiles

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
FETCH NEXT FROM file_cursor INTO @DeleteFiles
END

CLOSE file_cursor
DEALLOCATE file_cursor

You could apply similar logic to the other approach.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871 (AT) microsoft (DOT) com...
Thanks Russell, I am working in a SQL Server 2000 environment.
I will try the suggestions you made..

--
KPoku

"Russell Fields" 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

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 in message
news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D (AT) microsoft (DOT) com...
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

#44

KPoku,

I have never written CDO code, so I am not readily equiped to help you with
that. However, you might want to know that the sp_smtp_sendmail and
xp_smtp_sendmail have been used for this purpose by many many people on SQL
Server 2000. I recommend that you look at it and see if it makes life
easier.

http://sqldev.net/xp/xpsmtp.htm

RLF

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

Quote:

Thanks, I will try that and let you know how it goes.

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

"Russell Fields" wrote:

So, the difference in SQL Server 2000 is that there is not an
NVARCHAR(MAX)
data type.

So, instead you will need to loop through the files. E.g.

DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR
SELECT '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

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @DeleteFiles

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
FETCH NEXT FROM file_cursor INTO @DeleteFiles
END

CLOSE file_cursor
DEALLOCATE file_cursor

You could apply similar logic to the other approach.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871 (AT) microsoft (DOT) com...
Thanks Russell, I am working in a SQL Server 2000 environment.
I will try the suggestions you made..

--
KPoku

"Russell Fields" 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

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 in message
news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D (AT) microsoft (DOT) com...
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

#45

KPoku,

I have never written CDO code, so I am not readily equiped to help you with
that. However, you might want to know that the sp_smtp_sendmail and
xp_smtp_sendmail have been used for this purpose by many many people on SQL
Server 2000. I recommend that you look at it and see if it makes life
easier.

http://sqldev.net/xp/xpsmtp.htm

RLF

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

Quote:

Thanks, I will try that and let you know how it goes.

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

"Russell Fields" wrote:

So, the difference in SQL Server 2000 is that there is not an
NVARCHAR(MAX)
data type.

So, instead you will need to loop through the files. E.g.

DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR
SELECT '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

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @DeleteFiles

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
FETCH NEXT FROM file_cursor INTO @DeleteFiles
END

CLOSE file_cursor
DEALLOCATE file_cursor

You could apply similar logic to the other approach.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871 (AT) microsoft (DOT) com...
Thanks Russell, I am working in a SQL Server 2000 environment.
I will try the suggestions you made..

--
KPoku

"Russell Fields" 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

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 in message
news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D (AT) microsoft (DOT) com...
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

#46

KPoku,

I have never written CDO code, so I am not readily equiped to help you with
that. However, you might want to know that the sp_smtp_sendmail and
xp_smtp_sendmail have been used for this purpose by many many people on SQL
Server 2000. I recommend that you look at it and see if it makes life
easier.

http://sqldev.net/xp/xpsmtp.htm

RLF

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

Quote:

Thanks, I will try that and let you know how it goes.

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

"Russell Fields" wrote:

So, the difference in SQL Server 2000 is that there is not an
NVARCHAR(MAX)
data type.

So, instead you will need to loop through the files. E.g.

DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR
SELECT '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

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @DeleteFiles

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
FETCH NEXT FROM file_cursor INTO @DeleteFiles
END

CLOSE file_cursor
DEALLOCATE file_cursor

You could apply similar logic to the other approach.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871 (AT) microsoft (DOT) com...
Thanks Russell, I am working in a SQL Server 2000 environment.
I will try the suggestions you made..

--
KPoku

"Russell Fields" 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

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 in message
news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D (AT) microsoft (DOT) com...
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

#47

Thanks Russell.
--
KPoku

"Russell Fields" wrote:

Quote:

KPoku,

I have never written CDO code, so I am not readily equiped to help you with
that. However, you might want to know that the sp_smtp_sendmail and
xp_smtp_sendmail have been used for this purpose by many many people on SQL
Server 2000. I recommend that you look at it and see if it makes life
easier.

http://sqldev.net/xp/xpsmtp.htm

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:116D5907-9929-4590-BE73-1E8FF168630E (AT) microsoft (DOT) com...
Thanks, I will try that and let you know how it goes.

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

"Russell Fields" wrote:

So, the difference in SQL Server 2000 is that there is not an
NVARCHAR(MAX)
data type.

So, instead you will need to loop through the files. E.g.

DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR
SELECT '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

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @DeleteFiles

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
FETCH NEXT FROM file_cursor INTO @DeleteFiles
END

CLOSE file_cursor
DEALLOCATE file_cursor

You could apply similar logic to the other approach.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871 (AT) microsoft (DOT) com...
Thanks Russell, I am working in a SQL Server 2000 environment.
I will try the suggestions you made..

--
KPoku

"Russell Fields" 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

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 in message
news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D (AT) microsoft (DOT) com...
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

#48

Thanks Russell.
--
KPoku

"Russell Fields" wrote:

Quote:

KPoku,

I have never written CDO code, so I am not readily equiped to help you with
that. However, you might want to know that the sp_smtp_sendmail and
xp_smtp_sendmail have been used for this purpose by many many people on SQL
Server 2000. I recommend that you look at it and see if it makes life
easier.

http://sqldev.net/xp/xpsmtp.htm

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:116D5907-9929-4590-BE73-1E8FF168630E (AT) microsoft (DOT) com...
Thanks, I will try that and let you know how it goes.

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

"Russell Fields" wrote:

So, the difference in SQL Server 2000 is that there is not an
NVARCHAR(MAX)
data type.

So, instead you will need to loop through the files. E.g.

DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR
SELECT '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

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @DeleteFiles

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
FETCH NEXT FROM file_cursor INTO @DeleteFiles
END

CLOSE file_cursor
DEALLOCATE file_cursor

You could apply similar logic to the other approach.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871 (AT) microsoft (DOT) com...
Thanks Russell, I am working in a SQL Server 2000 environment.
I will try the suggestions you made..

--
KPoku

"Russell Fields" 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

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 in message
news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D (AT) microsoft (DOT) com...
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

#49

Thanks Russell.
--
KPoku

"Russell Fields" wrote:

Quote:

KPoku,

I have never written CDO code, so I am not readily equiped to help you with
that. However, you might want to know that the sp_smtp_sendmail and
xp_smtp_sendmail have been used for this purpose by many many people on SQL
Server 2000. I recommend that you look at it and see if it makes life
easier.

http://sqldev.net/xp/xpsmtp.htm

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:116D5907-9929-4590-BE73-1E8FF168630E (AT) microsoft (DOT) com...
Thanks, I will try that and let you know how it goes.

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

"Russell Fields" wrote:

So, the difference in SQL Server 2000 is that there is not an
NVARCHAR(MAX)
data type.

So, instead you will need to loop through the files. E.g.

DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR
SELECT '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

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @DeleteFiles

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
FETCH NEXT FROM file_cursor INTO @DeleteFiles
END

CLOSE file_cursor
DEALLOCATE file_cursor

You could apply similar logic to the other approach.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871 (AT) microsoft (DOT) com...
Thanks Russell, I am working in a SQL Server 2000 environment.
I will try the suggestions you made..

--
KPoku

"Russell Fields" 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

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 in message
news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D (AT) microsoft (DOT) com...
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

#50

Thanks Russell.
--
KPoku

"Russell Fields" wrote:

Quote:

KPoku,

I have never written CDO code, so I am not readily equiped to help you with
that. However, you might want to know that the sp_smtp_sendmail and
xp_smtp_sendmail have been used for this purpose by many many people on SQL
Server 2000. I recommend that you look at it and see if it makes life
easier.

http://sqldev.net/xp/xpsmtp.htm

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:116D5907-9929-4590-BE73-1E8FF168630E (AT) microsoft (DOT) com...
Thanks, I will try that and let you know how it goes.

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

"Russell Fields" wrote:

So, the difference in SQL Server 2000 is that there is not an
NVARCHAR(MAX)
data type.

So, instead you will need to loop through the files. E.g.

DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR
SELECT '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

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @DeleteFiles

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
FETCH NEXT FROM file_cursor INTO @DeleteFiles
END

CLOSE file_cursor
DEALLOCATE file_cursor

You could apply similar logic to the other approach.

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871 (AT) microsoft (DOT) com...
Thanks Russell, I am working in a SQL Server 2000 environment.
I will try the suggestions you made..

--
KPoku

"Russell Fields" 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

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 in message
news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D (AT) microsoft (DOT) com...
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