BCP Utility Main Purpose

Question

BCP Utility Main Purpose

JC-3113

SSCrazy Eights

Points: 8371
More actions
November 9, 2009 at 2:04 pm

#213760

Hi Folks
I am looking at this utility called BCP and I am a little confused on what it does other than
copy data from one database table into another database table.
Is there any way to export and import the entire database into a new database?
I guess my bottom line question is, if I have database and some developer inadvertently drops a table that he now needs, what do I need to do to get the table back ?
It seems that BCP cannot be used unless that database's tables have been exported previously.
Would I restore his database from a backup into a new database, generate the table scripts and then export the table and import it using BCP ?
Thanks
Jim

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Actually, the main use of BCP is to import and export data to and from text files. It's got other uses, but that's probably 99% of what it gets used for.

For recovering a lost table, I'd use the last known good full backup and log backups to restore to a point in time right before the drop table command was issued. If the database isn't being backed up, that's a whole major issue all by itself.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

Hi GSquared

I have backups. I am just unsure how to restore just one table that got dropped from the full and diff backups, but your saying i can from these backups get what I need

Thanks

Jim

GSquared SSC Guru Points: 260824 More actions · Answer 3

Restore with a different name, then copy the table from that database to the main one.

When you do a restore, you pick the database name. Name it something like "MyDatabase_Old" or "MyDatabase_Restored" or something like that. Then you can use the import/export wizard, or a T-SQL statement, or whatever tool you're most comfortable with, to move the data from the restored copy to the other.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 4

JC-3113

SSCrazy Eights

Points: 8371

November 9, 2009 at 3:01 pm

#1076956

Got it..

Thanks

Jim

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 5

Hi GSquared

if i restore a table, are the index(s) also restored with it, or do I have to do additional steps ?

Thanks

Jim

Lowell SSC Guru Points: 323518 More actions · Answer 6

JC-3113 (11/10/2009)
Hi GSquared
if i restore a table, are the index(s) also restored with it, or do I have to do additional steps ?
Thanks
Jim

If a table was dropped, it's indexes went with it as well; so when you restore a backup to a new database name, you'd connect to the new database, script out the table definition as well as it's indexes, and then run that script on your original database.

after that you'd most like move the data that was in the table as well, from the "new" database to the original.

the fastest way is with an INSERT INTO statement, and if it has a primary key, you'd proable want to set IDENTITY_INSERT YOURTABLE ON;

basic example:

USE ORIGINALDB

SET XACT_ABORT ON --any error, auto rollback my transaction

BEGIN TRAN --start a transaction

SET IDENTITY_INSERT MYTABLE ON --i'm going to insert identity() values

INSERT INTO MYTABLE(PK,COLUMLIST)

SELECT PK,COLUMLIST FROM OTHERDATABASE.dbo.MYTABLE --from the other database!

SET IDENTITY_INSERT MYTABLE OFF --done isnerting, turn this off

--is my data OK?

SELECT * FROM MYTABLE

--ROLLBACK TRAN

--COMMIT TRAN

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 7

Thanks Lowell

I am just running some tests with NorthWind database right now

Jim