[sql] SQL Server-한 db에서 다른 db로 저장 프로 시저 복사

원래이 게시물은 원격 프로덕션 데이터베이스에서 로컬 개발 데이터베이스로 저장 프로 시저를 복사하는 솔루션을 찾고있었습니다. 이 스레드에서 제안 된 접근 방식을 사용하여 성공한 후 저는 점점 게으르고 (또는 선호하는대로 수완이 풍부 해져) 이것이 자동화되기를 원했습니다. 내가 건너 온 이 링크 매우 도움이 될 입증, (당신 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 파일을 반복하고 저장 프로 시저를 다시 만듭니다. 이 부분이 누군가에게 도움이되기를 바랍니다.