Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There was a application which, earlier, pointed to a database. It used a specific set of tables. Those tables had triggers, constraints, etc. There were also stored procs which insert data into these tables.

There was a new application built on the same lines (with minimal changes), but to make that application work in our test box, we had to backup the existing database, and restore that one with a different name. The new app database pointed to this newly restored db.

But the restored db has objects which were totally unnecessary.

Now I am faced with the task of creating deployment scripts that should run in our production box, and create the database (with the required objects for the new app to work).

Are there any tools that would make this job easier?

PS: My test box has a sql server 2005 db. Where as the production box has a sql server 2000 db.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

From inside Microsoft SQL Server Management Studio, just right click on the database choose

Tasks | Generate Scripts

enter image description here

share|improve this answer

Even though you did not say it explicitly, I guess you removed the unnecessary objects from the restored database and now want to migrate the structure, and maybe some of the data, from the new (restored plus modifications) database to Production.

If this is correct, then I would recommend Red Gate’s SQL Compare (Structure) and SQL Data Compare (Data, of course) products. Both of these work very well and are able to generate change scripts which you may modify further or let the products execute the changes directly. Both products work by comparing the "From" and "To" databases to determine the needed changes. I am not sure if both products are aware of version differences (Never had a downgrade issue) so I would check the generated scripts to see if they add anything which is not compatible with SQL Server 2000. Also, both products are available on a trial basis.

If you are a user of Visual Studio and have the appropriate version level (With VS 2010, I believe it is Pro and higher), you might also consider using Visual Studio for Database Professionals.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.