Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 9

The MAK

Sử dụng PowerShell và SMO để tạo kịch bản SQL Server

Phần 1 và phần 2 của loạt bài này chúng tôi đã giới thiệu về cài đặt PowerShell và SMO, WMI cmdlets đơn giản. Trong phần 3 chúng tôi đã giới thiệu về cách lập kịch bản cho PowerShell và kết nối SQL Server.

Phần 4 đã giới thiệu cách sử dụng kịch bản PowerShell để lặp trong nội dung file và kết nối các máy chủ khác nhau. Phần 5 là giới thiệu về cách tạo cơ sở dữ liệu SQL Server bằng PowerShell và SMO. Phần 6 nghiên cứu vấn đề backup một cơ sở dữ liệu SQL Server bằng PowerShell và SMO, phần 7 là cách tạo một danh sách các đối tượng trong một cơ sở dữ liệu và phần 8 là cách liệt kê tất cả các thuộc tính của các đối tượng trong cơ sở dữ liệu bằng PowerShell và SMO.

Trong phần 9 của loạt bài này, chúng tôi sẽ giới thiệu cho các bạn cách sử dụng PowerShell kết hợp với SMO để tạo kịch bản SQL Server. Tạo kịch bản SQL Server là một nhiệm vụ quan trọng cho quản trị viên và các chuyên gia phát triển cơ sở dữ liệu SQL Server.

Phương pháp 1

Hãy giả dụ rằng chúng ta muốn tạo một kịch bản “Create Database” cho cơ sở dữ liệu AdventureWorks từ máy chủ “HOME\SQLEXPRESS”. Thực thi cmdlets dưới đây (xem hình 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" "HOME\SQLEXPRESS"
$MyScripter.Server=$srv
$MyScripter.Script($srv.databases["AdventureWorks"])

Hình 1.1

Lệnh này sẽ tạo một kịch bản “Create Database” cho cơ sở dữ liệu AdventureWorks như thể hiện bên dưới (xem hình 1.2)

CREATE DATABASE [AdventureWorks] ON PRIMARY
( NAME = N'AdventureWorks_Data',
FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\
MSSQL\Data\AdventureWorks_Data.mdf' ,
SIZE = 180992KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N'AdventureWorks_Log', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\
MSSQL\DATA\AdventureWorks_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

Hình 1.2

Phương pháp 2

Chúng ta hãy giả dụ rằng muốn tạo một kịch bản cho tất cả các bảng trong cơ sở dữ liệu AdventureWorks từ máy chủ “HOME\SQLEXPRESS”. Thực thi cmdlets dưới đây (tham khảo hình 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" "HOME\SQLEXPRESS"
$MyScripter.Server=$srv
$MyScripter.Script($srv.Databases["adventureworks"].tables)

Hình 1.3

Lệnh này tạo kịch bản CREATE TABLE cho các bảng trong cơ sở dữ liệu AdventureWorks từ máy chủ “HOME\SQLEXPRESS”. Thực thi cmdlets dưới đây (tham khảo hình 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]
......
......

Hình 1.4

Phương pháp 3

Việc viết kịch bản cơ sở dữ liệu SQL Server và các đối tượng của nó có nhiều tùy chọn. Các tùy chọn này có thể được thay đổi bằng cách điều chỉnh cờ có sẵn thành “on” hoặc “off”. Thực thi cmdlets dưới đây để xem tất cả các tùy chọn có sẵn trong kịch bản (tham khảo hình 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" "HOME\SQLEXPRESS"
$MyScripter.Server=$srv
$so=$MyScripter.Options
$so

Hình 1.5

Lệnh trên hiển thị tất cả các tùy chọn có sẵn cho kịch bản mà bạn có thể thay đổi như thể hiện bên dưới (tham khảo hình 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

Hình 1.6

Lúc này chúng ta hãy thử lưu kịch bản đã tạo “Create Database” vào một file bằng tùy chọn Scripting. Thực thi cmdlets như phần bên dưới (tham khảo hình 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" "HOME\SQLEXPRESS"
$MyScripter.Server=$srv
$so=$MyScripter.Options
$so.FileName="C:\MyDatabaseScript.sql"
$MyScripter.Script($srv.Databases["adventureworks"])

Hình 1.7

Lệnh này sẽ lưu kịch bản “Create Database” trên vào file C:\MyDatabaseScript.sql, như thể hiện trong hình 1.8 và hình 1.9.

Hình 1.8

Hình 1.9

Kết luận

Phần 9 của loạt bài này giới thiệu cho các bạn cách sử dụng PowerShell và SMO để tạo kịch bản cho cơ sở dữ liệu và các bảng. Nó cũng giới thiệu cả cách sử dụng các tùy chọn kịch bản để ghi vào một file dữ liệu. Phần 10 chúng tôi sẽ giới thiệu thêm về các tùy chọn kịch bản và cách tạo các kịch bản PowerShell để từ đó tạo kịch bản SQL Server bằng các tham số chuyển.

Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 10
Microsoft Windows PowerShell và SQL Server 2005 SMO – Phần 11

Thứ Năm, 17/04/2008 14:14

5 ★ 2 👨 1.608