BCP out/in very large table

Could someone give me some guidance on the fastest way to unload and reload
data from very large tables. I am planning to drop about 30% of columns
(fixed length columns) from few of our largest tables (size ranges from 300
GB to 500 GB each). In order to reclaim space from dropped columns, looks
like only way is to unload the data, recreate the table (with less columns)
and reload the data. Dropping and recreating clustered index didn't reclaim
space ( I am running SQL 2000 sp4).
I am planning to perform following steps:
1. BCP out data
2. Drop/recreate table with only clustered index
3. BCP in data
4. Create non clustered index, foreign key, constraints etc
Do you see any potential problem with exporting such a big size to text
file? I am planning to export the data to multiple files so that when I BCP
in, I can load them concurrently and also planning to use ORDER and TABLOCK
hint. Can I use ORDER and TABLOCK hint when loading data to a table with
clustered index and loading multiple file simultaneously? I am guessing if I
use "queryout" option with "order by" clause on clustered column when bcp
out the data, I should be able to use ORDER hint when loading data in. Let
me know if I am wrong. Also, I am planning to use SQL server Native format.
If you have any experience in unloading/reloading very large table from sql
server, I would love to hear your comments/suggestion.
Instead of BCP in, consider BULK INSERT with parallel streams. See the
following excellent articles for some of the best rpactices recommendations:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx
http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx
http://www.microsoft.com/sql/techinfo/administration/2000/rosetta.doc
Linchi
"james" wrote:

> Could someone give me some guidance on the fastest way to unload and reload
> data from very large tables. I am planning to drop about 30% of columns
> (fixed length columns) from few of our largest tables (size ranges from 300
> GB to 500 GB each). In order to reclaim space from dropped columns, looks
> like only way is to unload the data, recreate the table (with less columns)
> and reload the data. Dropping and recreating clustered index didn't reclaim
> space ( I am running SQL 2000 sp4).
> I am planning to perform following steps:
> 1. BCP out data
> 2. Drop/recreate table with only clustered index
> 3. BCP in data
> 4. Create non clustered index, foreign key, constraints etc
> Do you see any potential problem with exporting such a big size to text
> file? I am planning to export the data to multiple files so that when I BCP
> in, I can load them concurrently and also planning to use ORDER and TABLOCK
> hint. Can I use ORDER and TABLOCK hint when loading data to a table with
> clustered index and loading multiple file simultaneously? I am guessing if I
> use "queryout" option with "order by" clause on clustered column when bcp
> out the data, I should be able to use ORDER hint when loading data in. Let
> me know if I am wrong. Also, I am planning to use SQL server Native format.
> If you have any experience in unloading/reloading very large table from sql
> server, I would love to hear your comments/suggestion.
>
>
sql

BCP out/in very large table

Could someone give me some guidance on the fastest way to unload and reload
data from very large tables. I am planning to drop about 30% of columns
(fixed length columns) from few of our largest tables (size ranges from 300
GB to 500 GB each). In order to reclaim space from dropped columns, looks
like only way is to unload the data, recreate the table (with less columns)
and reload the data. Dropping and recreating clustered index didn't reclaim
space ( I am running SQL 2000 sp4).
I am planning to perform following steps:
1. BCP out data
2. Drop/recreate table with only clustered index
3. BCP in data
4. Create non clustered index, foreign key, constraints etc
Do you see any potential problem with exporting such a big size to text
file? I am planning to export the data to multiple files so that when I BCP
in, I can load them concurrently and also planning to use ORDER and TABLOCK
hint. Can I use ORDER and TABLOCK hint when loading data to a table with
clustered index and loading multiple file simultaneously? I am guessing if I
use "queryout" option with "order by" clause on clustered column when bcp
out the data, I should be able to use ORDER hint when loading data in. Let
me know if I am wrong. Also, I am planning to use SQL server Native format.
If you have any experience in unloading/reloading very large table from sql
server, I would love to hear your comments/suggestion.Instead of BCP in, consider BULK INSERT with parallel streams. See the
following excellent articles for some of the best rpactices recommendations:
http://www.microsoft.com/technet/pr.../19/602142.aspx
http://www.microsoft.com/sql/techin...000/rosetta.doc
Linchi
"james" wrote:

> Could someone give me some guidance on the fastest way to unload and reloa
d
> data from very large tables. I am planning to drop about 30% of columns
> (fixed length columns) from few of our largest tables (size ranges from 30
0
> GB to 500 GB each). In order to reclaim space from dropped columns, looks
> like only way is to unload the data, recreate the table (with less columns
)
> and reload the data. Dropping and recreating clustered index didn't reclai
m
> space ( I am running SQL 2000 sp4).
> I am planning to perform following steps:
> 1. BCP out data
> 2. Drop/recreate table with only clustered index
> 3. BCP in data
> 4. Create non clustered index, foreign key, constraints etc
> Do you see any potential problem with exporting such a big size to text
> file? I am planning to export the data to multiple files so that when I BC
P
> in, I can load them concurrently and also planning to use ORDER and TABLO
CK
> hint. Can I use ORDER and TABLOCK hint when loading data to a table with
> clustered index and loading multiple file simultaneously? I am guessing if
I
> use "queryout" option with "order by" clause on clustered column when bcp
> out the data, I should be able to use ORDER hint when loading data in. Let
> me know if I am wrong. Also, I am planning to use SQL server Native format
.
> If you have any experience in unloading/reloading very large table from sq
l
> server, I would love to hear your comments/suggestion.
>
>

Basic Replication Question

I'm really new to this concept, so i would appreciate a little guidance.
I have a database that is stored on a remote server (shared hosting
environment). Of course the hosting company says they do daily backups, but
I don't want to trust that for myself and our clients.
Is there a way for me to set up something locally in enterprise manager that
replicates those databases to my local copy? I would like for it to sync
whenever there are changes, but it could be a batch process that runs hourly
or something.
thanks,
You could use transactional replication or log-shipping for this. Have a
look at this article for some pros and cons:
http://www.replicationanswers.com/Standby.asp
Probably the main issue will be making sure that there is connectivity and
that it is secure.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)