TSQL Code

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. 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...

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: directory – This is the directory...

If you’re a DBA like me then you’re probably pretty detail-oriented and like to keep things very organized. For example, when doing database backups, I like to have a specific folder structure to hold the backup files for each database. See below: D:\Backup\Server\Instance\MyDb1 D:\Backup\Server\Instance\MyDb2 D:\Backup\Server\Instance\MyDb3 In order to make this happen automatically, my TSQL backup jobs need to have code that will create folders when a new database is added or when the jobs are initially setup. SQL offers an undocumented extended stored procedures that allow us to create them on the fly; master.sys.xp_create_subdir. Master.sys.xp_create_subdir will create whatever folder listing...