/
08-ScriptData2.ps1
52 lines (43 loc) · 1.67 KB
/
08-ScriptData2.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
[string]$Server = 'localhost';
[string]$DBName = 'Minion';
[string]$SchemaName = 'Collector';
[string]$TableName = 'DBUsers';
[string]$FileBase = "C:\MyDBs"
<#
Scripting Data 2
You can also separate the schema and data collections by putting them
in separate scripts.
!!Legal Disclaimer: Don't do anything on a production system
without testing it first and you know the ramifications.
I am NOT responsible for any use or misuse of anything you
see here.
#>
##Load the assembly.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null;
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" #$server
##Create a var to hold the scripting options.
$so = New-Object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions');
$so.ScriptDrops = 1;
$so.IncludeIfNotExists = 1;
# Get the database and table objects
$DBList = $srv.Databases[$DBName]
$TableList = $DBList.Tables | ?{ $_.Schema -eq $SchemaName -and $_.name -eq $TableName }
$TableList | %{
###Script Exists and Drop.
"-- $(Get-Date)" | Out-File "$FileBase\$SchemaName`.$TableName.txt";
$so.ScriptDrops = 1;
$so.IncludeIfNotExists = 1;
$_.Script($so) | Out-File "$FileBase\$SchemaName`.$TableName.txt" -Append;
###Script Schema.
$so.ScriptDrops = 0;
$so.IncludeIfNotExists = 0;
$_.Script($so) | Out-File "$FileBase\$SchemaName`.$TableName.txt" -Append;
###Script data. Calling external script.
##Set the path cause we're calling the script from a diff path.
##If we were calling the script from it's path we wouldn't
##need to do this.
$Path = Split-Path -parent $PSCommandPath;
Set-Location "$Path";
./DataScripter.ps1 $Server $DBName $SchemaName $TableName $FileBase;
}