dbTalk Databases Forums  

script to delete old backup jobs

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.tools script to delete old backup jobs

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools


Discuss script to delete old backup jobs in the microsoft.public.sqlserver.tools forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
opokad06
 
Posts: n/a

Default script to delete old backup jobs - 11-25-2008 , 10:58 AM





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

Reply With Quote
  #2  
Old   
Russell Fields
 
Posts: n/a

Default Re: script to delete old backup jobs - 11-25-2008 , 01:07 PM





KPoku

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

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

RLF

RLF


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

Quote:
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


Reply With Quote
  #3  
Old   
Russell Fields
 
Posts: n/a

Default Re: script to delete old backup jobs - 11-25-2008 , 01:07 PM


KPoku

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

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

RLF

RLF


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

Quote:
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


Reply With Quote
  #4  
Old   
Russell Fields
 
Posts: n/a

Default Re: script to delete old backup jobs - 11-25-2008 , 01:07 PM


KPoku

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

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

RLF

RLF


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

Quote:
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


Reply With Quote
  #5  
Old   
Russell Fields
 
Posts: n/a

Default Re: script to delete old backup jobs - 11-25-2008 , 01:07 PM


KPoku

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

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

RLF

RLF


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

Quote:
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


Reply With Quote
  #6  
Old   
Russell Fields
 
Posts: n/a

Default Re: script to delete old backup jobs - 11-25-2008 , 01:07 PM


KPoku

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

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

RLF

RLF


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

Quote:
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


Reply With Quote
  #7  
Old   
Russell Fields
 
Posts: n/a

Default Re: script to delete old backup jobs - 11-25-2008 , 01:07 PM


KPoku

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

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

RLF

RLF


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

Quote:
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


Reply With Quote
  #8  
Old   
Russell Fields
 
Posts: n/a

Default Re: script to delete old backup jobs - 11-25-2008 , 01:07 PM


KPoku

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

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

RLF

RLF


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

Quote:
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


Reply With Quote
  #9  
Old   
Russell Fields
 
Posts: n/a

Default Re: script to delete old backup jobs - 11-25-2008 , 01:07 PM


KPoku

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

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

RLF

RLF


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

Quote:
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


Reply With Quote
  #10  
Old   
Russell Fields
 
Posts: n/a

Default Re: script to delete old backup jobs - 11-25-2008 , 01:07 PM


KPoku

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

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

RLF

RLF


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

Quote:
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


Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top