Microsoft Windows PowerShell and SQL Server 2005 SMO – Part 10

Using PowerShell Script to Generate SQL Server Scripts for Database and Tables

(“Microsoft.SqlServer.Smo”) | out-null
$MyScripter=new-object

Execute
the PowerShell script as shown below. [Refer Fig 1.1]

./ScriptSQL "HOME\SQLEXPRESS" "Admin" "Database"

Fig 1.1

Parameters explained:

• ScriptSQL is actually the ScriptSQL.ps1
  script in the folder c:\ps
• HOME is the hostname
• SQLEXPRESS is the sql server instance name
  on the host HOME

·        
Admin
is the
database name that resides in SQLEXPRESS

• Database is the parameter, which when
  passed will generate the “Create database” script

This
script generates the “Create Database” script shown below. [Refer Fig 1.2]

Database Scripts
—————–
CREATE DATABASE [Admin] ON  PRIMARY
( NAME = N’admin’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\admin.mdf’ , SIZE = 2240KB , M
AXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N’admin_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\admin_log.LDF’ , SIZE = 76
8KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
EXEC dbo.sp_dbcmptlevel @dbname=N’Admin’, @new_cmptlevel=90
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [Admin].[dbo].[sp_fulltext_database] @action = ‘enable’
end
ALTER DATABASE [Admin] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [Admin] SET ANSI_NULLS OFF
ALTER DATABASE [Admin] SET ANSI_PADDING OFF
ALTER DATABASE [Admin] SET ANSI_WARNINGS OFF
ALTER DATABASE [Admin] SET ARITHABORT OFF
ALTER DATABASE [Admin] SET AUTO_CLOSE ON
ALTER DATABASE [Admin] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [Admin] SET AUTO_SHRINK OFF
ALTER DATABASE [Admin] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [Admin] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [Admin] SET CURSOR_DEFAULT  GLOBAL
ALTER DATABASE [Admin] SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE [Admin] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [Admin] SET QUOTED_IDENTIFIER OFF
ALTER DATABASE [Admin] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [Admin] SET  ENABLE_BROKER
ALTER DATABASE [Admin] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [Admin] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [Admin] SET TRUSTWORTHY OFF
ALTER DATABASE [Admin] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [Admin] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [Admin] SET  READ_WRITE
ALTER DATABASE [Admin] SET RECOVERY FULL
ALTER DATABASE [Admin] SET  MULTI_USER
ALTER DATABASE [Admin] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [Admin] SET DB_CHAINING OFF

Fig 1.2

Now
Execute the PowerShell script as shown below. [Refer Fig 1.3]

./ScriptSQL "HOME\SQLEXPRESS" "VixiaTrack""Tables"

Fig 1.3

Parameters explained:

• ScriptSQL is actually the ScriptSQL.ps1
  script in the folder c:\ps
• HOME is the hostname
• SQLEXPRESS is the sql server instance name
  on the host HOME

·        
VixiaTrack
is the
database name that resides in SQLEXPRESS

• “Tables”
  is the
  parameter, which when passed generates the “Create table” script.

This
script generates the following “Create Database” script shown below. [Refer Fig
1.4]

SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[StockCriteriaHistory](
        [StockCriteriaHistoryID] [int] IDENTITY(1,1) NOT NULL,
        [LocationID] [int] NULL,
        [LocationDescription] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [SiteID] [int] NULL,
        [Site] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [WingID] [int] NULL,
        [Wing] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [BuildingID] [int] NULL,
        [Building] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [FloorNo] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DepartmentID] [int] NULL,
        [Department] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [RoomNo] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [RoomTypeID] [int] NULL,
        [RoomType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [VixiaLocationType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [VixiaLocationNo] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [TargetCount] [int] NULL,
        [LowAlertCount] [int] NULL,
        [LowAlarmCount] [int] NULL,
        [HighAlertCount] [int] NULL,
        [HighAlarmCount] [int] NULL,
        [EquipCategoryID] [int] NULL,
        [EquipCategory] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [EquipTypeID] [int] NULL,
        [EquipType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CreatedDt] [datetime] NULL,
        [CreatedID] [int] NULL,
        [UserName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Wing](
        [WingID] [int] IDENTITY(1,1) NOT NULL,
        [Description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CreatedID] [int] NULL,
        [CreatedDt] [datetime] NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[XMLStaging](
        [rdt] [nvarchar](364) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [us] [nvarchar](364) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ltid] [nvarchar](364) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ls] [nvarchar](364) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [eqtid] [nvarchar](364) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [es] [nvarchar](364) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [tp] [nvarchar](364) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[UploadedFile](
        [UploadedFileID] [int] IDENTITY(1,1) NOT NULL,
        [Description] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [UploadedUserID] [int] NULL,
        [UploadedDt] [datetime] NULL
) ON [PRIMARY]

Fig 1.4

You can redirect
the output to a file as shown below. [Refer Fig 1.5]

./ScriptSQL "HOME\SQLEXPRESS" "VixiaTrack" "Tables" > C:\MyScript1.sql

Fig 1.5

The
generated script is not stored in C:\MyScript1.sql. [Refer Fig 1.6]

Fig 1.6

Conclusion

Part 10
of this article series illustrated how to use a PowerShell script in conjunction
with SMO to generate a script for a database and tables by passing parameters.

»


See All Articles by Columnist MAK