help----backup using BCP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

help----backup using BCP

Author

Topic

anishki
Starting Member

5 Posts

Posted - 2007-08-31 : 02:37:15

i need to backup data (which satisfies a condition) from tables to flat files.....it worked fine using bcp...now i need to delete the records from tables which has been backed up......is ther any way out???????????

i wrote that bcp commands in a batch file to automate it.....

Westley
Posting Yak Master

229 Posts

Posted - 2007-08-31 : 04:09:32

I guess what you can do is copy the data to a temp table, bcp it out, once the bcp is done. Then join the temp table back to your main table and perform the delete. Then repeat the process for different batches.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-31 : 16:14:13

Not quite sure what you try to do. You can delete rows from any table even it was backed up, just bcp it out again after deletion.

anishki
Starting Member

5 Posts

Posted - 2007-09-03 : 04:49:21

is there any way to perform the delete query inside batch file?????? like bcp command i hav done like
bcp.exe "select * from AdventureWorks..anish where dateofbirth <'%1'" queryout "c:\dhl\transemployees.txt" -c -T

Kristen
Test

22859 Posts

Posted - 2007-09-03 : 08:10:08

"is there any way to perform the delete query inside batch file?"

Wrap the code in a Stored Procedure, and can that from BCP

SProc needs to do:


SELECT PKCol1, PKCol2, ...
INTO #TEMP
FROM MyTable
WHERE ...

SELECT T2.*
FROM #TEMP AS T1
     JOIN MyTable AS T2
         ON T2.PKCol1 = T1.PKCol1
        AND T2.PKCol2 = T1.PKCol2
        ...

DELETE T2
FROM #TEMP AS T1
     JOIN MyTable AS T2
         ON T2.PKCol1 = T1.PKCol1
        AND T2.PKCol1 = T1.PKCol1

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-03 : 13:09:25

Or call sql statement with osql in batch file.

anishki
Starting Member

5 Posts

Posted - 2007-09-04 : 02:36:00

my tables hav got millions of records......delete command will take hours to execute...........

Kristen
Test

22859 Posts

Posted - 2007-09-04 : 04:12:43

If you are deleting, say, 90% or more of the data then consider copying the "good" data to a temporary table, then drop the original table and rename the temporary table to the original name.

Or you can output the database with BCP, truncate the table, and re-import the data. Same thing really, but with the first method you can keep the application running (it will have to reference the temporary table, or have some final "lock application out and copy across the new records since this thing started" step)

Otherwise there isn't a way to get rid of the data other than deleting it!

DBAs here have delete routines that takes days and weeks to remove Stale data, so that there is only minimal impact on use of the database. We do large deletes using loops with delays to allow other processes to run, and so on.

Kristen

anishki
Starting Member

5 Posts

Posted - 2007-09-04 : 07:21:31

now am able to copy the data....n truncate the table from the batch file using th sqlcmd application......now i need to put back the needed data back to th table.....
i tried using bcp command to put back the data....but its not working for me....
i tried the command
bcp.exe AdventureWorks..anish2 in c:\dhl\transemployees.txt -k -T
its not working....
its showing the error....
"unable to open BCP host data-file".....any idea?????????????????????

Kristen
Test

22859 Posts

Posted - 2007-09-04 : 07:33:11

OK, so am I understanding what you are doing:

BCP out some data from a table
Truncate the table
BCP the data back in again

i.e. you are preserving a small-ish part of the table, and deleting the rest?

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-04 : 23:29:07

What's the whole process you like to do? Maybe there is better way.

anishki
Starting Member

5 Posts

Posted - 2007-09-05 : 01:27:43

yes kristen....you got it......if i trigger a delete query on the table after bulk copying...i think it will take more time than this(as it has gt millions of records)....am i right????????????...........

hi rmiao......i need to back up historical data of a database to flat files.....is there any other way out??????

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 03:59:37

"am i right?"

In order to be sure you would have to make a test, but I would think that:

If you are deleting 90% of the data then Export / Truncate / Import will be fastest (or copy-to-new-table as I mentioned above)

If you are deleting 10% of the data then DELETE will be fastest.

For any percentage in between there is some point where the two methods meet! but you can only guess what it is without an experiment.

See also:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Bulk+Delete+of+Records,Deleting+lots+of+Records+from+a+large+table

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources