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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles