Microsoft SQL Server 2012 Bible

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 potentially 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 fit 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 difficult method to move data.
• Server logins: A script can easily create server logins, but server logins tend to be domain-specific, so this option is useful only within a single domain.
• Server jobs: Server-specific 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 ...