Prev: SQL 2000 Maintenance Plan question
Next: Run same query on multiple databases.
From: RG on 25 Jun 2010 10:09
I have 2 dbs that reside on two servers respectively. Both dbs have the
same stucture. There is roughly 20 tables in the db. These two tables have
parent/child relationship which is enforced by foreign key constraint. The
parent table has timestamp column. I am looking to offload data older than
3 days from one db to another. By offload, I mean copy the data to remote
db and delete it. This is to take place every day at 3pm. I can put
together a sql agent job which will do this using linked serverr. However,
is there a utility out of the box or a better way to accomlish this?

Thanks in advance


From: Erland Sommarskog on 25 Jun 2010 18:25
RG (nobody(a)nowhere.com) writes:
> I have 2 dbs that reside on two servers respectively. Both dbs have
> the same stucture. There is roughly 20 tables in the db. These two
> tables have parent/child relationship which is enforced by foreign key
> constraint. The parent table has timestamp column. I am looking to
> offload data older than 3 days from one db to another. By offload, I
> mean copy the data to remote db and delete it. This is to take place
> every day at 3pm. I can put together a sql agent job which will do this
> using linked serverr. However, is there a utility out of the box or a
> better way to accomlish this?

Depends on what you mean with out of the box. Since the business
rule for identifying the data is your responsibility, I would not
trust anything that claims to do it for you.

I guess the alternative is to use SQL Server Integration Services for
the task. One advantage with this is that you can avoid the hassle
that linked server buys you.

An important thing is that either you have a transaction in which
you both copy and delete, or you implement the copy operation in
a way it can be redone, if the job does not complete.

By the way, you mention timestamp column. I assume that you are not
talking about the SQL Server data type timestamp here? This data type
has nothing to do with date and time.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: SQL 2000 Maintenance Plan question
Next: Run same query on multiple databases.