620
Part V: Enterprise Data Management
Working with SQL Script
Sometimes when you want to move a database, it is important to know that all the objects
created on the destination are new and are exactly as you dictated them rather than poten-
tially mix old and new objects/data together. This is where the option of scripting excels; it
gives the administrator granular control over exactly what objects get created and when.Scripts are smaller than databases. They often fi t on a portable media, and they can be
edited with simple tools such as Notepad. As an example, the sample databases for this
book are distributed by means of scripts.Scripts are useful for distributing the following:■ (^) Database schema (databases, tables, views, stored procedures, functions, and so on)
■ (^) Security roles
■ (^) Database jobs
■ (^) Limited sample data or priming data
Although you could, it isn’t recommended to create a script to move the following:
■ (^) Data: A script can insert rows, but this is a diffi cult method to move data.
■ (^) Server logins: A script can easily create server logins, but server logins tend to be
domain-specifi c, so this option is useful only within a single domain.
■ (^) Server jobs: Server-specifi c jobs generally require individualized tweaking.
Although a script may be useful to copy jobs, they likely require editing prior to
execution.
You can also use scripts to implement a change to a database. The easiest way to modify
a client database is to write a script. The change script can be tested on a backup of the
database.
You can generate scripts in several ways:
■ (^) You can initially develop the database in Management Studio or SQL Server Data
Tools using a handwritten DDL script. Chapter 7, “Relational Database Design
and Creating the Physical Database Schema,” explains how to create such a
script. In addition, the sample databases on the website are all created using a
DDL script.
■ (^) Management Studio can generate a script to create the entire database or a change
script for schema changes made with the Table Designer or the Database Designer.
■ (^) Most third-party database-design tools generate scripts to create the database or
apply changes.
c23.indd 620c23.indd 620 7/31/2012 9:25:21 AM7/31/2012 9:25:21 AM
http://www.it-ebooks.info