Reinstalling Database Without Losting Data

Question

Reinstalling Database Without Losting Data

Reem Tigani

SSC Rookie

Points: 48
More actions
July 10, 2007 at 4:22 pm

#175216

Hi Everyone
I need your help badly. I created a Database using SQL server 2000 to my ASP.NET application. And instaled it into the customer computer and he used the application and inserted data into the database. My problem is i needed to make some changes in my database and my appplication and reinstall them into the customer computer. When i reinstall the updated database into the customer computer i lost all the data that are saved into the database. How can i reinstall the database changes without losting data.
To install my database into customer computer i use:
Databse > all tasks > Generate SQL script
And then run this script in the customer computer. When i did that i lost the data.
Please HELP me
Thanks for your help

Viewing 11 posts - 1 through 10 (of 10 total)

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

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 1

By scripting the database with default parameter you will get the database structure and not with the data's. If you need with data you should specify while scripting the db.

Its better to use backup and restore method , it will be better to take the db with data's.

1.)just backup of the database from the source

2.) restore the database into the customer system.

Regards..Vidhya Sagar
SQL-Articles

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

You will not able able to generate scripts to insert or update data into your database with the Generate Script available with SQL Server. You will need to write out scripts or there are plenty of tools/scripts available in net you can use one among them. But i hope you have already dropped and recreated the database so unless you have a backup of that database you will not be able to get back the data.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Reem Tigani SSC Rookie Points: 48 More actions · Answer 3

Thanks for reply vidhya sagar. I understand you but the database data that i fried to lost are in the customer system ot in the source. And when i use the backup method on the source i will take the test data and install it in customer system. Am i right ?

Thanks again

Reem Tigani SSC Rookie Points: 48 More actions · Answer 4

Hi frist of all thank you Sugesh Kumar. Ok can you help me how can i search for these tools or can you give me an example.

Thanks again

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

http://vyaskn.tripod.com/code.htm#inserts

http://www.codeproject.com/dotnet/ScriptDatabase.asp

Hope these script/link will be useful for you.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Reem Tigani SSC Rookie Points: 48 More actions · Answer 6

Thanks alot for your help. But my problem is when i installed the database in the customer system and it being used and full of records. I made some changes in the database structure and i want to install the new database in the customer system without losting the data in the orginal database. All the tools i found are focusing on how to generate database script and data scripts to take it from the source computer. And this is not my case because the data are found in the customer system and the structure is changed.

Thanks

Greg Charles SSC-Forever Points: 45403 More actions · Answer 7

Reem,

The advice and examples that have been posted are what you need. I'll try to explain how I think you should use them.

The next time you need to make changes to the customer database structures, use these steps:

1. Make copies of the existing tables, including data, in the customer database with DTS or SELECT INTO.

2. Execute the script you generated to produce the structural changes in the customer database

3. Copy the data from the tables created in step 1 into the new empty tables created in step 2.

I hope this helps you next time. If you're asking for help recovering the data you've already lost (you haven't stated that), restoring from a backup of the customer database is probably your only option as Sugesh stated in his first post.

Greg

goodguy SSCertifiable Points: 5456 More actions · Answer 8

I think it would be better to make and run ALTER scripts to modify DB structure on production servers.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 9

Reem -

It sounds like the one thing we're missing is that you need to recover the lost data?

If yes, I would restore the database from the latest backup before you applied your changes, then apply your changes again - being sure to update your scripts not to destroy your customers' data (e.g. alter table vs. create table scripts)

There are several tools available that will help you to script only the differences between the two databases that you may want to take a look at. My favorite is SQL Compare from Red Gate (http://www.red-gate.com) but the tools available from ApexSQL (http://www.apexsql.com) are pretty darn nice as well.

Joe

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 10

If you want to get the scripts to change the structures then go for tools like SchemaCompare by Red-Gate or Quest or SQLDelta they have better tools to do these schema changes.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com