Copy a file from one dir to another by date

Question

Hi, I have this SQL Job (in SQL Server 2005) that creates a backup every six(6) hours, the backup's filename is based on the timestamp so that it will create a unique filename(dbname_yyyymmddhhmmss.bak), Now my question is, How would I know using xp_cmdshell if the file is three day old and based on my script I want to delete backup(.bak) that is a three day old. Can someone out there help me, thanks in advance. Cheers!

Answer 1

Hi,

This is not really a task that is suited to xp_cmdshell. Enabling this feature within SQL Server also has security implications.

What you are looking to achieve would be much better suited to SQL Server Integration Services (SSIS). There are components available that can be used to manage and perform your backups, as well as File System Task components that can be used to move and delete data.

You could use a combination of a File System Task, variables and expressions in order to retrieve the backup filename, extract the date component and determine how old the file is. You can then take appropriate action on the file.

I hope this helps but please feel free to pose further questions if you require additional information.

Cheers,John

Answer 2

This is not really the answer to your questions, but you could do this directly in SqlServer 2005 with a Maintenance Plan (Object Explorer -> Management -> Maintenance Plans).

I usually create one Maintenance Plan including two tasks: One "Maintenance Cleanup Task" which deletes old backups after x days, followed by a "Back Up Database Task".

Answer 3

You could write a .NET assembly and call it from within SQL Server. It would be fairly easy to write it so that a table valued function returns all of the files in a certain directory with filename and file datestamp.

Answer 4

I agree that xp_cmdshell is not the best alternative for the job. If you're like me and you don't like/trust maintenance plans, you can probably write a C# console application, where file system support is much stronger than what you can do in DOS (or using T-SQL to parse the output of xp_cmdshell 'DIR ...'), and then schedule that in a windows scheduled task so that you don't have to worry about escalation of privileges from the SQL Server service/proxy account. While it's nice to put everything in one package, you don't always want the guy who changes your oil to make you a quiche.