Backup Database

[Rajeswari]

I am using VB Client to get the specific condition like Backup to database, flatfile, date range, percentage of data to be pruned, etc. I am using DTS Package to do this functionality. But for 600000 records(each table)- 11 tables, to backup 20% of data, time taken to backup is about 1 hour. I need a more effective method to backup the database.

I tried "bulk insert" through stored procedures, it also very slow.

 

[tlbroadbent]

When you say backup the database do you mean archive old data? Are you copying data from one database to another and deleting from the source database? Or have I totally misunderstood what you are trying to accomplish? Perhaps, you can provide more details.

Thanks, Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Rajeswari]

Yes, I am copying data from one database to another and deleting from the source database for specified condition.

I tried using bcp, but the problem is,
I have an identity field in the table, if i set the "-e" option to set the identity to true in bcp, first time it works fine, when i tried to backup for the second time for the same condition, it is not checking the identity for already existing records, it appends the table with new identity. Hence I used "Bulk Insert", but is very slow.

 

[tlbroadbent]

We use stored procedures to archive data. The stored procedures usually consist of the following steps.

1- Drop the indexes on the destination table
2- Begin a transaction
3- Lock the destination table
4- Append the data on the source table to the destination table using a SQL INSERT statement.
5- Commit the transaction
6- Begin a transaction
7- Lock the source table
8- Delete records on the source table that exist in the destination table
9- Commit the transaction
10- Create the indexes on the destination table

Repeat the steps outlined for each table. This process is generally fast even for a large number of records. However, there are times when it is necessary to archive in smaller batches and run the process multiple times. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.