Re: 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" <opokad06@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:116D5907-9929-4590-BE73-1E8FF168630E@xxxxxxxxxxxxxxxx
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/cdosys/html/_cdosys_schema_configuration_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@xxxxxxx', 'test2@xxxxxxx', '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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C1FA7B1E-AB8F-4C23-8E37-95E5F2150871@xxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:5B178E1D-2AED-4A9C-81A9-43F2D1717C3D@xxxxxxxxxxxxxxxx
>> > 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
>>
>>



.

Relevant Pages

  • Re: Script to delete backup files which are 7 days old
    ... -- This is to configure a remote SMTP server. ... DECLARE @DeleteFiles NVARCHAR ... SELECT 'exec xp_cmdshell ''DEL "' ...
    (microsoft.public.sqlserver.tools)
  • Re: Script to delete backup files which are 7 days old
    ... We name our backups consistently DatabaseName_yymmdd.bak on SQL Server 2000. ... We now have simple recovery and do not backup transactional logs. ... DECLARE @DeleteFiles NVARCHAR ...
    (microsoft.public.sqlserver.tools)
  • Re: Script to delete backup files which are 7 days old
    ... -- This will insert the full pathnames into #BackupFiles ... INSERT INTO #BackupFiles EXEC ... DECLARE @DeleteFiles NVARCHAR ... We name our backups consistently DatabaseName_yymmdd.bak on SQL Server ...
    (microsoft.public.sqlserver.tools)
  • Re: Script to delete backup files which are 7 days old
    ... We name our backups consistently DatabaseName_yymmdd.bak on SQL Server 2000. ... INSERT INTO #BackupFiles EXEC ... DECLARE @DeleteFiles NVARCHAR ...
    (microsoft.public.sqlserver.tools)
  • SQL-DMO problems
    ... I'm trying to generate the sql script for a given database. ... declare @DatabaseCollection int ... -- intializes the server object ... exec sp_OAGetErrorInfo @Server, @src OUT, @desc OUT ...
    (microsoft.public.sqlserver.programming)