Copy a file from one directory to another by date

Question

I have this SQL job (in SQL Server 2005) that backs up every six (6) hours, the backup file name is based on the timestamp to create a unique file name (dbname_yyyymmddhhmmss.bak). Now my question is, How can I find out using xp_cmdshell if the file has three days and based on my script I want to delete the backup (.bak), which is three days old. Can someone out there help me, thanks in advance. Hurrah!

Answer 1

This is not really the answer to your questions, but you can do it directly in SqlServer 2005 with a maintenance plan (Object Explorer → Management → Maintenance Plans).

Typically, I create one Maintenance Plan that includes two tasks: One “Maintenance Cleanup Task” that deletes old backups after x days, and then “Database Backup Task”.

Answer 2

, xp_cmdshell - . , , #, , , DOS ( T-SQL xp_cmdshell 'DIR...'), Windows, /- SQL Server. , , , .

Answer 3

, xp_cmdshell. SQL Server .

, , SQL Server (SSIS). , , , .

, , , , . .

, , , , , .

Cheers,

Answer 4

You can write a .NET assembly and call it from SQL Server. It would be quite easy to write it so that the table-valued function returns all files in a specific directory with the file name and file datestamp.