posh.sqlpsx | Export Table Schema & Data as Script

#匯出資料表schema以及資料

#這樣就可以連資料都匯出了!!

#順序:create table語法部分 / alter建立FK/CONSTRAINTS部分 / INSERT語法部分

 

if((Get-Module sqlpsx) -eq $null){

        Import-Module sqlpsx -Force

}

    

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" 

    

# Load needed assemblies  

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

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")| Out-Null;  

 

    

$sqlhost = "localhost"

$instance = "mssqlserver"

$port = 1433

 

$sqlsrv = Get-SqlServer -sqlserver "$sqlhost\$instance,$port" -username sa -password "/'],lp123"

 

#$srvcon = Get-SqlConnection -sqlserver "$sqlhost\$instance,$port" -username sa -password "/'],lp123"

 

 

$sss = Get-SqlDatabase -sqlserver $sqlsrv -dbname DB -force

 

 

$scriptingOptions = New-SqlScriptingOptions

$scriptingOptions.Triggers = $true

$scriptingOptions.Permissions = $false

$scriptingOptions.ClusteredIndexes = $true

$scriptingOptions.DriAllConstraints = $true

$scriptingOptions.DriForeignKeys = $true

$scriptingOptions.DriAllKeys = $true

$scriptingOptions.DriPrimaryKey = $true

$scriptingOptions.FullTextCatalogs = $true

$scriptingOptions.FullTextIndexes = $true

$scriptingOptions.FullTextStopLists = $true

$scriptingOptions.IncludeFullTextCatalogRootPath = $true

$scriptingOptions.ScriptData = $true

 

#最後面改成以下三行

 

#$scripts = Get-SqlTable -Database $sss | Get-SqlScripter -scriptingOptions $scriptingOptions

#$scripts | ?{$_ -NOTMATCH "^ALTER"}

#$scripts | ?{$_ -MATCH "^ALTER"}

 

#SCRIPT DATA

$SCRI = New-Object Microsoft.SqlServer.Management.Smo.SCRIPTER($sqlsrv)

$SCRI.Options = $scriptingOptions

 

$TBL = Get-SqlTable -Database $sss 

$scripts = $SCRI.EnumScript($($TBL | %{$_.URN}))

$scripts | ?{($_ -NOTMATCH "^ALTER") -AND ($_ -NOTMATCH "^INSERT")}

$scripts | ?{$_ -MATCH "^ALTER"}

$scripts | ?{$_ -MATCH "^INSERT"}

Comments