Powershell:SQL Server Management Studio 脚本生成器 [英] Powershell: SQL Server Management Studio Script Generator
问题描述
我使用集成在 Microsoft SQL Server Management Studio 中的脚本生成器为整个数据库生成导入脚本.
I use the Script Generator which is integrated in the Microsoft SQL Server Management Studio to generate an import script for a whole database.
我必须在脚本中做一些替换,我用 Powershell 做的.现在我想自动化生成.有没有办法完全执行这个脚本生成器工具(并在屏幕截图上设置一些选项 - 在我的情况下为仅数据")?或者(如果这是不可能的)我可以从 ps 脚本自动打开这个工具窗口,这样我就不必打开 Management Studio,选择数据库,......?
I have to do some replacements in the script which I do with Powershell. Now I want to automate the generation. Is there a way to execute exactly this Script Generator Tool (and setting some options as on the screenshot - in my case 'Data only')? Or (if this isn't possible) can I open this tool window automatically from a ps script so I don't have to open the Management Studio, selecting the DB, ...?
我发现了一些在 Powershell 中手动"构建脚本文件的脚本,但这并不是我想要的.
I found some scripts which 'manually' build the script file in Powershell but that's not exactly what I'm looking for.
谢谢!
推荐答案
这个问题已经存在一段时间了,您现在可能已经找到了答案,但是对于那些正在寻找一种简单的方法来做到这一点的人来说,当前版本的 SQL服务器 Powershell 模块具有支持 SMO 中的此功能的本机命令和方法.
This question's been here awhile and you've probably found your answer by now, but for those looking for a simple way to do this, the current versions of SQL server Powershell modules have native commands and methods that support this functionality from SMO.
您可以使用 Get-SqlDatabase 和诸如 .Script() 和 .EnumScript() 之类的方法.
You can use Get-SqlDatabase and methods such as .Script() and .EnumScript().
例如,这将为用户定义的函数生成 CREATE 脚本并将其保存到文件:
For example, this will generate CREATE scripts for user defined functions and save it to file:
$Database = Get-SqlDatabase -ServerInstance $YourSqlServer -Name $YourDatabaseName
$MyFuncs = $Database.UserDefinedFunctions | Where Schema -eq "dbo"
$MyFuncs.Script() | Out-File -FilePath ".\SqlScripts\MyFunctions.sql"
如果您想要脚本数据和索引、键、触发器等元素,您必须指定脚本选项,如下所示:
If you want to script data and elements like indexes, keys, triggers, etc. you will have to specify the scripting options, like this:
$scriptOptions = New-Object -TypeName Microsoft.SqlServer.Management.Smo.ScriptingOptions
$scriptOptions.NoCollation = $True
$scriptOptions.Indexes = $True
$scriptOptions.Triggers = $True
$scriptOptions.DriAll = $True
$scriptOptions.ScriptData = $True
$Database.Tables.EnumScript($scriptOptions) | Out-File -FilePath ".\AllMyTables.sql"
请注意,Script() 方法不支持脚本数据.对表格使用 EnumScript().
Note that the Script() method doesn't support scripting data. Use EnumScript() for tables.
如果您只想编写脚本数据,如所问,您可以尝试 $scriptOptions.ScriptData = $True
和 $scriptOptions.ScriptSchema = $False
.
If you want to script data only, as asked, you can try $scriptOptions.ScriptData = $True
and $scriptOptions.ScriptSchema = $False
.
这篇关于Powershell:SQL Server Management Studio 脚本生成器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!