I recently changed jobs and I am working with the rest of the software development team to streamline our deployment process. My primary focus, past few weeks has been in the area of database continuous integration. Yesterday I did my first push of one of our major ASP.NET application to the staging server. I tried experimenting with handling all of the DB changes via script. It worked great (after some tweaking) and now, I have a script I can run against production and not worry about whether or not I missed something, since I tested against staging.
Dear Reader, I thought I would share with you the steps I took to accomplish this task, in hopes that you may be helped, or that you can help point me towards a "better"/easier alternative. For some background: my IDE is Visual Studio 2005 and the database is Microsoft SQL Server 2000.
SQL 2000 Enterprise Manager
Stored Procedure Generation: I used the Generate SQL Script… feature to produce CREATE scripts for all of the stored procedures. If the stored procedure already existed and had been changed, I manually changed the script by replacing CREATE with ALTER” text. This way I did not have to drop the existing stored procedure, re-create it, and be forced to update permissions.
New Table Generation (without data): You can use the same method, as above, to generate a T-SQL script to create new databases, without data (we’ll come back to that point in a moment).
Existing Table Alteration: You can have SQL Server Enterprise Manager automatically generate an ALTER TABLE script for you by doing the following:
- Right click the table you wish to alter and click Design Table.
- Make your desired changes
- Click on the Save change script icon at the top of the window.
- Copy the change script text out of the Save Change Script window.
Microsoft SQL Server Database Publishing Wizard
New Table Generation (with data): Two of the new tables I was moving into the staging database are list tables that contain data I wanted to be present (Example: State list table). Using SQL 2000 Enterprise Manager I could not figure out how to automatically generate a script to both create the table and populate it with data. However, I was able to find a tool that would allow me to do this: Microsoft’s new SQL Server Database Publishing Wizard. The tool allows you to select one or more database objects for script generation and, in the case of tables, allows you to specify if you would like to include data. (via John Galloway)