Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10

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

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. Part 9 of this article series illustrated how to use PowerShell and SMO to generate a script for the database and tables.

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

Let us assume we want to have a PowerShell script that generates a “Create Database” script for a given database or a “Create object” script for all the objects from a given database. In addition, the server name and database name will be passed as parameters to the PowerShell script.

We can achieve this by creating a PowerShell script as shown below.

Create C:\PS\ScriptSQL.ps1 as shown. Refer Fig 1.0

param 
(
  [string] $ServerName,
  [string] $DatabaseName,
  [string] $scriptType 
)

[reflection.assembly]::LoadWithPartialName
 ("Microsoft.SqlServer.Smo") | out-null
$MyScripter=new-object 
 ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object 
 "Microsoft.SqlServer.Management.Smo.Server" "$ServerName" 
$db = $srv.Databases["$DatabaseName"]
$MyScripter.Server=$srv

if ($scriptType -eq "Database") 
{
echo "Database Scripts "
echo "-----------------"

$MyScripter.Script($srv.databases["$DatabaseName"])
}

if ($scriptType -eq "Tables") 
{
echo "Table Scripts "
echo "-----------------"

$MyScripter.Script($srv.Databases["$DatabaseName"].tables)
}

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

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

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

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

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

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]

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

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

The generated script is not stored in C:\MyScript1.sql. [Refer 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