Creating db scripts for any changes...mantaining SQL incremental scripts for db revisions.
-
Can anyone offer their thoughts/suggestions on how to best breakup scripts for managing a db? For instance, I'm starting a new project, and have already used SQL Server Manager to create the tables, pk's, indexes and the relationship between tables. Does this need to all be scripted in one large script? Moving forward, any changes that I may apply to the db (e.g. new table, new index) will have to be saved to the db. Should I create scripts for any incremental changes? What is the best way to capture these changes? Is there something in sql server manager that will say "capture changes" and script just the changes?? I guess the advantage of scripting all of your incremental scripts would be to easily back out changes that may have caused problems with your code. Do you create one giant script with a bunch of ALTER statements for your VIEWS, SP's, FUNCTIONS, etc...? Thanks
-
Can anyone offer their thoughts/suggestions on how to best breakup scripts for managing a db? For instance, I'm starting a new project, and have already used SQL Server Manager to create the tables, pk's, indexes and the relationship between tables. Does this need to all be scripted in one large script? Moving forward, any changes that I may apply to the db (e.g. new table, new index) will have to be saved to the db. Should I create scripts for any incremental changes? What is the best way to capture these changes? Is there something in sql server manager that will say "capture changes" and script just the changes?? I guess the advantage of scripting all of your incremental scripts would be to easily back out changes that may have caused problems with your code. Do you create one giant script with a bunch of ALTER statements for your VIEWS, SP's, FUNCTIONS, etc...? Thanks
-
Can anyone offer their thoughts/suggestions on how to best breakup scripts for managing a db? For instance, I'm starting a new project, and have already used SQL Server Manager to create the tables, pk's, indexes and the relationship between tables. Does this need to all be scripted in one large script? Moving forward, any changes that I may apply to the db (e.g. new table, new index) will have to be saved to the db. Should I create scripts for any incremental changes? What is the best way to capture these changes? Is there something in sql server manager that will say "capture changes" and script just the changes?? I guess the advantage of scripting all of your incremental scripts would be to easily back out changes that may have caused problems with your code. Do you create one giant script with a bunch of ALTER statements for your VIEWS, SP's, FUNCTIONS, etc...? Thanks
You should think of your db scripts like source code and create a SQL folder in your source structure that you check in and out of your version control system. (You are using one ? Right ?) You can then see what the differences are between your 1.0 and 1.1 create table scripts are. :-) Then consider this: 1) Create a base set of scripts called "create_XXX" where the "XXX" is the database object like tables, views, trigger, sproc, index. These scripts should be designed so that you could create an entire database schema from a blank database instance. 2) Create a script called "upgradeSchema" which will contain all of the alter table, alter index, create view, etc that may have changed from one release to another. You should design this upgradeSchema script in such a way that it could be run more than once on a database and have no undesireable effects. In other words, make no assumptions about the current schema state, test to see if a column already exists, is the data in the column null, etc. Lots of error checking going on here ... Also you may want to consider 2 other areas: System data - This script would insert values into lookup tables in order for the system to function. Sample data - This might be enough data in tables so that you can publish a student guide showing some basic operations of your application. What I've presented isn't a complete solution, but it should give you something to think about ... If you are not using a version control system, your asking for trouble. Look into Subversion http://subversion.tigris.org[^] Or if you a small Windows shop, you could always use Visual Source Safe, from Microsoft.
-
You should think of your db scripts like source code and create a SQL folder in your source structure that you check in and out of your version control system. (You are using one ? Right ?) You can then see what the differences are between your 1.0 and 1.1 create table scripts are. :-) Then consider this: 1) Create a base set of scripts called "create_XXX" where the "XXX" is the database object like tables, views, trigger, sproc, index. These scripts should be designed so that you could create an entire database schema from a blank database instance. 2) Create a script called "upgradeSchema" which will contain all of the alter table, alter index, create view, etc that may have changed from one release to another. You should design this upgradeSchema script in such a way that it could be run more than once on a database and have no undesireable effects. In other words, make no assumptions about the current schema state, test to see if a column already exists, is the data in the column null, etc. Lots of error checking going on here ... Also you may want to consider 2 other areas: System data - This script would insert values into lookup tables in order for the system to function. Sample data - This might be enough data in tables so that you can publish a student guide showing some basic operations of your application. What I've presented isn't a complete solution, but it should give you something to think about ... If you are not using a version control system, your asking for trouble. Look into Subversion http://subversion.tigris.org[^] Or if you a small Windows shop, you could always use Visual Source Safe, from Microsoft.
Wow...you've definitely provided great examples! 1) Should the base set of scripts (Create_xxx) pretty much create the 'version 1.0' db? Then, I imagine I would run my alter scripts to get it updated to the current version? 2) Should I create one or multiple ALTER scripts for each release? For example, if my project has 8 releases, I probably should have 8 alter scripts right? Or should I simply have one version for my ALTER script and place everything in that one script. 3) Is there a way I can take my existing db and script it out within SQL Server mgr? THanks!
-
Wow...you've definitely provided great examples! 1) Should the base set of scripts (Create_xxx) pretty much create the 'version 1.0' db? Then, I imagine I would run my alter scripts to get it updated to the current version? 2) Should I create one or multiple ALTER scripts for each release? For example, if my project has 8 releases, I probably should have 8 alter scripts right? Or should I simply have one version for my ALTER script and place everything in that one script. 3) Is there a way I can take my existing db and script it out within SQL Server mgr? THanks!
- Yes, the base set of scripts would be the 1.0 version. However, you can also think about the 2.0 version as being a new set of "create" scripts. Each time you have a major release (Ver x.0), the create scripts would be able to create a fresh schema. In a major release, you could have a conversion script like UpgradeSchema_v1_v2, which would take the system from any v1.x release and get them right up to a 2.0 release. 2) In the past, I have created 1 large ALTER script. I prefer to call it something like UpgradeSchema.sql for clarity. In this script you need to design it so that it can take a database from a v1.0 to a v1.1, v1.2, v1.3, etc in one shot. The script has to be designed so that it doesn't care which database version the system is at, perform enough checks so that nothing errors out. For example, if you need to add a column to a table, then check to see if the column already exists before adding it. You will become very familiar with the SYSOBJECTS table when writing these types of upgradeSchema scripts. 3) You can use the SQL server Management Studio to create your base scripts by highlighting each table, right click, choose "Script Table As, Create to, New query editor Window", this will give you a jump on creating your base set of scripts. Remember to create a separate set of scripts for each database object; one script creates all tables, another one creates views, etc ... You will see that running these scripts requires that they be run in a certain order otherwise they won't execute. For example, you can't create views unless you have already created table. ;-) More to think about.
-
- Yes, the base set of scripts would be the 1.0 version. However, you can also think about the 2.0 version as being a new set of "create" scripts. Each time you have a major release (Ver x.0), the create scripts would be able to create a fresh schema. In a major release, you could have a conversion script like UpgradeSchema_v1_v2, which would take the system from any v1.x release and get them right up to a 2.0 release. 2) In the past, I have created 1 large ALTER script. I prefer to call it something like UpgradeSchema.sql for clarity. In this script you need to design it so that it can take a database from a v1.0 to a v1.1, v1.2, v1.3, etc in one shot. The script has to be designed so that it doesn't care which database version the system is at, perform enough checks so that nothing errors out. For example, if you need to add a column to a table, then check to see if the column already exists before adding it. You will become very familiar with the SYSOBJECTS table when writing these types of upgradeSchema scripts. 3) You can use the SQL server Management Studio to create your base scripts by highlighting each table, right click, choose "Script Table As, Create to, New query editor Window", this will give you a jump on creating your base set of scripts. Remember to create a separate set of scripts for each database object; one script creates all tables, another one creates views, etc ... You will see that running these scripts requires that they be run in a certain order otherwise they won't execute. For example, you can't create views unless you have already created table. ;-) More to think about.