how to generate scripts for database objects specific to an application?

Question

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.

Answer 1

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

Tasks | Generate Scripts

Answer 2

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.