Backup and restore database tables data, relations problem !!!

[maged]

Dear all,
we have built a small utitlity to save some business data. it uses the msde as its database.

because the msde doesn't have any enterprize manager, we had to create a backup/restore functions inside the software.

since its only about 10 tables in the database, we would like to save the data into xml files and resotre them back from xml files.

our problem is this:

we didn't implement physical database relations inside the database. although we are using relational database designs. but we prefer to join rows on runtime and not implement relations inside the database.

this seems to be a very large problem in backup and restoring why?

because we are have for example two tables : [documents] & [docdetails]

documents table has an identity integer column which we use as primary key.

docdetails rows has a foriegn key column called [document] which is related to the [documents.serial]

when we save the data to xml files its ok, but when we try to retireve the data from xml into the msde database. all [documents] rows are assigned a new autonumber , this way all the related rows in the docdetails table loses their correct integerity with the data .

i hope i am clear, is there any solution to solve this problem ????

thank you in advance

rgds

[VBDT]

I think that if you handle the tables’ relations at run time, then you need to handle the tables’ autonumber as well in your app.

[maged]

is that your way for punishing me for being wrong

[VBDT]

I don’t know if you understand me right. What I am trying to say is that you need to generate the autonumber in your app when a record is added. There are three ways to set the autonumber, in database, in dataset and manually through the code. The first to will not work because they are generated independently (perhaps you can try the dataset aoutonumber to see if it works).

[mendhak]

I suppose that's happening because of the way you've approached this task. When you take a backup to SQL, you are essentially extracting the information from the database, not backing it up. Now, if you were to maintain your data the way it is, you'd need to first disable the autonumber field on the table, insert the data with primary key values intact, and then restore the autonumbering on the primary key column in the tables.

This is not a nice approach because you are potentially opening up a hole in data integrity. XML files, being text files, can be edited. Addition of a new row to the xml file could cause duplication.

MSDE, just like SQL server, allows T-SQL statements. Which means that you can use the BACKUP DATABASE ... TO DISK statement to perform your backups. That is the proper approach.

[szlamany]

Plus...

and although I certainly don't agree with saving data to text files...

It would have been easier to save the DATA from the tables as TAB-DELIMITED .TXT text files.

Then to get it back into SQL say

BULK INSERT TABLEXYZ FROM "C:\...\...\TABLEXYZ.TXT" WITH (KEEPIDENTITY)

KEEPIDENTITY means that the IDENTITY values in the .TXT file are retained during the insert of the data...

We often "save" text files of tables - so we can archive them (mostly control tables in our DB - not actual production data). We use TDF type .TXT files for that.

But at any rate - to get around you problem - when you are ready to INSERT the rows from the XML again do this:

SET IDENTITY_INSERT TABLEXYZ ON

This will allow you to specify the IDENTITY value in your INSERT statements.

btw - only one table can have this set to ON at a time - keep that in mind.

[maged]

thx szlamany

this tip is what i was looking for

thank you all of your time

rgds