Managed Procedure to automate archiving files in a database

I need to archive files in a database by checking an archive date for the file contained in a field in a table of a database, if the archive date is greater than todays date then archive the file by moving it to an archive folder. I am thinking the best way might be to use a manged stored procedure, but I also need to run this procedure once every 24 hours at about midnight so how would I do thi? Another way might be by using DTS or something. Has someone else done this and how did they go about it?

Hi,

You might want to have a look atJobsin sql server. You are able setup jobs to run at set intervals (in your case, midnight).

With moving archived files into a different directory u can consider usingxp_cmdshell

eg. EXECxp_cmdshell 'copy c:\test.txt d:\archived\text.txt --this is equivelent to running this in command prompt.

If you dont like this idea then consider writing aWindows Service.