create a table prior to bcping data

I need to do the following;
1. bcp out data from our reporting server
2. Move bcp file to our archival server
Sounds pretty straight forward.. but of course there is
more...
Since we are using this data for archival purposes, we
just want to store the bcp file. My concern is that there
are many schema changes to the source table and I wanted
to make sure that I had the correct schema needed to bcp
that file back into a table, say three years from now.
Is there a option in bcp or bulk copy that would create
the schema for you? Or can this be done via DTS?
Thanks in advance
Susan
Any help would be greatly appreciated.
Thanks
SusanHi,
You can "Generate a script " of all objects and keep it along with the BCP
OUT data.
How to Generate scripts:
1. In Enterprise manager
2. Connect to the SQL server
3. Select the database you need to generate the script
4. Right click All Tasks - "Select the Generate SQL Script" option
5. Click Show all
6. Go to Options Tab - in the Table options select Script INdexes, Triggers
and Primary keys.../
Click the save button and save the file to your hard disk in safe place.
Once you require create a ne database and Execute this script
and BCP IN the the data back.
Thanks
Hari
MCDBA
"Susan" <anonymous@.discussions.microsoft.com> wrote in message
news:1a40701c41db3$13400250$a501280a@.phx.gbl...
> I need to do the following;
> 1. bcp out data from our reporting server
> 2. Move bcp file to our archival server
> Sounds pretty straight forward.. but of course there is
> more...
> Since we are using this data for archival purposes, we
> just want to store the bcp file. My concern is that there
> are many schema changes to the source table and I wanted
> to make sure that I had the correct schema needed to bcp
> that file back into a table, say three years from now.
> Is there a option in bcp or bulk copy that would create
> the schema for you? Or can this be done via DTS?
> Thanks in advance
> Susan
>
>
> Any help would be greatly appreciated.
> Thanks
> Susan
>|||I've found a solution to my issue and wanted to let you
all know!!
I had also posted my question on the SQL Server Central
site and Mr. Mortensen was nice enough to provide a GENIUS
solution. He has a proc that will
1. Generate schema of the table in question
2. Update stats on table prior to BCPing
3. BCP's out the data into a readable text file
4. Verify row counts from the BCP and the actual table
(VERY NICE)
5. He also has a bat file that will run the schema and bcp
the data into the DB/table of your choice.
It was EXACTLY what I need and I want to thank him for his
help.
BTW - Why did Microsoft get rid of the backup 'table'
option? I used it quite a bit in 6.5 and there are MANY
time you only need to save one table and not the whole
database. I know there is dts and bcp, but nothing
compares to backing up and restoring a table. Other DBMS
provide this.. Just wondering.
Thanks
Susan
Here's the link in case your curious.
http://www.sqlservercentral.com/forums/shwmessage.aspx?
forumid=8&messageid=111276
>--Original Message--
>I need to do the following;
>1. bcp out data from our reporting server
>2. Move bcp file to our archival server
>Sounds pretty straight forward.. but of course there is
>more...
>Since we are using this data for archival purposes, we
>just want to store the bcp file. My concern is that there
>are many schema changes to the source table and I wanted
>to make sure that I had the correct schema needed to bcp
>that file back into a table, say three years from now.
>Is there a option in bcp or bulk copy that would create
>the schema for you? Or can this be done via DTS?
>Thanks in advance
>Susan
>
>
>Any help would be greatly appreciated.
>Thanks
>Susan
>.
>