Bulk Move

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,
AliAli
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
"Ali" <nikzad.a@.gmail.com> wrote in message
news:1191158805.093132.214280@.22g2000hsm.googlegroups.com...
> 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
>|||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.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:1191158805.093132.214280@.22g2000hsm.googlegroups.com...
> 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
>|||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.
"Ali" <nikzad.a@.gmail.com> wrote in message
news:1191158805.093132.214280@.22g2000hsm.googlegroups.com...
> 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
>|||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
> 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.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ali" <nikza...@.gmail.com> wrote in message
> news:1191158805.093132.214280@.22g2000hsm.googlegroups.com...
> > 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
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|||> 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.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ali" <nikzad.a@.gmail.com> wrote in message
news:1191166646.655159.267330@.g4g2000hsf.googlegroups.com...
> 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
>> 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.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ali" <nikza...@.gmail.com> wrote in message
>> news:1191158805.093132.214280@.22g2000hsm.googlegroups.com...
>> > 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
> 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
>