Script to delete backup files which are 7 days old

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
> 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" wrote in message
>
> > 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
>
>
 >> Stay informed about: Script to delete backup files which are 7 days old 

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" wrote in message

> 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" wrote in message
>>
>> > 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
>>
>>
 >> Stay informed about: Script to delete backup files which are 7 days old 

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/default.asp?url=/library/en-us/cdosy...tml/_cd
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@aol.com', 'test2@aol.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" wrote in message
>
> > 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" wrote in message
> >>
> >> > 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
> >>
> >>
>
>
 >> Stay informed about: Script to delete backup files which are 7 days old 

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" wrote in message

> 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/default.asp?url=/library/en-us/cdosy...tml/_cd
> 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@aol.com', 'test2@aol.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" wrote in message
>>
>> > 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" wrote in message
>> >>
>> >> > 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
>> >>
>> >>
>>
>>
 >> Stay informed about: Script to delete backup files which are 7 days old 

Thanks Russell.
--
KPoku


"Russell Fields" wrote:

> 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" wrote in message
>
> > 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/default.asp?url=/library/en-us/cdosy...tml/_cd
> > 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@aol.com', 'test2@aol.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" wrote in message
> >>
> >> > 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" wrote in message
> >> >>
> >> >> > 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
> >> >>
> >> >>
> >>
> >>
>
>
 >> Stay informed about: Script to delete backup files which are 7 days old 

Russell,

We name our backups consistently DatabaseName_yymmdd.bak on SQL Server 2000.
We now have simple recovery and do not backup transactional logs. Can you
help me me the code below work for SQL 2000.

Thanks for helping..

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

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)

--
KPoku


"Russell Fields" wrote:

> 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" wrote in message
>
> > 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/default.asp?url=/library/en-us/cdosy...tml/_cd
> > 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@aol.com', 'test2@aol.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" wrote in message
> >>
> >> > 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" wrote in message
> >> >>
> >> >> > 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
> >> >>
> >> >>
> >>
> >>
>
>
 >> Stay informed about: Script to delete backup files which are 7 days old 

KPoku,

I believe that this is what you are after. Change the last part of the
WHERE clause from the original example to this one:

-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,10) <
CONVERT(VARCHAR(10),
DATEADD(DAY, -7, GETDATE()),12)

Hope that it helps,
RLF

"opokad06" wrote in message

> Russell,
>
> We name our backups consistently DatabaseName_yymmdd.bak on SQL Server
> 2000.
> We now have simple recovery and do not backup transactional logs. Can you
> help me me the code below work for SQL 2000.
>
> Thanks for helping..
>
> The other is, if you name your files consistently with date in the
> name,
> such as:
> DatabaseName_db_YYYYMMDDHHMM.BAK
> DatabaseName_log_YYYYMMDDHHMM.TRN
>
> 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)
>
> --
> KPoku
>
>
> "Russell Fields" wrote:
>
>> 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" wrote in message
>>
>> > 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/default.asp?url=/library/en-us/cdosy...tml/_cd
>> > 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@aol.com', 'test2@aol.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" wrote in message
>> >>
>> >> > 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" wrote in message
>> >> >>
>> >> >> > 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
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>
 >> Stay informed about: Script to delete backup files which are 7 days old 

Thanks.

This is what I have now with the cursors....
where do I input the fullpathname?I know we set the @command but not the
fullpathname... I am novice in programming help me out.

CREATE TABLE #BackupFiles
(FullPathName NVARCHAR(256))


DECLARE @Command NVARCHAR(256)


SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ 'D:\Program Files\Microsoft SQL Server\MSSQL\Backup\' + '*.* /B /S"'
INSERT INTO #BackupFiles EXEC (@Command)


DECLARE @DeleteFiles NVARCHAR(4000)

DECLARE file_cursor CURSOR FOR

SELECT ' exec xp_cmdshell ''DEL "'
+ FullPathName + '"''' +CHAR(13)+CHAR(10)
FROM #BackupFiles
WHERE (FullPathName LIKE '%_%.BAK')
-- Trims punctuation and space from date time string
-- Trims punctuation and space from date time string
AND RIGHT(FullPathName,10) <
CONVERT(VARCHAR(10),
DATEADD(DAY, -7, GETDATE()),12)


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
--
KPoku


"Russell Fields" wrote:

> KPoku,
>
> I believe that this is what you are after. Change the last part of the
> WHERE clause from the original example to this one:
>
> -- Trims punctuation and space from date time string
> AND RIGHT(FullPathName,10) <
> CONVERT(VARCHAR(10),
> DATEADD(DAY, -7, GETDATE()),12)
>
> Hope that it helps,
> RLF
>
> "opokad06" wrote in message
>
> > Russell,
> >
> > We name our backups consistently DatabaseName_yymmdd.bak on SQL Server
> > 2000.
> > We now have simple recovery and do not backup transactional logs. Can you
> > help me me the code below work for SQL 2000.
> >
> > Thanks for helping..
> >
> > The other is, if you name your files consistently with date in the
> > name,
> > such as:
> > DatabaseName_db_YYYYMMDDHHMM.BAK
> > DatabaseName_log_YYYYMMDDHHMM.TRN
> >
> > 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)
> >
> > --
> > KPoku
> >
> >
> > "Russell Fields" wrote:
> >
> >> 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" wrote in message
> >>
> >> > 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/default.asp?url=/library/en-us/cdosy...tml/_cd
> >> > 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@aol.com', 'test2@aol.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" wrote in message
> >> >>
> >> >> > 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" wrote in message
> >> >> >> news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D@microsoft.com...
 >> Stay informed about: Script to delete backup files which are 7 days old 

KPoku,

If everything is set up correctly (rights to the drive, and so forth) then:

SET @Command = 'master.dbo.xp_cmdshell "DIR '
+ 'D:\Program Files\Microsoft SQL Server\MSSQL\Backup\' + '*.* /B /S"'

-- This will insert the full pathnames into #BackupFiles
INSERT INTO #BackupFiles EXEC (@Command)

Since you are not familiar with this syntax, you should read the details on
INSERT...EXEC in the Books Online for 2005, especially the examples toward
the end of:
http://msdn.microsoft.com/en-us/library/ms174335(SQL.90).aspx

Just try running that part of the script, then selecting from #BackupFiles
to see what you are getting.

RLF


"opokad06" wrote in message

> Thanks.
>
> This is what I have now with the cursors....
> where do I input the fullpathname?I know we set the @command but not the
> fullpathname... I am novice in programming help me out.
>
> CREATE TABLE #BackupFiles
> (FullPathName NVARCHAR(256))
>
>
> DECLARE @Command NVARCHAR(256)
>
>
> SET @Command = 'master.dbo.xp_cmdshell "DIR '
> + 'D:\Program Files\Microsoft SQL Server\MSSQL\Backup\' + '*.* /B /S"'
> INSERT INTO #BackupFiles EXEC (@Command)
>
>
> DECLARE @DeleteFiles NVARCHAR(4000)
>
> DECLARE file_cursor CURSOR FOR
>
> SELECT ' exec xp_cmdshell ''DEL "'
> + FullPathName + '"''' +CHAR(13)+CHAR(10)
> FROM #BackupFiles
> WHERE (FullPathName LIKE '%_%.BAK')
> -- Trims punctuation and space from date time string
> -- Trims punctuation and space from date time string
> AND RIGHT(FullPathName,10) <
> CONVERT(VARCHAR(10),
> DATEADD(DAY, -7, GETDATE()),12)
>
>
> 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
> --
> KPoku
>
>
> "Russell Fields" wrote:
>
>> KPoku,
>>
>> I believe that this is what you are after. Change the last part of the
>> WHERE clause from the original example to this one:
>>
>> -- Trims punctuation and space from date time string
>> AND RIGHT(FullPathName,10) <
>> CONVERT(VARCHAR(10),
>> DATEADD(DAY, -7, GETDATE()),12)
>>
>> Hope that it helps,
>> RLF
>>
>> "opokad06" wrote in message
>>
>> > Russell,
>> >
>> > We name our backups consistently DatabaseName_yymmdd.bak on SQL Server
>> > 2000.
>> > We now have simple recovery and do not backup transactional logs. Can
>> > you
>> > help me me the code below work for SQL 2000.
>> >
>> > Thanks for helping..
>> >
>> > The other is, if you name your files consistently with date in the
>> > name,
>> > such as:
>> > DatabaseName_db_YYYYMMDDHHMM.BAK
>> > DatabaseName_log_YYYYMMDDHHMM.TRN
>> >
>> > 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)
>> >
>> > --
>> > KPoku
>> >
>> >
>> > "Russell Fields" wrote:
>> >
>> >> 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" wrote in message
>> >>
>> >> > 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/default.asp?url=/library/en-us/cdosy...tml/_cd
>> >> > 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@aol.com', 'test2@aol.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" wrote in message
>> >> >>
>> >> >> > 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" wrote in message
>> >> >> >> news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D@microsoft.com...
 >> Stay informed about: Script to delete backup files which are 7 days old 

Thanks, I did try that I think it might be permissions issue. I will work on
that.
I do appreciate your reponses.
--
KPoku


"Russell Fields" wrote:

> KPoku,
>
> If everything is set up correctly (rights to the drive, and so forth) then:
>
> SET @Command = 'master.dbo.xp_cmdshell "DIR '
> + 'D:\Program Files\Microsoft SQL Server\MSSQL\Backup\' + '*.* /B /S"'
>
> -- This will insert the full pathnames into #BackupFiles
> INSERT INTO #BackupFiles EXEC (@Command)
>
> Since you are not familiar with this syntax, you should read the details on
> INSERT...EXEC in the Books Online for 2005, especially the examples toward
> the end of:
> http://msdn.microsoft.com/en-us/library/ms174335(SQL.90).aspx
>
> Just try running that part of the script, then selecting from #BackupFiles
> to see what you are getting.
>
> RLF
>
>
> "opokad06" wrote in message
>
> > Thanks.
> >
> > This is what I have now with the cursors....
> > where do I input the fullpathname?I know we set the @command but not the
> > fullpathname... I am novice in programming help me out.
> >
> > CREATE TABLE #BackupFiles
> > (FullPathName NVARCHAR(256))
> >
> >
> > DECLARE @Command NVARCHAR(256)
> >
> >
> > SET @Command = 'master.dbo.xp_cmdshell "DIR '
> > + 'D:\Program Files\Microsoft SQL Server\MSSQL\Backup\' + '*.* /B /S"'
> > INSERT INTO #BackupFiles EXEC (@Command)
> >
> >
> > DECLARE @DeleteFiles NVARCHAR(4000)
> >
> > DECLARE file_cursor CURSOR FOR
> >
> > SELECT ' exec xp_cmdshell ''DEL "'
> > + FullPathName + '"''' +CHAR(13)+CHAR(10)
> > FROM #BackupFiles
> > WHERE (FullPathName LIKE '%_%.BAK')
> > -- Trims punctuation and space from date time string
> > -- Trims punctuation and space from date time string
> > AND RIGHT(FullPathName,10) <
> > CONVERT(VARCHAR(10),
> > DATEADD(DAY, -7, GETDATE()),12)
> >
> >
> > 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
> > --
> > KPoku
> >
> >
> > "Russell Fields" wrote:
> >
> >> KPoku,
> >>
> >> I believe that this is what you are after. Change the last part of the
> >> WHERE clause from the original example to this one:
> >>
> >> -- Trims punctuation and space from date time string
> >> AND RIGHT(FullPathName,10) <
> >> CONVERT(VARCHAR(10),
> >> DATEADD(DAY, -7, GETDATE()),12)
> >>
> >> Hope that it helps,
> >> RLF
> >>
> >> "opokad06" wrote in message
> >>
> >> > Russell,
> >> >
> >> > We name our backups consistently DatabaseName_yymmdd.bak on SQL Server
> >> > 2000.
> >> > We now have simple recovery and do not backup transactional logs. Can
> >> > you
> >> > help me me the code below work for SQL 2000.
> >> >
> >> > Thanks for helping..
> >> >
> >> > The other is, if you name your files consistently with date in the
> >> > name,
> >> > such as:
> >> > DatabaseName_db_YYYYMMDDHHMM.BAK
> >> > DatabaseName_log_YYYYMMDDHHMM.TRN
> >> >
> >> > 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)
> >> >
> >> > --
> >> > KPoku
> >> >
> >> >
> >> > "Russell Fields" wrote:
> >> >
> >> >> 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" wrote in message
> >> >>
> >> >> > 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/default.asp?url=/library/en-us/cdosy...tml/_cd
> >> >> > 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@aol.com', 'test2@aol.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
> >> >> >>
> >> >> >>
 >> Stay informed about: Script to delete backup files which are 7 days old