Microsoft Windows Powershell and SQL Server 2005 SMO - Part 9

This guide provides a practical overview of Microsoft Windows powershell and SQL server 2005 smo - part 9 with clear explanations and useful details. It focuses on the information users are most likely to need.

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

How Microsoft Windows Powershell and SQL Server 2005 SMO - Part 9 Works

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"])

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.1MSSQLDataAdventureWorks_Data. mdf ',SIZE = 180992KB, MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB)LOG ON(NAME = N'AdventureWorks_Log ', FILENAME =N'C: Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAAdventureWorks_Log. ldf ',SIZE = 2048KB, MAXSIZE = 2048GB, FILEGROWTH = 16384KB)COLLATE Latin1_General_CI_ASEXEC dbo. sp_dbcmptlevel @ dbname = N'AdventureWorks', @ new_cmptlevel = 90IF (1 = FULLTEXTSERVICEPROPERTY ('IsFullTextInstalled'))BeginEXEC [AdventureWorks]. [Dbo]. [Sp_fulltext_database] @action = 'enable'EndALTER DATABASE [AdventureWorks] SET ANSI_NULL_DEFAULT OFFALTER DATABASE [AdventureWorks] SET ANSI_NULLS ONALTER DATABASE [AdventureWorks] SET ANSI_PADDING ONALTER DATABASE [AdventureWorks] SET ANSI_WARNINGS ONALTER DATABASE [AdventureWorks] SET ARITHABORT ONALTER DATABASE [AdventureWorks] SET AUTO_CLOSE ONALTER DATABASE [AdventureWorks] SET AUTO_CREATE_STATISTICS ONALTER DATABASE [AdventureWorks] SET AUTO_SHRINK OFFALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS ONALTER DATABASE [AdventureWorks] SET CURSOR_CLOSE_ON_COMMIT OFFALTER DATABASE [AdventureWorks] SET CURSOR_DEFAULT GLOBALALTER DATABASE [AdventureWorks] SET CONCAT_NULL_YIELDS_NULL ONALTER DATABASE [AdventureWorks] SET NUMERIC_ROUNDABORT OFFALTER DATABASE [AdventureWorks] SET QUOTED_IDENTIFIER ONALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS OFFALTER DATABASE [AdventureWorks] SET DISABLE_BROKERALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS_ASYNC OFFALTER DATABASE [AdventureWorks] SET DATE_CORRELATION_OPTIMIZATION OFFALTER DATABASE [AdventureWorks] SET TRUSTWORTHY OFFALTER DATABASE [AdventureWorks] SET ALLOW_SNAPSHOT_ISOLATION OFFALTER DATABASE [AdventureWorks] SET PARAMETERIZATION SIMPLEALTER DATABASE [AdventureWorks] SET READ_WRITEALTER DATABASE [AdventureWorks] SET RECOVERY SIMPLEALTER DATABASE [AdventureWorks] SET MULTI_USERALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUMALTER DATABASE [AdventureWorks] SET DB_CHAINING OFF

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)

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 ONSET QUOTED_IDENTIFIER ONCREATE 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 ONSET QUOTED_IDENTIFIER ONCREATE 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 ONSET QUOTED_IDENTIFIER ONCREATE 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]..

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

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. UnicodeEncodingDriWithNoCheck: FalseScriptDrops: FalseTargetServerVersion: Version80AnsiFile: FalseAppendToFile: FalseToFileOnly: FalseSchemaQualify: TrueIncludeHeaders: FalseIncludeIfNotExists: FalseWithDependencies: FalseDriPrimaryKey: FalseDriForeignKeys: FalseDriUniqueKeys: FalseDriClustered: FalseDriNonClustered: FalseDriChecks: FalseDriDefaults: FalseTriggers: FalseBindings: FalseNoFileGroup: FalseNoCollation: FalseContinueScriptingOnError: FalsePermissions: FalseAllowSystemObjects: TrueNoIdentities: FalseConvertUserDefinedDataTypesToBaseType: FalseTimestampToBinary: FalseAnsiPadding: FalseExtendedProperties: FalseDdlHeaderOnly: FalseDdlBodyOnly: FalseNoViewColumns: FalseStatistics: TrueSchemaQualifyForeignKeysReferences: FalseClusteredIndexes: FalseNonClusteredIndexes: FalseAgentAlertJob: FalseAgentJobId: FalseAgentNotify: FalseLoginSid: FalseFullTextIndexes: FalseNoCommandTerminator: FalseNoIndexPartitioningSchemes: FalseNoTablePartitioningSchemes: FalseIncludeDatabaseContext: FalseFullTextCatalogs: FalseNoXmlNamespaces: FalseNoAssemblies: FalsePrimaryObject: TrueDriIncludeSystemNames: FalseDefault: TrueXmlIndexes: FalseOptimizerData: FalseNoExecuteAs: FalseEnforceScriptingOptions: FalseNoMailProfileAccounts: FalseNoMailProfilePrincipals: FalseIndexes: FalseDriIndexes: FalseDriAllKeys: FalseDriAllConstraints: FalseDriAll: False

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"])

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.

Figure 1.8

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 10 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11