script to delete old backup jobs

I used the code below, what the difference between @command and fullPathName?



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
AND RIGHT(FullPathName,16) <
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')


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

KPoku

>I used the code below, what the difference between @command and
>fullPathName?

@command is set to run the command:
master.dbo.xp_cmdshell "DIR D:\Program Files\Microsoft SQL
Server\MSSQL\Backup\*.* /B /S"

However, this is incorrect. Perhaps I mixed up the single quotes and double
quotes. You want your set of @Command to look like this:

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



Second, what is in fullPathName after the EXECUTE(@Command)? The contents
of the DIR command are inserted into #BackupFiles, so each row of that table
has one column, FullPathName, with the full path qualified name of a file.
E.g.

D:\Program Files\Microsoft SQL Server\MSSQL\Backup\Database_081104.BAK
D:\Program Files\Microsoft SQL Server\MSSQL\Backup\Database_081105.BAK
D:\Program Files\Microsoft SQL Server\MSSQL\Backup\Database_081106.BAK

RLF

-- My response to your other thread with your latest question. --
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

RLF


"opokad06" wrote in message

>I used the code below, what the difference between @command and
>fullPathName?
>
>
>
> 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
> AND RIGHT(FullPathName,16) <
> REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),
> DATEADD(month, -1, GETDATE()),120) ,' ',''),':',''),'-','')
>
>
> 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