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.

maintenance plan wizard - security question, possible bug?

Hi All,
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way to
specify that (concering that I get IS package, very hard to modify anything)?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:
> Pedja
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000).
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed the
> password
> In other words , your SQL Agent runs under the same account as you logged ,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> > Hi All,
> > I am trying to create maintenance plan for updating statistics, checking
> > database integrity and database backup, using wizard. First of all, I
> > don't
> > see any option for how long do I want to keep backup files on the system
> > (like we had on 2000). I find it very inconvinient. Is there any other way
> > to
> > specify that (concering that I get IS package, very hard to modify
> > anything)?
> > More important question is regarding security issue with this wizard: I
> > have
> > only 'sa' access to the server (not member of local admin group, and I'm
> > not
> > able to use windows authentication). Each month we change 'sa' password on
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> > am I
> > missing something?
> > Thanks in advance,
> > Pedja
> >
> > P.S. The same thing would happen even if I created maintenance plan
> > manually, because I would have to create connection objects manually (and
> > specify 'sa' again)...
>
>