A short SQL Server 2000 tutorial explaining how to script a complete database with roles and logins and all other objects for a future empty database that resides in a single .sql text file.
The project used is the upsized Access database that comes with the Dreamweaver Learningsite command.
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
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
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
Download the script