Please Help!!!...moving MSSQL db

Question

Post reply

Please Help!!!...moving MSSQL db

Dinesh Ranasinghe

Valued Member

Points: 60
More actions
February 27, 2006 at 9:17 am

#90843

Hi,

I am fairly new to administering sql server db. I have two identical databases. One in a production server and another in a dev server. I've made modifications to the database in the dev server (stored proc, tables, etc) and I need to move the database in the dev server over to the production server without loosing any of the data in the production server database. Basically I am wanting to move the tables, stored procedures, sql accounts etc, with out the data in the dev server. Could you please let me know the best way to do this. Or direct me to a resource where I could find out how to do this.

Thank you for your help.

Dinesh

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Change management is one of the most difficult issues to deal with for a development dba.

The tools do not help you maintain versions of code or table schema.

What you should do is keep a copy of the changes you make and save them for your "Release".

When you change a table either use Enterprise manager, and script the changes and save them., or use t-sql to make the changes and save those scripts.

Keep track of the stored procedures you have changed also.

Then create/run the change scripts against the other database.

As far as to help you now that your ready to make changes I don't have any advice except go through and figure out what changed, and then create your scripts accordingly.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

You could change the name of the dev server database, move it to the production server, do a INSERT INTO and select all the data from the production database tables (one table at a time) and then drop the production database and rename the one from the dev server.

A lot of work..............and Ray's is more efficient. But you would have needed to keep track of the changes you made so you could script them to change the production database.

-SQLBill

Norene Malaney SSCommitted Points: 1896 More actions · Answer 3

If your budget allows, you might want to consider purchasing something like RedGate's SQLCompare (they have a 14 day trial for download on their website). It will allow you to compare 2 different databases and then it gives you a list of all the differences as well as the option to create a script to run on the one of the databases to make it look exactly like the other database.

We used to promote our dev db changes to our production db by hand but now with this tool, we find it to be much more efficient and much less error-prone.

Have a good day,

Norene Malaney

Dinesh Ranasinghe Valued Member Points: 60 More actions · Answer 4

Hi,

Thank you all so much for the reply. I really appreciate it. And thanks Norene for informing me about the RedGate's software. Looks like I would have to go with that for now. Although I like Ray's suggestions as well, like bill mentioned it would be efficient way to do the changes. I think I will do that for the next enhancements I am doing. Again thanks everyone for the feedback.

Dinesh