Rotating Header Image

xp_delete_file vs. PowerShell

So I’m a firm believer in the KISS theory.


No not THAT KISS… the Keep It Simple Stupid theory.

So when it comes to backups… if I’m not needing to “trick them out” or do anything custom I don’t have any problems with setting up a nice and easy maintenance plan and calling it a day.  I know there are some strong opinions out there that you should avoid Maintenance Plans like the plague and I agree that in some situations that they are not your best option but I don’t like to write them off altogether.

Sorry… that went longer then expected…ok back on topic…

xp_delete_file… So I needed to build a custom backup job because with the size of the database I would get greater performance by backing up to multiple backup files (an option that is not available in a maintenance plan).  Now normally I would just attach the maintenance cleanup task to another maintenance plan but I thought “Hey… let’s get crazy” I’m going to click on that View T-SQL Button and see what happens.


Well isn’t that easy?

EXECUTE master.dbo.xp_delete_file 0,N'\\ServerName\Folder',N'BAK','2009-08-13T08:14:17',1

Could it REALLY be that simple?

Hey… what’s with that “0” at the front and the “1” at the end?  What are those parameters for?

I BINGed the question (OK I really GOOGLEd it… sorry Microsoft I just can’t break my google ties) and found that the first arguement determines if it’s a backup file or a report file (0 and 1 respectively) and the last arguement determines whether or not to include first level subfolders (0 – no 1 – yes)

Great… it looks like I’ve found my solution….. but wait for it…… wait for it……..

ENTER PowerShell….


(Great picture BTW by Joe Brinkman – The Accidental Geek )

OK admittedly I’m one of the 42% on Brent Ozar’s Powershell Poll that don’t currently use PowerShell but want to.  So I thought here is my chance…

 I started out simple:

Built myself a nice little .ps1 file with this:

$directory = “C:\”
Get-ChildItem $directory

Ran that bad boy and low and behold… I’m a PowerShell developer 🙂

So I thought… let’s add my network share location for my backup files:

$directory = “\\servername\folder”
Get-ChildItem $directory

Ran that and I’m back to being a PowerShell failure:

Get-ChildItem : Cannot find path ‘\\servername\folder’ because it does not exist.

Turns out you need to map a drive for a network share (or at least that’s how I solved my problem… please comment if there is a more elegant solution):

$directory = “\\ServerName\Folder”
New-PSDrive -name X -psprovider FileSystem -root $directory
Get-ChildItem X:\

 Now we’re cooking with fire 🙂

Next I changed my Get-ChildItem statement to filter only the *.BAK files and I set it to recursively go through subdirectories:

$directory = “\\ServerName\Folder”
New-PSDrive -name X -psprovider FileSystem -root $directory
Get-ChildItem X:\ -include *.BAK -recurse

This is getting good now 🙂 Now I wanted to only get the *.BAK files that are older then 4 hours… this took some googling to get my syntax right:

$directory = “\\ServerName\Folder”
New-PSDrive -name X -psprovider FileSystem -root $directory
Get-ChildItem X:\ -include *.BAK -recurse | where {((get-date)-$_.creationTime).hours -ge 4}

 And finally I wanted to delete these files so we “pipe in” a remove-item and we’re done:

$directory = “\\ServerName\Folder”
New-PSDrive -name X -psprovider FileSystem -root $directory
Get-ChildItem X:\ -include *.BAK -recurse | where {((get-date)-$_.creationTime).hours -ge 4} | remove-item -force -recurse

And if you wanted to add your transaction log backups as well you could copy/paste your Get-ChildItem and change accordingly

Get-ChildItem X:\ -include *.TRN -recurse | where {((get-date)-$_.creationTime).days -ge 1.5} | remove-item -force -recurse

And there you go… I’m not sure if this means I’m now a PowerShell user but I’m definitely starting to drink the KoolAid for sure.


Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon


  1. Regarding the error you received trying to access a network share from PowerShell might be a permissions issue, as there should not be any PowerShell related impediment to accessing a network share.

    Best of luck with your exploration into PowerShell. Feel free to stop on over to PowerShellCommunity.Org if you have any questions.

  2. Colin Smith says:

    I love powershell and I really think that it is the future of administering any new Microsoft Enterprise level Product including SQL Server. I have written some nice reports and things for my SQL Servers and all of my Monitoring in Powershell. As I am learning more about SQL Server I am adding to my monitoring to make it more robust but it is still in Powershell. I agree with Steven that you should not have any problems doing a get-childitem on a UNC path from Powershell. I do encourage you to do more Powershell as I think that it is an amazing tool.

  3. Friends and family near and far gobbled up my strategically timed, carefully curated posts. I guess it’s no surprise that passive scrolling through someone’s Facebook page to stay up to date on their lives is preferable to just picking up the phone

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.