Sql Server Development Server and Live

I have a database project that goes through iterations (only one so far) and I need to deploy a testing version to a开发者_开发技巧 live server. I'm not sure how to go about this.

I can make all the changes in a copy and then remake those changes in the live version. That doesn't make sense.

Is there a way to change a server name to an existing server? What's the best practice for this scenario?

With a Visual Studio Database Project, you should be able to have as many database connections defined as you like. When you go run your scripts, you can pick an menu option called "Run On...." and then pick which server connection to run those scripts on.

Just make sure the database name is the same for both instances, or make sure that you do not specify USE (database) at the top of all your scripts, if the database names are different from target to target.

In the first place, you should have scripts already written for the changes you made. They should be in source control. No changes ever should be made to database structure without a script and versioning.

Since you don't appear to have what you should have to deploy, then you need to tool to check the differences between the databases. Redgate's SQL Compare is the one to buy.

Be careful of simply using the tool without thinking, there may be changes in dev you are not yet ready to promote to prod. Read through the scripts before running them.

Also you may need SQL Data Compare to run against any lookup tables you have to see if new values have been added in dev that need to go to prod. Again these inserts should have been scripted and in source control and then deploying is simple.

Maybe I'm misunderstanding the question, but I don't see how you could just swap the databases. If you make a development version of a database and update the schema, you must surely run some tests and update the data. You can't just make that the development database now because it's full of test data.

What you need to do is run a tool that compares the old schema to the new schema and then apply these changes to the production database. There are tools out there on the market to do this. Failing that, you could dump the old and new schemas, run them through an ordinary file compare to get the differences, and then build an update script out of that.

On my present project we use what I think is a terrible practice: We keep a hand-maintained script of schema updates for each version, and every time someone makes a change they're supposed to update this script. Every now and then someone makes a mistake and we have to scramble to figure out what went wrong. Like we just had a problem deploying to our user acceptance test because someone updated the create statement for a new table to include a foreign key to another new table ... not realizing that the table being referenced was created until further down in the script. It worked fine it test because the tables were created in an order that made it work.

My conclusion is you're much better off to just make changes to the schema on the fly, then when you're done, run an automated compare to generate the ALTER statements.

By the way, on a project I worked on a few years ago, for a desktop application where each customer had their own copy of the database, we put in what I thought was a very nice feature: Every time the program started up, it compared the schema of the database to what it thought it ought to be, and if they didn't match, it automatically updated it. So when they installed a new version, it just automatically updated the database the first time they ran it.