Development and Production Databases | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Development and Production Databases

I am not truly a newbie to SQL but still very green. I have always worked with a single server and database until now. What I am wondering is, is there a way to have a development server where new columns, tables and idexes get created while the application gets developed. From time to time update the database on the production server with the add, modifies and deletes to tables and columns. I know I can generate the SQL script and create a new database with it however I notice that if I try to run the script against a database that already exists all data gets removed by the DROP and CREATE options. Up until now I have been doing it by hand but I keep forgetting this that and the other so I was hoping there is a way to compare them and only update the differences. Please take it easy on me as I know this question is probably a DBA 101 level question but I am just a simple network guy. Thanks in advance
Well SQL Server does not give the in built functionality for this but as you mentioned, you can always write scripts to compare the databases. And as far as DROP and CREATE is concerned, you don’t have to do that. Almost all database objects support ALTER database statement. However u’ll have to manually generate the ALTER statements based on the comparison. For comparing the database structures, there are some third party tools like SQL Compare are also available. HTH. Gaurav
When you script the database (I assume from EM), you can choose not to generate a DROP statement. Then you can change CREATE into ALTER where needed. Bambola.
There are some third-party tools that can help in your situation. Check out the software spotlight section of this website to read about a couple of them. —————————–
Brad M. McGehee, MVP
Thanks all, I appreciate the info. I think I will look into the cheap (corporate solution <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />) and try bambola’s idea.