Scripting a Database

Scripting a Database and it's objects

What exactly is the purpose of scripting objects or indeed entire databases as shown in this short tutorial ?

Well, the benefit of scripting a database is that you can include it in download versions of your applications, for one. But a even bigger benefit is that with a scripted database you always have a new and empty version of your database available.

The benefit of scripting objects will come to you gradually, over time... When you script a trigger or stored procedure that you need a "Slightly" different version of, instead of re-writing all that code you simply change a table name or field name or something similar.

Let us get to work.

First off we open our Enterprise Manager, then we expand our server and databases and we right-click on the SQL_Learning database, this database is leftover from a previous Upsizing tutorial. After right-clicking we move to All Tasks and select Generate SQL Script, as in Figure 1.

Now we are presented with a Generate SQL Scripts window and the first thing to do here, or we cannot script anything, is to click on "Show All". Now our objects appear and before we do anything else we select the Script all objects checkmark. Now we have something identical to Figure 2 which literally means that every OBJECT is going to be scripted.

When we click on the Formatting tab, we can select some more usefull features for instance Include descriptive headers, this is usefull because it comments your scripts ! I allways script everything, just to be sure i'm not missing a dependant object like a view. If you select only 7.0 compatible then you will lose the new SQL 2000 features when you run this script to create another database from it, so I tend not to use that.

When we switch to the options tab we can script the entire Database, which is what we wanted, in this example i'm also scripting all the users with their roles, SQL Server Logins and permissions but also all indexes and triggers and OFFCOURSE primary keys and foreign keys, defaults and check constraints.

Important ! Make sure that you select that option or you could find that after you run a script to rebuild your database that you get errors and when you look closer you will find that the tables lost ALL primary key settings and default values like getDate(), so make sure that you script them.

It is now safe to click on that OK button. A file requester pops up asking us where we want to store this script, I store it among all my scripts in a special folder called SQL. And when we click on save, the scripting process will commence, when the DONE requester pops up we are finished. We can now examine the script:

And now we have scripted the database and all it's objects, sadly we cannot script the actual data, but in a upcoming article I will show how to create a backup job and I will demonstrate how to schedule it so that it runs daily or weekly.

Download the script