我希望能够自动化脚本生成(在SSMS中--> 任务 --> 生成脚本)。我已经了解到SQL Server 2008不支持数据库发布向导(包括SQLPUBWIZ SCRIPT),但可以使用SQL Server 2008中的SMO进行自动化。我对SMO和如何使用SMO一无所知,您能否给我一些建议(资源等)如何开始?
我希望能够自动化脚本生成(在SSMS中--> 任务 --> 生成脚本)。我已经了解到SQL Server 2008不支持数据库发布向导(包括SQLPUBWIZ SCRIPT),但可以使用SQL Server 2008中的SMO进行自动化。我对SMO和如何使用SMO一无所知,您能否给我一些建议(资源等)如何开始?
Scripter
类。所有其他工具(如SSMS)都使用该类来生成对象创建脚本。在MSDN上有一个使用示例:{
//Connect to the local, default instance of SQL Server.
Server srv = new Server();
//Reference the AdventureWorks2008R2 database.
Database db = srv.Databases["AdventureWorks2008R2"];
//Define a Scripter object and set the required scripting options.
Scripter scrp = new Scripter(srv);
scrp.Options.ScriptDrops = false;
scrp.Options.WithDependencies = true;
//Iterate through the tables in database and script each one. Display the script.
//Note that the StringCollection type needs the System.Collections.Specialized namespace to be included.
Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] smoObjects = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[1] ;
foreach (Table tb in db.Tables) {
smoObjects[0] = tb.Urn;
if (tb.IsSystemObject == false) {
System.Collections.Specialized.StringCollection sc;
sc = scrp.Script(smoObjects);
foreach ( string st in sc) {
Console.WriteLine(st);
}
}
}
}
虽然这个问题已经得到了准确的回答,但我花了几天时间编写一个脚本,用于生成我关心的数据库服务器上所有对象的脚本。以下是我的代码,以防对其他人有用。
# Load needed assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") | Out-Null;
#Specify target server and databases.
$sql_server = "SomeServerName"
$SMOserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList "$sql_server"
$databases = $SMOserver.Databases
$BaseSavePath = "T:\SomeFilePath\" + $sql_server + "\"
#Remove existing objects.
Remove-Item $BaseSavePath -Recurse
#Script server-level objects.
$ServerSavePath = $BaseSavePath
$ServerObjects = $SMOserver.BackupDevices
$ServerObjects += $SMOserver.Endpoints
$ServerObjects += $SMOserver.JobServer.Jobs
$ServerObjects += $SMOserver.LinkedServers
$ServerObjects += $SMOserver.Triggers
foreach ($ScriptThis in $ServerObjects | where { !($_.IsSystemObject) }) {
#Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name
$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
$scriptr.Options.AppendToFile = $True
$scriptr.Options.AllowSystemObjects = $False
$scriptr.Options.ClusteredIndexes = $True
$scriptr.Options.DriAll = $True
$scriptr.Options.ScriptDrops = $False
$scriptr.Options.IncludeHeaders = $False
$scriptr.Options.ToFileOnly = $True
$scriptr.Options.Indexes = $True
$scriptr.Options.Permissions = $True
$scriptr.Options.WithDependencies = $False
<#Script the Drop too#>
$ScriptDrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
$ScriptDrop.Options.AppendToFile = $True
$ScriptDrop.Options.AllowSystemObjects = $False
$ScriptDrop.Options.ClusteredIndexes = $True
$ScriptDrop.Options.DriAll = $True
$ScriptDrop.Options.ScriptDrops = $True
$ScriptDrop.Options.IncludeHeaders = $False
$ScriptDrop.Options.ToFileOnly = $True
$ScriptDrop.Options.Indexes = $True
$ScriptDrop.Options.WithDependencies = $False
<#This section builds folder structures. Remove the date folder if you want to overwrite#>
$TypeFolder = $ScriptThis.GetType().Name
if ((Test-Path -Path "$ServerSavePath\$TypeFolder") -eq "true") `
{ "Scripting Out $TypeFolder $ScriptThis" } `
else { new-item -type directory -name "$TypeFolder"-path "$ServerSavePath" }
$ScriptFile = $ScriptThis -replace ":", "-" -replace "\\", "-"
$ScriptDrop.Options.FileName = $ServerSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"
$scriptr.Options.FileName = $ServerSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"
#This is where each object actually gets scripted one at a time.
$ScriptDrop.Script($ScriptThis)
$scriptr.Script($ScriptThis)
} #This ends the object scripting loop at the server level.
#Script database-level objects.
foreach ($db in $databases) {
$DatabaseObjects = $db.ApplicationRoles
$DatabaseObjects += $db.Assemblies
$DatabaseObjects += $db.ExtendedStoredProcedures
$DatabaseObjects += $db.ExtendedProperties
$DatabaseObjects += $db.PartitionFunctions
$DatabaseObjects += $db.PartitionSchemes
$DatabaseObjects += $db.Roles
$DatabaseObjects += $db.Rules
$DatabaseObjects += $db.Schemas
$DatabaseObjects += $db.StoredProcedures
$DatabaseObjects += $db.Synonyms
$DatabaseObjects += $db.Tables
$DatabaseObjects += $db.Triggers
$DatabaseObjects += $db.UserDefinedAggregates
$DatabaseObjects += $db.UserDefinedDataTypes
$DatabaseObjects += $db.UserDefinedFunctions
$DatabaseObjects += $db.UserDefinedTableTypes
$DatabaseObjects += $db.UserDefinedTypes
$DatabaseObjects += $db.Users
$DatabaseObjects += $db.Views
#Build this portion of the directory structure out here. Remove the existing directory and its contents first.
$DatabaseSavePath = $BaseSavePath + "Databases\" + $db.Name
new-item -type directory -path "$DatabaseSavePath"
foreach ($ScriptThis in $DatabaseObjects | where { !($_.IsSystemObject) }) {
#Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name
$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
$scriptr.Options.AppendToFile = $True
$scriptr.Options.AllowSystemObjects = $False
$scriptr.Options.ClusteredIndexes = $True
$scriptr.Options.DriAll = $True
$scriptr.Options.ScriptDrops = $False
$scriptr.Options.IncludeHeaders = $False
$scriptr.Options.ToFileOnly = $True
$scriptr.Options.Indexes = $True
$scriptr.Options.Permissions = $True
$scriptr.Options.WithDependencies = $False
<#Script the Drop too#>
$ScriptDrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
$ScriptDrop.Options.AppendToFile = $True
$ScriptDrop.Options.AllowSystemObjects = $False
$ScriptDrop.Options.ClusteredIndexes = $True
$ScriptDrop.Options.DriAll = $True
$ScriptDrop.Options.ScriptDrops = $True
$ScriptDrop.Options.IncludeHeaders = $False
$ScriptDrop.Options.ToFileOnly = $True
$ScriptDrop.Options.Indexes = $True
$ScriptDrop.Options.WithDependencies = $False
<#This section builds folder structures. Remove the date folder if you want to overwrite#>
$TypeFolder = $ScriptThis.GetType().Name
if ((Test-Path -Path "$DatabaseSavePath\$TypeFolder") -eq "true") `
{ "Scripting Out $TypeFolder $ScriptThis" } `
else { new-item -type directory -name "$TypeFolder"-path "$DatabaseSavePath" }
$ScriptFile = $ScriptThis -replace ":", "-" -replace "\\", "-"
$ScriptDrop.Options.FileName = $DatabaseSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"
$scriptr.Options.FileName = $DatabaseSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"
#This is where each object actually gets scripted one at a time.
$ScriptDrop.Script($ScriptThis)
$scriptr.Script($ScriptThis)
} #This ends the object scripting loop.
} #This ends the database loop.
有几种方法可以脚本化数据库中的所有对象,但其中最简单的一种是使用SMO传输类。以下是一些PowerShell代码,可用于脚本化所有对象:
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$sourceSrv = "$env:computername\sql2k8"
$sourceDb = "Northwind"
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sourceSrv
$db = $server.Databases[$sourceDb]
$transfer = new-object ("Microsoft.SqlServer.Management.Smo.Transfer") $db
$transfer.CopyAllObjects = $true
$transfer.DropDestinationObjectsFirst = $true
$transfer.CopySchema = $true
$transfer.Options.IncludeIfNotExists = $true
$transfer.ScriptTransfer()