Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9

October 3, 2007

Using PowerShell and SMO to Generate an SQL Server Script

Part 1 and Part 2 of this series discussed Power Shell installation and simple SMO, WMI cmdlets. Part 3 covered how to script PowerShell and connect to SQL Server.

Part 4 explained how to use a PowerShell script to loop through the content of a file and connect to different servers. Part 5 examined creating a SQL Server database using PowerShell and SMO. Part 6 talked about backing up a SQL Server database using PowerShell and SMO. Part 7 illustrated how to list all of the objects in a database and part 8 demonstrated how to list all of the properties of the objects in a database using PowerShell and SMO.

This installment of the series illustrates how to use PowerShell in conjunction with SMO to Generate an SQL Server Script. Generating SQL Server Scripts is an essential task for SQL Server Database administrators and developers.

Method 1

Let’s assume that we would like to generate a “Create Database” script for the AdventureWorks database from the server “HOME\SQLEXPRESS”. Execute the following cmdlets as shown below. [Fig 1.1]

[reflection.assembly]::LoadWithPartialName
	("Microsoft.SqlServer.Smo") | out-null
$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" 
	"HOME\SQLEXPRESS" 
$MyScripter.Server=$srv
$MyScripter.Script($srv.databases["AdventureWorks"])

This would generate a “Create Database” script for the AdventureWorks database as shown below. [Refer Fig 1.2]

CREATE DATABASE [AdventureWorks] ON  PRIMARY
( NAME = N'AdventureWorks_Data', 
FILENAME = 
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\
	MSSQL\Data\AdventureWorks_Data.mdf' , 
SIZE = 180992KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
 LOG ON
( NAME = N'AdventureWorks_Log', FILENAME = 
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\
	MSSQL\DATA\AdventureWorks_Log.ldf' , 
SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
 COLLATE Latin1_General_CI_AS
EXEC dbo.sp_dbcmptlevel @dbname=N'AdventureWorks', @new_cmptlevel=90
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AdventureWorks].[dbo].[sp_fulltext_database] @action = 'enable'
end
ALTER DATABASE [AdventureWorks] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [AdventureWorks] SET ANSI_NULLS ON
ALTER DATABASE [AdventureWorks] SET ANSI_PADDING ON
ALTER DATABASE [AdventureWorks] SET ANSI_WARNINGS ON
ALTER DATABASE [AdventureWorks] SET ARITHABORT ON
ALTER DATABASE [AdventureWorks] SET AUTO_CLOSE ON
ALTER DATABASE [AdventureWorks] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [AdventureWorks] SET AUTO_SHRINK OFF
ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [AdventureWorks] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [AdventureWorks] SET CURSOR_DEFAULT  GLOBAL
ALTER DATABASE [AdventureWorks] SET CONCAT_NULL_YIELDS_NULL ON
ALTER DATABASE [AdventureWorks] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [AdventureWorks] SET QUOTED_IDENTIFIER ON
ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [AdventureWorks] SET  DISABLE_BROKER
ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [AdventureWorks] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [AdventureWorks] SET TRUSTWORTHY OFF
ALTER DATABASE [AdventureWorks] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [AdventureWorks] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [AdventureWorks] SET  READ_WRITE
ALTER DATABASE [AdventureWorks] SET RECOVERY SIMPLE
ALTER DATABASE [AdventureWorks] SET  MULTI_USER
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [AdventureWorks] SET DB_CHAINING OFF

Method 2

Let’s assume that we want to generate a script for all of the tables in the AdventureWorks database from the “HOME\SQLEXPRESS” server. Execute the following cmdlets as shown below. [Fig 1.3]

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME\SQLEXPRESS" 
$MyScripter.Server=$srv
$MyScripter.Script($srv.Databases["adventureworks"].tables)

This generates CREATE TABLE scripts for all of the tables in the AdventureWorks database from the server “HOME\SQLEXPRESS”. Execute the following cmdlets as shown below. [Fig 1.4]

.....
.....

CREATE TABLE [Sales].[SpecialOffer](
        [SpecialOfferID] [int] IDENTITY(1,1) NOT NULL,
        [Description] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
        [DiscountPct] [smallmoney] NOT NULL,
        [Type] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
        [Category] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
        [StartDate] [datetime] NOT NULL,
        [EndDate] [datetime] NOT NULL,
        [MinQty] [int] NOT NULL,
        [MaxQty] [int] NULL,
        [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
        [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SpecialOfferProduct](
        [SpecialOfferID] [int] NOT NULL,
        [ProductID] [int] NOT NULL,
        [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
        [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[Store](
        [CustomerID] [int] NOT NULL,
        [Name] [dbo].[Name] NOT NULL,
        [SalesPersonID] [int] NULL,
        [Demographics] [xml](CONTENT [Sales].[StoreSurveySchemaCollection]) NULL,
        [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
        [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[StoreContact](
        [CustomerID] [int] NOT NULL,
        [ContactID] [int] NOT NULL,
        [ContactTypeID] [int] NOT NULL,
        [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
        [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
......
......

Method 3

Scripting SQL Server database and its objects comes with many options. These options can be altered by turning the available flags on and off. Execute the following cmdlets to see all of the available options in scripting. [Refer Fig 1.5]

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME\SQLEXPRESS" 
$MyScripter.Server=$srv
$so=$MyScripter.Options
$so

This displays all of the scripting options available that can be altered as shown below. [Refer Fig 1.6]

FileName                              :
Encoding                              : System.Text.UnicodeEncoding
DriWithNoCheck                        : False
ScriptDrops                           : False
TargetServerVersion                   : Version80
AnsiFile                              : False
AppendToFile                          : False
ToFileOnly                            : False
SchemaQualify                         : True
IncludeHeaders                        : False
IncludeIfNotExists                    : False
WithDependencies                      : False
DriPrimaryKey                         : False
DriForeignKeys                        : False
DriUniqueKeys                         : False
DriClustered                          : False
DriNonClustered                       : False
DriChecks                             : False
DriDefaults                           : False
Triggers                              : False
Bindings                              : False
NoFileGroup                           : False
NoCollation                           : False
ContinueScriptingOnError              : False
Permissions                           : False
AllowSystemObjects                    : True
NoIdentities                          : False
ConvertUserDefinedDataTypesToBaseType : False
TimestampToBinary                     : False
AnsiPadding                           : False
ExtendedProperties                    : False
DdlHeaderOnly                         : False
DdlBodyOnly                           : False
NoViewColumns                         : False
Statistics                            : True
SchemaQualifyForeignKeysReferences    : False
ClusteredIndexes                      : False
NonClusteredIndexes                   : False
AgentAlertJob                         : False
AgentJobId                            : False
AgentNotify                           : False
LoginSid                              : False
FullTextIndexes                       : False
NoCommandTerminator                   : False
NoIndexPartitioningSchemes            : False
NoTablePartitioningSchemes            : False
IncludeDatabaseContext                : False
FullTextCatalogs                      : False
NoXmlNamespaces                       : False
NoAssemblies                          : False
PrimaryObject                         : True
DriIncludeSystemNames                 : False
Default                               : True
XmlIndexes                            : False
OptimizerData                         : False
NoExecuteAs                           : False
EnforceScriptingOptions               : False
NoMailProfileAccounts                 : False
NoMailProfilePrincipals               : False
Indexes                               : False
DriIndexes                            : False
DriAllKeys                            : False
DriAllConstraints                     : False
DriAll                                : False

Now let’s try to save the generated “Create Database” script on to a file using the Scripting option. Execute the cmdlets shown below. [Refer Fig 1.7]

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "HOME\SQLEXPRESS"
$MyScripter.Server=$srv
$so=$MyScripter.Options
$so.FileName="C:\MyDatabaseScript.sql"
$MyScripter.Script($srv.Databases["adventureworks"])

This would generate the “Create Database” script on to the file C:\MyDatabaseScript.sql, as shown in Fig 1.8 and Fig 1.8.

Conclusion

Part 9 of this article series illustrated how to use PowerShell and SMO to generate a script for database and tables. It also illustrated how to use scripting options to write to a file. Part 10 will discuss more about scripting options and how to create PowerShell scripts to generate SQL Server scripts by passing parameters.

» See All Articles by Columnist MAK