Microsoft® SQL Server® 2008 Administration with Windows PowerShell™

Chapter 21. Generating Database Scripts

Generating Data Definition Language (DDL) scripts for a database and its objects is also a SQL Server database administration task. Generating such scripts is very useful for comparing changes in the DDLs between two different dates. It also helps in copying the schema and objects from one server to another. This chapter covers generating DDL scripts for the following:

• Databases

• Schemas

• User-defined data types

• Tables

• User views

• Stored procedures

• Functions

• XML schemas

• Users

Scripting Databases

Throughout this chapter you will use the .NET-based object library SMO. You learned how to write SMO programs in Windows PowerShell in Chapter 13, and now you will use two major SMO classes in this chapter:

• Microsoft.SqlServer.Management.Smo.Scripter

• Microsoft.SqlServer.Management.Smo.Server

For some tasks, such as scripting stored procedures, you will also be using the SMO class Microsoft.SqlServer.Management.Smo.StoredProcedure.

This chapter describes how to use SQL Server Management Objects (SMO) to script databases and its objects. All the scripts in this chapter store the SQL script files generated to a directory named C:\scdata. Let's first create this directory:

New-Item -Path C:\ -Name scdata -Type directory

Create the following C:\DBAScripts\Script-Db.ps1 script. This script generates a DDL statement that takes three parameters: a server name, a database, and a file path. The server name indicates where the database resides. The file path points to the file where ...