Contents

1 방법1
2 방법2
3 job script


[edit]

1 방법1 #

 # set "Option Explicit" to catch subtle errors 

set-psdebug -strict
 
$DirectoryToSaveTo='d:\script_backup\' # local directory to save build-scripts to
 
$servername='localhost' # server name and instance
 
$Database='stg' # the database to copy from

$date = Get-Date -Format yyyyMMdd
 
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
 

Trap {
 
# Handle the error
 
$err = $_.Exception
 
write-host $err.Message
 
while( $err.InnerException ) {
 
   $err = $err.InnerException
 
   write-host $err.Message
 
   };
 
# End the script.
 
break
 
}
 
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
 
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
 
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
 
   }
 
$My='Microsoft.SqlServer.Management.Smo'
 
$s = new-object ("$My.Server") $ServerName # get the server.
 
$Server=$s.netname -replace '[\\\/\:\.]',' ' # remove characters that can cause problems
 
$instance = $s.instanceName -replace '[\\\/\:\.]',' ' # ditto
 
$DatabaseName =$database -replace '[\\\/\:\.]',' ' # ditto
 

#$DirectoryToSaveTo=$DirectoryToSaveTo+$Server+'\'+$Instance+'\' # database scripts are local on client
$DirectoryToSaveTo=$DirectoryToSaveTo+'\'+$Database+'\' # database scripts are local on client
 
if (!( Test-Path -path "$DirectoryToSaveTo" )) # create it if not existing
 
       {$progress ="attempting to create directory $DirectoryToSaveTo"
 
              Try { New-Item "$DirectoryToSaveTo" -type directory | out-null } 

             Catch [system.exception]{
 
       Write-Error "error while $progress. $_"
 
                return
 
                } 

           }
 
<# now we will use the canteen system of SMO to specify what we want from the script. It is best to have a list of the 

defaults to hand and just override the defaults where necessary, but there is a chance that a later revision of SMO could 

change the defaults, so beware! #>
 
$CreationScriptOptions = new-object ("$My.ScriptingOptions") 

$CreationScriptOptions.ExtendedProperties= $true # yes, we want these
 
$CreationScriptOptions.DRIAll= $true # and all the constraints 

$CreationScriptOptions.Indexes= $true # Yup, these would be nice
 
$CreationScriptOptions.Triggers= $true # This should be included when scripting a database
 
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
 
$CreationScriptOptions.Filename = "$($DirectoryToSaveTo)$($DatabaseName)$($date).sql"; 

# we have to write to a file to get the GOs
 
$CreationScriptOptions.IncludeHeaders = $true; # of course
 
$CreationScriptOptions.ToFileOnly = $true # no need of string output as well
 
$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
 
$transfer = new-object ("$My.Transfer") $s.Databases[$Database]
 
$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
 
$scripter = new-object ("$My.Scripter") $s # script out the database creation
 
$scripter.options=$CreationScriptOptions # with the same options
 
$scripter.Script($s.Databases[$Database]) # do it
 
# add the database object build script
 
$transfer.options.AppendToFile=$true
 
$transfer.options.ScriptDrops=$false#$true
 
$transfer.EnumScriptTransfer()
 
$transfer.options.ScriptDrops=$false
 
$transfer.EnumScriptTransfer()
 
"All written to $($DirectoryToSaveTo)$($DatabaseName)_Build.sql"


sqlps -Command "c:\script.ps1"


[edit]

2 방법2 #

--https://gist.github.com/cheynewallace/9558179
# Usage:  powershell ExportSchema.ps1 "SERVERNAME" "DATABASE" "C:\<YourOutputPath>"


# Start Script
Set-ExecutionPolicy RemoteSigned

# Set-ExecutionPolicy -ExecutionPolicy:Unrestricted -Scope:LocalMachine
function GenerateDBScript([string]$serverName, [string]$dbname, [string]$scriptpath)
{
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  [System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
  $srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
  $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
  $db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
  $db = $srv.Databases[$dbname]
  $scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
  $deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
  $scr.Server = $srv
  $options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
  $options.AllowSystemObjects = $false
  $options.IncludeDatabaseContext = $true
  $options.IncludeIfNotExists = $false
  $options.ClusteredIndexes = $true
  $options.Default = $true
  $options.DriAll = $true
  $options.Indexes = $true
  $options.NonClusteredIndexes = $true
  $options.IncludeHeaders = $false
  $options.ToFileOnly = $true
  $options.AppendToFile = $true
  $options.ScriptDrops = $false 

  # Set options for SMO.Scripter
  $scr.Options = $options

  #=============
  # Tables
  #=============
  $options.FileName = $scriptpath + "\$($dbname)_tables.sql"
  New-Item $options.FileName -type file -force | Out-Null
  Foreach ($tb in $db.Tables)
  {
   If ($tb.IsSystemObject -eq $FALSE)
   {
    $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection
    $smoObjects.Add($tb.Urn)
    $scr.Script($smoObjects)
   }
  }

  #=============
  # Views
  #=============
  $options.FileName = $scriptpath + "\$($dbname)_views.sql"
  New-Item $options.FileName -type file -force | Out-Null
  $views = $db.Views | where {$_.IsSystemObject -eq $false}
  Foreach ($view in $views)
  {
    if ($views -ne $null)
    {
     $scr.Script($view)
   }
  }

  #=============
  # StoredProcedures
  #=============
  $StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
  $options.FileName = $scriptpath + "\$($dbname)_stored_procs.sql"
  New-Item $options.FileName -type file -force | Out-Null
  Foreach ($StoredProcedure in $StoredProcedures)
  {
    if ($StoredProcedures -ne $null)
    {   
     $scr.Script($StoredProcedure)
   }
  } 

  #=============
  # Functions
  #=============
  $UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
  $options.FileName = $scriptpath + "\$($dbname)_functions.sql"
  New-Item $options.FileName -type file -force | Out-Null
  Foreach ($function in $UserDefinedFunctions)
  {
    if ($UserDefinedFunctions -ne $null)
    {
     $scr.Script($function)
   }
  } 

  #=============
  # DBTriggers
  #=============
  $DBTriggers = $db.Triggers
  $options.FileName = $scriptpath + "\$($dbname)_db_triggers.sql"
  New-Item $options.FileName -type file -force | Out-Null
  foreach ($trigger in $db.triggers)
  {
    if ($DBTriggers -ne $null)
    {
      $scr.Script($DBTriggers)
    }
  }

  #=============
  # Table Triggers
  #=============
  $options.FileName = $scriptpath + "\$($dbname)_table_triggers.sql"
  New-Item $options.FileName -type file -force | Out-Null
  Foreach ($tb in $db.Tables)
  {     
    if($tb.triggers -ne $null)
    {
      foreach ($trigger in $tb.triggers)
      {
        $scr.Script($trigger)
      }
    }
  } 
}

#=============
# Execute
#=============
GenerateDBScript $args[0] $args[1] $args[2]

[edit]

3 job script #