服务器支持最存储过程,SQL服务器 - 复制存储过程从一个分贝另一个

我最初发现这个帖子寻找一个解决方案，复制从我的远程生产数据库的存储过程，以我的本地开发数据库。在这个线程中成功使用了建议的方法之后，我意识到我越来越懒惰(或者足智多谋，无论你喜欢什么)，并希望自动化。我碰到this link，这被证明是非常有帮助的来到(谢谢vincpa)，我在扩展它，导致以下文件(schema_backup.ps1)：

$server = "servername"

$database = "databaseName"

$output_path = "D:\prod_schema_backup"

$login = "username"

$password = "password"

$schema = "dbo"

$table_path = "$output_path\table\"

$storedProcs_path = "$output_path\stp\"

$views_path = "$output_path\view\"

$udfs_path = "$output_path\udf\"

$textCatalog_path = "$output_path\fulltextcat\"

$udtts_path = "$output_path\udtt\"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null

$srvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection

$srvConn.ServerInstance = $server

$srvConn.LoginSecure = $false

$srvConn.Login = $login

$srvConn.Password = $password

$srv = New-Object Microsoft.SqlServer.Management.SMO.Server($srvConn)

$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")

$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")

$scripter = New-Object Microsoft.SqlServer.Management.SMO.Scripter($srvConn)

# Get the database and table objects

$db = $srv.Databases[$database]

$tbl = $db.tables | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }

$storedProcs = $db.StoredProcedures | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }

$views = $db.Views | Where-object { $_.schema -eq $schema }

$udfs = $db.UserDefinedFunctions | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }

$catlog = $db.FullTextCatalogs

$udtts = $db.UserDefinedTableTypes | Where-object { $_.schema -eq $schema }

# Set scripter options to ensure only data is scripted

$scripter.Options.ScriptSchema = $true;

$scripter.Options.ScriptData = $false;

#Exclude GOs after every line

$scripter.Options.NoCommandTerminator = $false;

$scripter.Options.ToFileOnly = $true

$scripter.Options.AllowSystemObjects = $false

$scripter.Options.Permissions = $true

$scripter.Options.DriAllConstraints = $true

$scripter.Options.SchemaQualify = $true

$scripter.Options.AnsiFile = $true

$scripter.Options.SchemaQualifyForeignKeysReferences = $true

$scripter.Options.Indexes = $true

$scripter.Options.DriIndexes = $true

$scripter.Options.DriClustered = $true

$scripter.Options.DriNonClustered = $true

$scripter.Options.NonClusteredIndexes = $true

$scripter.Options.ClusteredIndexes = $true

$scripter.Options.FullTextIndexes = $true

$scripter.Options.EnforceScriptingOptions = $true

function CopyObjectsToFiles($objects, $outDir) {

#clear out before

Remove-Item $outDir* -Force -Recurse

if (-not (Test-Path $outDir)) {

[System.IO.Directory]::CreateDirectory($outDir)

}

foreach ($o in $objects) {

if ($o -ne $null) {

$schemaPrefix = ""

if ($o.Schema -ne $null -and $o.Schema -ne "") {

$schemaPrefix = $o.Schema + "."

}

#removed the next line so I can use the filename to drop the stored proc

#on the destination and recreate it

#$scripter.Options.FileName = $outDir + $schemaPrefix + $o.Name + ".sql"

$scripter.Options.FileName = $outDir + $schemaPrefix + $o.Name

Write-Host "Writing " $scripter.Options.FileName

$scripter.EnumScript($o)

}

}

}

# Output the scripts

CopyObjectsToFiles $tbl $table_path

CopyObjectsToFiles $storedProcs $storedProcs_path

CopyObjectsToFiles $views $views_path

CopyObjectsToFiles $catlog $textCatalog_path

CopyObjectsToFiles $udtts $udtts_path

CopyObjectsToFiles $udfs $udfs_path

Write-Host "Finished at" (Get-Date)

$srv.ConnectionContext.Disconnect()

我有一个调用这个.bat文件，并从任务计划程序调用。在调用Powershell的文件后，我有：

for /f %f in ('dir /b d:\prod_schema_backup\stp\') do sqlcmd /S localhost /d dest_db /Q "DROP PROCEDURE %f"

该行将走通的目录，并把它打算重新创建的过程。如果这不是一个开发环境，我不会喜欢以这种方式编程式地删除过程。然后我重命名所有的存储过程文件。SQL：

powershell Dir d:\prod_schema_backup\stp\ | Rename-Item -NewName { $_.name + ".sql" }

然后运行：

for /f %f in ('dir /b d:\prod_schema_backup\stp\') do sqlcmd /S localhost /d dest_db /E /i "%f".sql

这遍历所有的.sql文件，并重新创建存储过程。我希望这个任何部分都能证明对某人有帮助。