Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 1 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 2 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 3 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 4 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 4
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 5 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 6 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 7 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 8 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8

The MAK

Use PowerShell and SMO to create SQL Server scripts

Part 1 and Part 2 of this series introduced PowerShell and SMO, WMI cmdlets simple installation. In Part 3, I explained how to script PowerShell and connect to SQL Server.

Part 4 introduced how to use PowerShell script to iterate in file content and connect different servers. Part 5 is an introduction to creating a SQL Server database using PowerShell and SMO. Part 6 examines the problem of backing up a SQL Server database with PowerShell and SMO, part 7 is how to create a list of objects in a database and part 8 is a way to list all the properties of the objects in the database by PowerShell and SMO.

In Part 9 of this series, I will show you how to use PowerShell in conjunction with SMO to create a SQL Server script. Creating SQL Server scripts is an important task for administrators and SQL Server database development professionals.

Method 1

Let's assume we want to create a 'Create Database' script for the AdventureWorks database from the server 'HOMESQLEXPRESS'. Execute cmdlets below (see Figure 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" "HOMESQLEXPRESS"
$ MyScripter.Server = $ srv
$ MyScripter.Script ($ srv.databases ["AdventureWorks"])

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 9
Figure 1.1

This command will create a 'Create Database' script for the AdventureWorks database as shown below (see Figure 1.2).

CREATE DATABASE [AdventureWorks] ON PRIMARY
(NAME = N'AdventureWorks_Data ',
FILENAME =
N'C: Program FilesMicrosoft SQL ServerMSSQL.1
MSSQLDataAdventureWorks_Data.mdf ',
SIZE = 180992KB, MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB)
LOG ON
(NAME = N'AdventureWorks_Log ', FILENAME =
N'C: Program FilesMicrosoft SQL ServerMSSQL.1
MSSQLDATAAdventureWorks_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

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 10
Figure 1.2

Method 2

Let's assume that we want to create a script for all tables in the AdventureWorks database from the server 'HOMESQLEXPRESS'. Execute cmdlets below (refer to Figure 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" "HOMESQLEXPRESS"
$ MyScripter.Server = $ srv
$ MyScripter.Script ($ srv.Databases ["adventureworks"]. Tables)

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 11
Figure 1.3

This command creates a CREATE TABLE script for tables in the AdventureWorks database from the server 'HOMESQLEXPRESS'. Execute cmdlets below (refer to Figure 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]
.
.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 12
Figure 1.4

Method 3

SQL Server database scripting and its objects have many options. These options can be changed by adjusting the available flag to 'on' or 'off'. Execute cmdlets below to see all options available in the script (refer to Figure 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" "HOMESQLEXPRESS"
$ MyScripter.Server = $ srv
$ so = $ MyScripter.Options
$ compared

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 13
Figure 1.5

The above command displays all available options for the scenario you can change as shown below (refer to Figure 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

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 14
Figure 1.6

Now let's try to save the created script 'Create Database' to a file using the Scripting option. Execute cmdlets as shown below (refer to Figure 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" "HOMESQLEXPRESS"
$ MyScripter.Server = $ srv
$ so = $ MyScripter.Options
$ so.FileName = "C: MyDatabaseScript.sql"
$ MyScripter.Script ($ srv.Databases ["adventureworks"])

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 15
Figure 1.7

This command will save the above 'Create Database' script to file C: MyDatabaseScript.sql, as shown in Figure 1.8 and Figure 1.9.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 16
Figure 1.8

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 17
Figure 1.9

Conclude

Part 9 of this series showed you how to use PowerShell and SMO to create scripts for databases and tables. It also introduces how to use scripting options to write to a data file. Part 10 will cover more scripting options and how to create PowerShell scripts from which to create SQL Server scripts using transfer parameters.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 18 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Picture 19 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11