Script to delete backup files which are 7 days old

#91

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/libr...5(SQL.90).aspx

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

RLF

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

Quote:

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" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:18BA52BC-6C8B-496F-85A5-FD37FE337FD8 (AT) microsoft (DOT) com...
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" <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...

#92

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:

Quote:

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/libr...5(SQL.90).aspx

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

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:906B2101-A775-4BBB-B4CE-16DC409B9758 (AT) microsoft (DOT) com...
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" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:18BA52BC-6C8B-496F-85A5-FD37FE337FD8 (AT) microsoft (DOT) com...
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" <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

#93

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:

Quote:

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/libr...5(SQL.90).aspx

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

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:906B2101-A775-4BBB-B4CE-16DC409B9758 (AT) microsoft (DOT) com...
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" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:18BA52BC-6C8B-496F-85A5-FD37FE337FD8 (AT) microsoft (DOT) com...
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" <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

#94

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:

Quote:

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/libr...5(SQL.90).aspx

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

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:906B2101-A775-4BBB-B4CE-16DC409B9758 (AT) microsoft (DOT) com...
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" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:18BA52BC-6C8B-496F-85A5-FD37FE337FD8 (AT) microsoft (DOT) com...
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" <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

#95

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:

Quote:

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/libr...5(SQL.90).aspx

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

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:906B2101-A775-4BBB-B4CE-16DC409B9758 (AT) microsoft (DOT) com...
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" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:18BA52BC-6C8B-496F-85A5-FD37FE337FD8 (AT) microsoft (DOT) com...
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" <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

#96

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:

Quote:

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/libr...5(SQL.90).aspx

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

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:906B2101-A775-4BBB-B4CE-16DC409B9758 (AT) microsoft (DOT) com...
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" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:18BA52BC-6C8B-496F-85A5-FD37FE337FD8 (AT) microsoft (DOT) com...
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" <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

#97

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:

Quote:

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/libr...5(SQL.90).aspx

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

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:906B2101-A775-4BBB-B4CE-16DC409B9758 (AT) microsoft (DOT) com...
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" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:18BA52BC-6C8B-496F-85A5-FD37FE337FD8 (AT) microsoft (DOT) com...
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" <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

#98

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:

Quote:

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/libr...5(SQL.90).aspx

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

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:906B2101-A775-4BBB-B4CE-16DC409B9758 (AT) microsoft (DOT) com...
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" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:18BA52BC-6C8B-496F-85A5-FD37FE337FD8 (AT) microsoft (DOT) com...
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" <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

#99

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:

Quote:

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/libr...5(SQL.90).aspx

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

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:906B2101-A775-4BBB-B4CE-16DC409B9758 (AT) microsoft (DOT) com...
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" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:18BA52BC-6C8B-496F-85A5-FD37FE337FD8 (AT) microsoft (DOT) com...
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" <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

#**100**

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:

Quote:

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/libr...5(SQL.90).aspx

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

RLF

"opokad06" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:906B2101-A775-4BBB-B4CE-16DC409B9758 (AT) microsoft (DOT) com...
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" <opokad06 (AT) discussions (DOT) microsoft.com> wrote in message
news:18BA52BC-6C8B-496F-85A5-FD37FE337FD8 (AT) microsoft (DOT) com...
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" <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