hi,
I am using Microsoft SQL server 2005. I want to move a large set of
data with a specific condition to other database in the same machine.
for example after a period of time I want to archive data by the
condtion of time ( in a specific year ) to other database. that
archive database is going to store the previous data which are
archived.
Am I going to use queries to move the data for each table? or there is
a better way?
what is the best approach to do so?
Thanks in advance,
Ali
Uri Dimant - 30 Sep 2007 14:42 GMT
Ali
What is large set of data means?
You can have a job which does an INSERT statement every day ( that's
probably is not too large set fo data) or
INSERT INTO......dbname.dbo.tabl and having properly created indexes on the
source tables
> hi,
> I am using Microsoft SQL server 2005. I want to move a large set of
[quoted text clipped - 12 lines]
>
> Ali
Jeje - 30 Sep 2007 14:58 GMT
do you want to COPY or MOVE the data?
if it's a copy you can use the synchronization feature of SQL Server. this
process take a copy of the source database at a regular basis (up to a
realtime synchonization)
if you want to control everything (like which rows you want to copy by
applying filters) and/or want to delete the source data (so moving the
data):
you can do it by running SQL scripts or using SSIS
so you have to write the SQL statements required to copy the expected rows
and delete them in the source if you want to remove them.
> hi,
> I am using Microsoft SQL server 2005. I want to move a large set of
[quoted text clipped - 12 lines]
>
> Ali
Dan Guzman - 30 Sep 2007 15:02 GMT
Why are you archiving data? If the purpose is manageability, you might
consider partitioning instead of moving data to another database (assuming
SQL 2005 Enterprise Edition). You can also use a partition switch to
efficiently delete or insert mass amounts of data by partition as part of
your move process.
You'll need to use queries to extract and delete data if partitioning is not
an option. Bulk insert (especially minimally logged) is faster than INSERT
when large amounts of data are involved. A common technique to load large
amounts of data is to remove indexes on the destination table, perform a
minimally logged bulk insert (perhaps in a SSIS package) and then recreate
indexes and constraints.

Signature
Hope this helps.
Dan Guzman
SQL Server MVP
> hi,
> I am using Microsoft SQL server 2005. I want to move a large set of
[quoted text clipped - 12 lines]
>
> Ali
Ali - 30 Sep 2007 16:37 GMT
On Sep 30, 5:02 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> Why are you archiving data? If the purpose is manageability, you might
> consider partitioning instead of moving data to another database (assuming
[quoted text clipped - 31 lines]
>
> > Ali
Thanks for the answers. so I think I'd better use partitioning than
copying or moving them to other database.
My reason was the speed of retrieving data. I think partitioning can
do the Job.
Thanks,
Ali
Dan Guzman - 30 Sep 2007 19:21 GMT
> My reason was the speed of retrieving data. I think partitioning can
> do the Job.
Partitioning will help manageability but not necessarily performance. The
key to data retrieval performance is indexing and query tuning. Appropriate
indexes reduce the amount of data that needs to be touched without
physically moving data data via archiving or partitioning.

Signature
Hope this helps.
Dan Guzman
SQL Server MVP
> On Sep 30, 5:02 pm, "Dan Guzman" <guzma...@nospam-
> online.sbcglobal.net> wrote:
[quoted text clipped - 46 lines]
> Thanks,
> Ali