Apr 022013
 

With opening day of Major League Baseball season finally here, I thought I’d take the time to cover two of my favorite topics…SQL Server and softball.  Have you ever thought about how you can use SQL Server in conjunction with softball? Ok, so maybe you haven’t, but I have.  I have been managing a slow-pitch softball team, the Sons of Pitches, for the past 5 years.  Yes, I did say “slow-pitch”.  My friends and I have already passed the peak of our physical ability, so we use it as an excuse to get together and have a little fun.

As a DBA, I’m big on keeping track of metrics, so naturally this spills over into my extracurricular activities.  For each softball game, the league requires us to keep score, but in addition to that I like to keep individual player stats.  Why would I want to do this?  For benchmarking, of course!  How will I ever tell if my players are getting better or worse without it?  So to save myself a lot of time each week, I created a SQL Server database to keep track of all the stats.  Each week, all I have to do is enter the stats and then generate a report by running a few TSQL scripts.  Below is a sample of the score book that I keep for each game.  This is the only manual piece.  Everything else is automated by SQL Server.

Let’s start by creating our database and then a Games table to hold all of the data.
CREATE DATABASE Softball;
GO
USE Softball;
GO
CREATE TABLE dbo.Games(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [GameDate] DATE DEFAULT(GETDATE()) NOT NULL,
    [SeasonId] INT NOT NULL,
    [SeasonName] VARCHAR(50) NOT NULL,
    [GameNumber] SMALLINTNOT NULL,
    [BattingOrder] SMALLINTNOT NULL,
    [Roster] SMALLINT NOT NULL,
    [Name] VARCHAR(50) NOT NULL,
    [PA] FLOAT NOT NULL,
    [AB]  AS ([PA]-([SACf]+[BB])),
    [H]  AS ((([1B]+[2B])+[3B])+[HR]),
    [1B] FLOAT NOT NULL,
    [2B] FLOAT NOT NULL,
    [3B] FLOAT NOT NULL,
    [HR] FLOAT NOT NULL,
    [ROE] FLOAT NOT NULL,
    [SACf] FLOAT NOT NULL,
    [BB] FLOAT NOT NULL,
    [K] FLOAT NOT NULL,
    [RBI] FLOAT NOT NULL,
    [RUNS] FLOAT NOT NULL
);
GO
ALTER TABLE dbo.Games
    ADD CONSTRAINT PK_Games PRIMARY KEY CLUSTERED (Id,GameDate);
GO
Let’s backup for one minute.  For those of you who are not familiar with baseball (or softball) scoring; here’s a quick break down of what each abbreviation means.  These are the definitions for my softball scoring.  Some baseball statistics are omitted, because they are not relevant in our league.
PA = Plate Appearance: number of times that player appeared in the batter’s box.
AB = At Bat: plate appearances, not including walks or sacrifices.
H = Hit: number of times a batter safely reached a base w/o an error by the defense.
1B = Single: number of times a batter safely reached first base w/o an error by the defense.
2B = Double: number of times a batter safely reached second base w/o an error by the defense.
3B = Triple: number of times a batter safely reached third base w/o an error by the defense.
HR = Home Run: number of times a batter safely reached all four bases w/o an error by the defense.
ROE = Reached on Error: number of times a batter safely reached a base with an error by the defense.
SACf = Sacrifice Fly: Fly ball hit the outfield that was caught for an out, but allowed a base runner to advance.
BB = Base on Balls (aka Walk): number of times a batter did not swing at four pitches outside the strike zone, and was awarded first base by the umpire.
K = Strike Out: number of times a third strike is called or swung at and missed, or hit foul when the batter already had two strikes.
RBI = Run Batted In: number of runners who scored as a result of the batters’ action.
RUNS = Runs Scored: number of times a runner crossed home plate.
BA = Batting Average: Hits (H) divided by At Bats (AB).
OB = On Base Percentage: number of times a batter reached base (H + AB) divided by (AB + BB + SACf).
SLUG = Slugging Average: number of bases achieved (1B+2B*2+3B*3+HR*4) divided by At Bats (AB). One base is for each 1B, two bases for each 2B, three bases for each 3B, and four bases for each HR.
OPS = On Base Percentage Plus Slugging: sum of batter’s OB + SLUG.
The Games table will hold one row for each player and his stats for that game.  All columns will need to be manually entered, except for H and AB.  The hits and at bats are two of the various computed columns.  What we have now is the raw data stored in the database.
SELECT * FROM Games;
GO

Next, need to build some views to add the other calculated fields: BA, OB, SLUG, and OPS, as well as present the data in a more readable format.  Since Hits (H) and At Bats (AB) are already computed columns, we need to use a view in order to use them in another computed column.
CREATE VIEW dbo.PlayerStats
AS
SELECT
     [SeasonId]
    ,[SeasonName]
    ,[GameNumber]
    ,[BattingOrder]
    ,[Roster]
    ,[Name]
    ,[PA]
    ,[AB]
    ,[H]
    ,[1B]
    ,[2B]
    ,[3B]
    ,[HR]
    ,[ROE]
    ,[SACf]
    ,[BB]
    ,[K]
    ,[RBI]
    ,[RUNS]
    ,CONVERT(DECIMAL(5,3),(ISNULL(([H]/NULLIF([AB],0)),0))) AS [BA]
    ,CONVERT(DECIMAL(5,3),(ISNULL((([H]+[BB])/(NULLIF([AB]+[BB]+[SACf],0))),0))) AS [OB]
    ,CONVERT(DECIMAL(5,3),(ISNULL((([1B]+([2B]*2)
        +([3B]*3)+([HR]*4))/NULLIF([AB],0)),0))) AS [SLUG]
    ,CONVERT(DECIMAL(5,3),(ISNULL((([H]+[BB])/NULLIF([AB]+[BB]+[SACf],0)),0)
        +ISNULL((([1B]+([2B]*2)+([3B]*3)+([HR]*4))/NULLIF([AB],0)),0))) AS [OPS]
FROM dbo.Games;
GO
SELECT * FROM PlayerStats;
GO

Next, we can create a view for the season stats.
CREATE VIEW dbo.SeasonStats
AS
SELECT
     [SeasonId]
    ,[SeasonName]
    ,[Roster]
    ,[Name]
    ,COUNT(GameNumber) as [Games]
    ,SUM([PA]) AS [PA]
    ,SUM([AB]) AS [AB]
    ,SUM([H]) AS [H]
    ,SUM([1B]) AS [1B]
    ,SUM([2B]) AS [2B]
    ,SUM([3B]) AS [3B]
    ,SUM([HR]) AS [HR]
    ,SUM([ROE]) AS [ROE]
    ,SUM([SACf]) AS [SACf]
    ,SUM([BB]) AS [BB]
    ,SUM([K]) AS [K]
    ,SUM([RBI]) AS [RBI]
    ,SUM([RUNS]) AS [RUNS]
    ,ISNULL(CONVERT(DECIMAL(5,3),(SUM([H])/NULLIF(SUM([AB]),0))),0) AS [BA]
    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([H])+SUM([BB]))
        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)))),0) AS [OB]
    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([1B])+(SUM([2B])*2)
        +(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0))),0) AS [SLUG]
    ,CONVERT(DECIMAL(5,3),ISNULL((SUM([H])+SUM([BB]))
        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)),0)+ISNULL((SUM([1B])
        +(SUM([2B])*2)+(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0),0)) AS [OPS]
FROM dbo.PlayerStats
GROUP BY [SeasonId],[SeasonName],[Roster],[Name];
GO
This will allow us to view each player’s stats for a given season.
SELECT * FROM SeasonStats
WHERE SeasonId = 12
ORDER BY [OB] DESC, [BA] DESC;
GO

In addition to season stats, we also need a view for career stats.
CREATE VIEW dbo.CareerStats
AS
SELECT
     [Roster]
    ,[Name]
    ,COUNT(GameNumber) as [Games]
    ,SUM([PA]) AS [PA]
    ,SUM([AB]) AS [AB]
    ,SUM([H]) AS [H]
    ,SUM([1B]) AS [1B]
    ,SUM([2B]) AS [2B]
    ,SUM([3B]) AS [3B]
    ,SUM([HR]) AS [HR]
    ,SUM([ROE]) AS [ROE]
    ,SUM([SACf]) AS [SACf]
    ,SUM([BB]) AS [BB]
    ,SUM([K]) AS [K]
    ,SUM([RBI]) AS [RBI]
    ,SUM([RUNS]) AS [RUNS]
    ,ISNULL(CONVERT(DECIMAL(5,3),(SUM([H])/NULLIF(SUM([AB]),0))),0) AS [BA]
    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([H])+SUM([BB]))
        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)))),0) AS [OB]
    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([1B])+(SUM([2B])*2)
        +(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0))),0) AS [SLUG]
    ,CONVERT(DECIMAL(5,3),ISNULL((SUM([H])+SUM([BB]))
        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)),0)+ISNULL((SUM([1B])
        +(SUM([2B])*2)+(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0),0)) AS [OPS]
FROM dbo.PlayerStats
GROUP BY [Roster],[Name];
GO
SELECT * FROM CareerStats
ORDER BY [OB] DESC, [BA] DESC;
GO

Next, we can create a view for the individual player stats.
CREATE VIEW dbo.IndividualStats
AS
SELECT
     CONVERT(VARCHAR,[SeasonId]) AS [SeasonId]
    ,[SeasonName]
    ,[Roster]
    ,[Name]
    ,COUNT(GameNumber) AS [Games]
    ,SUM([PA]) AS [PA]
    ,SUM([AB]) AS [AB]
    ,SUM([H]) AS [H]
    ,SUM([1B]) AS [1B]
    ,SUM([2B]) AS [2B]
    ,SUM([3B]) AS [3B]
    ,SUM([HR]) AS [HR]
    ,SUM([ROE]) AS [ROE]
    ,SUM([SACf]) AS [SACf]
    ,SUM([BB]) AS [BB]
    ,SUM([K]) AS [K]
    ,SUM([RBI]) AS [RBI]
    ,SUM([RUNS]) AS [RUNS]
    ,ISNULL(CONVERT(DECIMAL(5,3),(SUM([H])/NULLIF(SUM([AB]),0))),0) AS [BA]
    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([H])+SUM([BB]))
        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)))),0) AS [OB]
    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([1B])+(SUM([2B])*2)
        +(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0))),0) AS [SLUG]
    ,CONVERT(DECIMAL(5,3),ISNULL((SUM([H])+SUM([BB]))
        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)),0)+ISNULL((SUM([1B])
        +(SUM([2B])*2)+(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0),0)) AS [OPS]
FROM dbo.PlayerStats
GROUP BY [SeasonId],[SeasonName],[Roster],[Name];
GO
This will allow us to view the stats from an individual player as well as union all of the careers stats.
SELECT * FROM IndividualStats
WHERE Roster = 4
UNION ALL
SELECT ‘CAREER STATS’,,* FROM CareerStats
WHERE Roster = 4;
GO

Finally, we can create one last view for the individual player stats by season.
CREATE VIEW dbo.IndividualTeamStats
AS
SELECT
     CONVERT(varchar,[SeasonId]) AS [SeasonId]
    ,[SeasonName]
    ,[Roster]
    ,[Name]
    ,CONVERT(varchar,[GameNumber]) AS [Games]
    ,SUM([PA]) AS [PA]
    ,SUM([AB]) AS [AB]
    ,SUM([H]) AS [H]
    ,SUM([1B]) AS [1B]
    ,SUM([2B]) AS [2B]
    ,SUM([3B]) AS [3B]
    ,SUM([HR]) AS [HR]
    ,SUM([ROE]) AS [ROE]
    ,SUM([SACf]) AS [SACf]
    ,SUM([BB]) AS [BB]
    ,SUM([K]) AS [K]
    ,SUM([RBI]) AS [RBI]
    ,SUM([RUNS]) AS [RUNS]
    ,ISNULL(CONVERT(DECIMAL(5,3),(SUM([H])/NULLIF(SUM([AB]),0))),0) AS [BA]
    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([H])+SUM([BB]))
        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)))),0) AS [OB]
    ,ISNULL(CONVERT(DECIMAL(5,3),((SUM([1B])+(SUM([2B])*2)
        +(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0))),0) AS [SLUG]
    ,CONVERT(DECIMAL(5,3),ISNULL((SUM([H])+SUM([BB]))
        /(NULLIF(SUM([AB])+SUM([BB])+SUM([SACf]),0)),0)+ISNULL((SUM([1B])
        +(SUM([2B])*2)+(SUM([3B])*3)+(SUM([HR])*4))/NULLIF(SUM([AB]),0),0)) AS [OPS]
FROM dbo.Games
GROUP BY [SeasonId],[SeasonName],[Name],[Roster],[GameNumber]
UNION
SELECT
     ‘SEASON STATS’
    ,[SeasonName]
    ,[Roster]
    ,[Name]
    ,
    ,[PA]
    ,[AB]
    ,[H]
    ,[1B]
    ,[2B]
    ,[3B]
    ,[HR]
    ,[ROE]
    ,[SACf]
    ,[BB]
    ,[K]
    ,[RBI]
    ,[RUNS]
    ,[BA]
    ,[OB]
    ,[SLUG]
    ,[OPS]
FROM dbo.SeasonStats;
GO
This allows us to get a line by line view of what each player did in each game throughout the season as well as have his aggregated season stats.
SELECT * FROM IndividualStatsTeam
WHERE SeasonName = ‘Spring 2012’
ORDER BY Roster;
GO

As you can see, two of the things I’m passionate about, SQL Server and softball, work very well together.  Although this was done as a hobby, I use it every single week.  What kind of hobbies can you use SQL Server for?
And yes, these are the actual stats of my team.  We play just about every Tuesday night during the spring, summer, and fall for the SportsLink league in Charlotte, NC.  We’re not the best team, but we’re also nowhere near the worst, and we’ve managed to win the league championship twice.  If you happen to be in Charlotte for the SQLPass Summit in October, maybe you can find some time of come watch my team play that Tuesday night.  I can’t guarantee that it will be good or bad, but it’s definitely entertaining.
Dec 312012
 

In a previous blog post, I demonstrated how you can use an undocumented stored procedure, master.sys.xp_dirtree, to list all files in a folder.  The previous posting mostly talked about how the stored procedure worked and the output it generated.  For this posting, I have expanded upon the script to parse all subdirectories and list all files in all subfolders from the starting point.

Just for a quick review, xp_dirtree has three parameters:
  1. directory – This is the directory you pass when you call the stored procedure; for example ‘D:Backup’.
  2. depth  – This tells the stored procedure how many subfolder levels to display.  The default of 0 will display all subfolders.
  3. isfile – This will either display files as well as each folder.  The default of 0 will not display any files.

Now for the updated script:

DECLARE
       @BasePath varchar(1000)
      ,@Path varchar(1000)
      ,@FullPath varchar(2000)
      ,@Id int;
–This is your starting point.
SET @BasePath = ‘D:Backup’;
–Create a temp table to hold the results.
IF OBJECT_ID(‘tempdb..#DirectoryTree’)IS NOT NULL
      DROP TABLE#DirectoryTree;
CREATE TABLE #DirectoryTree (
       id int IDENTITY(1,1)
      ,fullpath varchar(2000)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);
–Create a clustered index to keep everything in order.
ALTER TABLE #DirectoryTree
ADD CONSTRAINT PK_DirectoryTree PRIMARYKEY CLUSTERED (id);
–Populate the table using the initial base path.
INSERT#DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @BasePath,1,1;
UPDATE#DirectoryTree SET fullpath = @BasePath;
–Loop through the table as long as there are still folders to process.
WHILE EXISTS (SELECT id FROM #DirectoryTree WHEREisfile = 0)
BEGIN
      –Select the first row that is a folder.
      SELECT TOP (1)
             @Id = id
            ,@FullPath = fullpath
            ,@Path = @BasePath + + subdirectory
      FROM #DirectoryTree WHERE isfile = 0;
      IF @FullPath = @Path
      BEGIN
            –Do this section if the we are still in the same folder.
            INSERT #DirectoryTree (subdirectory,depth,isfile)
            EXEC master.sys.xp_dirtree @Path,1,1;
            UPDATE #DirectoryTree
            SET fullpath = @Path
            WHERE fullpath IS NULL;
            –Delete the processed folder.
            DELETE FROM #DirectoryTree WHEREid = @Id;
      END
      ELSE
      BEGIN
            –Do this section if we need to jump down into another subfolder.
            SET @BasePath = @FullPath;
            –Select the first row that is a folder.
            SELECT TOP (1)
                   @Id = id
                  ,@FullPath = fullpath
                  ,@Path = @BasePath + + subdirectory
            FROM #DirectoryTree WHERE isfile = 0;
            INSERT #DirectoryTree (subdirectory,depth,isfile)
            EXEC master.sys.xp_dirtree @Path,1,1;
            UPDATE #DirectoryTree
            SET fullpath = @Path
            WHERE fullpath IS NULL;
            –Delete the processed folder.
            DELETE FROM #DirectoryTree WHEREid = @Id;
      END
END
–Output the results.
SELECT fullpath + + subdirectory AS ‘CompleteFileList’
FROM#DirectoryTree
ORDER BY fullpath,subdirectory;
–Cleanup.
IF OBJECT_ID(‘tempdb..#DirectoryTree’)IS NOT NULL
      DROP TABLE#DirectoryTree;
GO

This code will process one folder level at a time since we’re specifying 1 for the depth parameter.  For each folder in the #DirectoryTree table, we get a list of files and subfolders and insert it to the #DirectoryTree table. 
INSERT#DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @Path,1,1;

If it’s a folder, then it the file parameter will be 0 and the WHILE loop will process once it gets further down the loop.  The folder needs a clustered index to keep all the rows in order.

ALTER TABLE #DirectoryTree
ADD CONSTRAINT PK_DirectoryTree PRIMARYKEY CLUSTERED (id);

After each folder is processed, the folder row is deleted from the table.  This is necessary so the WHILE loop doesn’t turn into an infinite loop.
DELETE FROM #DirectoryTree WHEREid = @Id;

The full path is being saved for output later, so comparing it to the current base path will let us know if we need to update the @BasePathparameter.
IF @FullPath = @Path
      –Continue processing the same folder.
ELSE
      SET @BasePath = @FullPath;
      –Start processing the next subfolder.

This is how we can keep looping through each subfolder until each one has been processed.

The final step is to output the results.

SELECT fullpath + + subdirectory AS ‘CompleteFileList’
FROM#DirectoryTree
ORDER BY fullpath,subdirectory;
I have gotten pretty good performance out of this script.  Even traversing the C:Windows folder on my test VM, I can get the report to return all 62000+ files in 14000+ folders within about 6 minutes. Hopefully this example will prove to be helpful for your everyday tasks.  Enjoy!

Dec 112012
 
You may have noticed in my code examples, I always include a semicolon “;” at the end of my TSQL statements.  If you’re not doing this, then you need to start now.  Actually, if you’re not writing TSQL code as a DBA then you need to start now.
Starting in SQL Server 2005, Microsoft introduced the TSQL terminator.  This syntax is not required for most statements, but at some point in the future it will be required for all.  You can read the details on MSDN.  As with all new changes to SQL, you should be future-proofing your code.
Some statements already require the use of the terminator.  One of the best examples is the use of a Common Table Expression (CTE).  If you attempt to run this code below, you will get a syntax error.
USEAdventureWorks2008R2
WITH CTE
AS (
      SELECT
             P.FirstName
            ,P.LastName
            ,E.VacationHours
      FROM HumanResources.Employee E
            JOIN Person.Person P
                  ON E.BusinessEntityID = P.BusinessEntityID
)
SELECT * FROM CTE
WHEREVacationHours > 40;
GO

Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
To start defining a CTE, the WITH statement must be the first statement in a batch or the preceding statement must be terminated with a semicolon, even if it’s something as simple as a USE DATABASE statement.
Add the semicolon to the end of the USE statement and rerun, and you’ll get the results without any errors.
USEAdventureWorks2008R2;
WITH CTE
AS (
      SELECT
             P.FirstName
            ,P.LastName
            ,E.VacationHours
      FROM HumanResources.Employee E
            JOIN Person.Person P
                  ON E.BusinessEntityID = P.BusinessEntityID
)
SELECT * FROM CTE
WHEREVacationHours > 40;
GO

(178 row(s) affected)

This is one of the easiest code changes you can make, so starting adding it to your code today and save yourself the hassle of rewriting it tomorrow.
Nov 212012
 

Continuing on my recent trend of using undocumented stored procedures, this week I thought we can cover using xp_delete_file to purge old backup files from a folder.  This is stored procedure used by the maintenance plans to clean up old backup files, but it makes for a handy purge tool when creating your own backup scripts.

Let’s suppose we have a simple script (taken from a recent post on stackoverflow.com) to backup all databases on a server.
DECLARE @name VARCHAR(50); — Database name

DECLARE @path VARCHAR(256); — Path for backup files
DECLARE @fileName VARCHAR(256); — Filename for backup
DECLARE @fileDate VARCHAR(20); — Used for file name
— Path to backups.
SET @path = ‘D:Backup’;
— Get date to include in file name.
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);
— Dynamically get each database on the server.
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’);
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;
— Loop through the list to backup each database.
WHILE @@FETCH_STATUS = 0
BEGIN 
      — Build the path and file name.
      SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’;
      — Backup the database.
      BACKUP DATABASE@name TO DISK = @fileName WITH INIT;
      — Loop to the next database.
      FETCH NEXTFROM db_cursor INTO@name;
END 
— Clean up.
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

The script will create a new database backup file of every database on the server each time it’s executed.  However, the path may not have unlimited disk space.  This is where we need to use xp_delete_file.
Xp_delete_file takes a five parameters:

  1. File Type = 0 for backup files or 1 for report files.
  2. Folder Path = The folder to delete files.  The path must end with a backslash “”.
  3. File Extension = This could be ‘BAK’ or ‘TRN’ or whatever you normally use.
  4. Date = The cutoff date for what files need to be deleted.
  5. Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.
To incorporate this into our backup script, we just need to add another parameter to define the cutoff date.

DECLARE @DeleteDate DATETIME = DATEADD(wk,-1,GETDATE());

In this example, we want to purge any backup files that are more than one week old and have a file extension of ‘BAK’.

Next we just need to add the xp_delete_file after the backup loop completes.

EXEC master.sys.xp_delete_file 0,@path,‘BAK’,@DeleteDate,0;
We only need to execute xp_delete_file once with subfolder=0 parameter since all backup files are being stored in the same folder.  If there was a separate subfolder for each database, then we’d change the subfolder parameter to 1 to delete files from each of those database subfolders.
The updated script would look like this.
DECLARE @name VARCHAR(50); — Database name

DECLARE @path VARCHAR(256); — Path for backup files
DECLARE @fileName VARCHAR(256); — Filename for backup
DECLARE @fileDate VARCHAR(20); — Used for file name
DECLARE@DeleteDate DATETIME =DATEADD(wk,-1,GETDATE()); — Cutoff date
— Path to backups.
SET @path = ‘D:Backup’;
— Get date to include in file name.
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);
— Dynamically get each database on the server.
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’);
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;
— Loop through the list to backup each database.
WHILE @@FETCH_STATUS = 0
BEGIN 
      — Build the path and file name.
      SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’;
      — Backup the database.
      BACKUP DATABASE@name TO DISK = @fileName WITH INIT;
      — Loop to the next database.
      FETCH NEXTFROM db_cursor INTO@name;
END 
— Purge old backup files from disk.
EXEC master.sys.xp_delete_file 0,@path,‘BAK’,@DeleteDate,0;
— Clean up.
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

Don’t let the stored procedure file name, xp_delete_file, fool you.  Microsoft has some internal code that only allows for the deletion of database and transaction log backup files.  The stored procedure cannot be used to delete any other type of file.
Yes, it really is that simple.
Nov 132012
 

UPDATED — Dec 31, 2012 — Be sure to read Part 2 of this post discussing xp_dirtree.

Last week I blogged about how to use an undocumented stored procedures to create folders.  This week we need to do the opposite.  We need to get a list of all files from a subfolder in order to process them.  In order to do that, we’ll use another undocumented extended stored procedure; master.sys.xp_dirtree.  This stored procedure will display a list of every folder, every subfolder, and every file for path you give it.

Xp_dirtree has three parameters: 
  1. directory – This is the directory you pass when you call the stored procedure; for example ‘D:Backup’.
  2. depth  – This tells the stored procedure how many subfolder levels to display.  The default of 0 will display all subfolders.
  3. file – This will either display files as well as each folder.  The default of 0 will not display any files.

For today’s example, we just want to display all of our backup files (*.BAK) in a particular folder. We need to all of the other parameters in order to show the files as well as any subfolders.

EXEC master.sys.xp_dirtree ‘D:BackupTRON4TEST2MyDb1’,0,1;

The output below will show us each subfolder and every file for the given directory.
We’re only concerned about the BAK files.  So we’ll need to create a temp table to hold the output of xp_dirtree.  Then we can select only the rows that are an actual file (isfile = 1) and that have a file extension of BAK.

IF OBJECT_ID(‘tempdb..#DirectoryTree’)IS NOT NULL
      DROP TABLE#DirectoryTree;
CREATE TABLE #DirectoryTree (
       id int IDENTITY(1,1)
      ,subdirectory nvarchar(512)
      ,depth int
      ,isfile bit);
INSERT#DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree ‘D:BackupTRON4TEST2MyDb1’,1,1;
SELECT * FROM #DirectoryTree
WHERE isfile =AND RIGHT(subdirectory,4) = ‘.BAK’
ORDER BYid;
GO

Below is the output.
This example only shows the files for a specific subfolder, which is used to get a list of files that are needed for purging before another database backup runs.  This stored procedure also can accept UNC paths (\ServerFolder) to get a list of files and subfolders from a network drive.  In a future post, I will add on to this script to show all files in all subfolders for a given directory.  Until then, play around with xp_dirtree and its parameters to see the different outputs.  You may be able to incorporate this into your own code.