Created
March 4, 2019 18:10
PowerShell script that saves every SQL Server table in a database to an Extended JSON file
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
set-psdebug -strict | |
$ErrorActionPreference = "stop" # | |
$MS = 'Microsoft.SQLServer' | |
$My = "$MS.Management.Smo" | |
$Errors = @() | |
#You need to fill this lot in | |
$SourceServer = 'MyServer' | |
$Username = 'MyUserID' | |
$Database = 'TheDatabase' | |
$OutputPath = "MyPathToTheDirectoryForTheOoutoutFiles" | |
#Load SMO assemblies and so on | |
Import-Module sqlserver -DisableNameChecking -ErrorAction silentlycontinue -ErrorVariable +Errors | |
#Does the output path exist? | |
if (-not (Test-Path -PathType Container $OutputPath)) | |
{ | |
# we create the directory if it doesn't already exist | |
New-Item -ItemType Directory -Force -Path $OutputPath ` | |
-ErrorAction silentlycontinue -ErrorVariable +Errors; | |
} | |
<# make sure all the connections are servicable #> | |
if ($Errors.Count -eq 0) | |
{ | |
$conn = new-object "$MS.Management.Common.ServerConnection" | |
$conn.ServerInstance = $SourceServer | |
if ($username -ieq '') | |
{ | |
$conn.LoginSecure = $true; | |
} | |
else | |
{ | |
<# This is more elaborate a process than you might expect because we can't assume that we can use Windows authentication, because of Azure, remote servers outside the | |
domain, and other such complications. We can't ever keep passwords for SQL Server | |
authentication as part of the static script data. At this stage, we ask for passwords | |
if they aren't known, and otherwise store them as secure strings on file in the user | |
area, protected by the workstation security. | |
#> | |
#create a connection object to manage credentials | |
$encryptedPasswordFile = "$env:USERPROFILE\$($Username)-$SourceServer.txt" | |
# test to see if we know about the password un a secure string stored in the user area | |
if (Test-Path -path $encryptedPasswordFile -PathType leaf) | |
{ | |
#has already got this set for this login so fetch it | |
$encrypted = Get-Content $encryptedPasswordFile | ConvertTo-SecureString | |
$Credentials = New-Object System.Management.Automation.PsCredential($Username, $encrypted) | |
} | |
else #then we have to ask the user for it | |
{ | |
#hasn't got this set for this login | |
$Credentials = get-credential -Credential $Username | |
$Credentials.Password | ConvertFrom-SecureString | | |
Set-Content "$env:USERPROFILE\$SourceLogin-$SourceServerName.txt" | |
} | |
$conn.LoginSecure = $false; | |
$conn.Login = $Credentials.UserName; | |
$conn.SecurePassword = $Credentials.Password; | |
} | |
} | |
if ($errors.Count -eq 0) | |
#if there were errors, then it gives up at this stage and reports the errors. | |
{ | |
$DataSourceInstanceObject = new-object ("$My.Server") $conn | |
if ($DataSourceInstanceObject.Version -eq $null) | |
{ Throw "Can't find the instance $SourceServer" }; | |
$DataDatabaseObject = $DataSourceInstanceObject.Databases[$Database] | |
if ($DataDatabaseObject.name -ne $Database) | |
{ | |
Throw "Can't find the database '$Database' in instance $SourceServer" | |
}; | |
$procedure = @' | |
CREATE OR ALTER PROCEDURE #SaveExtendedJsonDataFromTable | |
/** | |
Summary: > | |
This gets the Extended JSON data from a table. You can specify | |
it either by the database.schema.table 'tablespec, or do it | |
individually. | |
Author: phil factor | |
Date: 04/02/2019 | |
Examples: > | |
USE pubs | |
DECLARE @Json NVARCHAR(MAX) | |
EXECUTE #SaveExtendedJsonDataFromTable | |
@database='pubs', | |
@Schema ='dbo', | |
@table= 'jobs', | |
@JSONData=@json OUTPUT | |
PRINT @Json | |
Returns: > | |
The JSON data | |
**/ | |
(@database sysname = NULL, @Schema sysname = NULL, @table sysname = NULL, | |
@tableSpec sysname, @jsonData NVARCHAR(MAX) OUTPUT | |
) | |
AS | |
BEGIN | |
DECLARE @Data NVARCHAR(MAX); | |
IF Coalesce(@table, @Tablespec) IS NULL | |
OR Coalesce(@Schema, @Tablespec) IS NULL | |
RAISERROR('{"error":"must have the table details"}', 16, 1); | |
IF @table IS NULL SELECT @table = ParseName(@Tablespec, 1); | |
IF @Schema IS NULL SELECT @Schema = ParseName(@Tablespec, 2); | |
IF @database IS NULL | |
SELECT @database = Coalesce(ParseName(@Tablespec, 3), Db_Name()); | |
IF @table IS NULL OR @Schema IS NULL OR @database IS NULL | |
RAISERROR('{"error":"must have the table details"}', 16, 1); | |
DECLARE @SourceCode NVARCHAR(255) = | |
( | |
SELECT 'SELECT * FROM ' + QuoteName(@database) + '.' | |
+ QuoteName(@Schema) + '.' + QuoteName(@table) | |
); | |
DECLARE @a_unique_key bit | |
DECLARE @HowManyUniqueKeys INT | |
SELECT @HowManyUniqueKeys= Sum(Convert(INT,is_part_of_unique_key)) | |
FROM sys.dm_exec_describe_first_result_set(@SourceCode, NULL, 1) | |
SELECT @a_unique_key= CASE WHEN @HowManyUniqueKeys = 1 THEN 1 ELSE 0 END | |
DECLARE @params NVARCHAR(MAX); | |
SELECT @params = | |
String_Agg( | |
CASE WHEN system_type_id IN | |
( 35, -- text | |
99, -- ntext | |
98, -- sql_variant | |
167, -- varchar | |
231, -- nvarchar | |
239, -- nchar | |
175, -- char | |
36, -- uniqueidentifier | |
59, -- real | |
62, -- float | |
104, -- bit | |
241 | |
) -- xml | |
THEN | |
CASE WHEN @a_unique_key=1 AND is_part_of_unique_key =1 THEN name+ ' as "_id", ' | |
ELSE '' END +QuoteName(name) | |
WHEN user_type_id IN (128, 129, 130) THEN | |
'convert(nvarchar(100),' + name + ') as "' + name + '"' | |
--hierarchyid (128) geometry (130) and geography types (129) can be coerced. | |
--------binary | |
WHEN system_type_id IN | |
( 165, -- varbinary | |
173-- binary | |
) | |
--THEN name + ' as "' + name + '.$binary.hex", ''80'' as "' + name + '.$binary.subType"' | |
THEN name --I gave up. Extended json binary form is just awful | |
WHEN system_type_id = 34 THEN --image | |
'convert(varbinary(max),' + name + ') as "' + name + '"' | |
WHEN system_type_id IN (35) THEN --35 is text | |
'convert(varchar(max),' + name + ') as "' + name + '"' | |
WHEN system_type_id IN (99) THEN --ntext | |
'convert(nvarchar(max),' + name + ') as "' + name + '"' | |
--------numberInt | |
WHEN system_type_id IN | |
( 48, -- tinyint | |
56, -- int | |
52 -- smallint | |
) | |
THEN | |
CASE WHEN @a_unique_key=1 AND is_part_of_unique_key =1 THEN name+ ' as "_id", ' | |
ELSE '' END + | |
'convert(varchar(50),' + name + ') as "' + name + '.$numberInt"' | |
--------numberLong | |
WHEN system_type_id = 127 -- bigint | |
THEN | |
CASE WHEN @a_unique_key=1 AND is_part_of_unique_key =1 THEN name+ ' as "_id", ' | |
ELSE '' END + | |
'convert(varchar(50),' + name + ') as "' + name + '.$numberLong"' | |
--------date | |
WHEN system_type_id IN | |
( 40, -- date | |
41, -- time | |
42, -- datetime2 | |
43, -- datetimeoffset | |
58, -- smalldatetime | |
61, -- datetime | |
189 | |
) -- timestamp | |
THEN | |
'convert(datetimeoffset,convert(datetime2(0),' + name + ')) as "' + name + '.$date"' | |
-------numberDecimal | |
WHEN system_type_id IN | |
( 106, -- decimal | |
108, -- numeric | |
122, -- smallmoney | |
60 | |
) -- money | |
THEN | |
'convert(varchar(50),' + name + ') as "' + name + '.$numberDecimal"' | |
ELSE QuoteName(name) | |
END, | |
', ' | |
) | |
FROM sys.dm_exec_describe_first_result_set(@SourceCode, NULL, 1); | |
DECLARE @expression NVARCHAR(max) = | |
' | |
USE ' + @database + ' | |
SELECT @TheData=(SELECT ' + @params + ' FROM ' + QuoteName(@database) + '.' | |
+ QuoteName(@Schema) + '.' + QuoteName(@table) | |
+ ' FOR JSON PATH)'; | |
PRINT @Expression | |
EXECUTE sp_executesql @expression, N'@TheData nvarchar(max) output', | |
@TheData = @jsonData OUTPUT; | |
END; | |
'@ <# we create a new connection just so we can be certain of being able to use | |
the same connection that has the temporary stored procedure on it. Normally | |
we just share the server's connection but this isn't safe and it is possib;e | |
to lose the connection just by doing an SMO action #> | |
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection | |
$SqlConnection.ConnectionString = $conn.ConnectionString | |
$SqlConnection.Open() | |
$sqlCommand = $sqlConnection.CreateCommand() | |
try | |
{ | |
$sqlCommand.CommandText = $procedure | |
$sqlCommand.ExecuteScalar() | |
} | |
catch [System.Exception] | |
{ | |
$_.Exception.InnerException.Errors | foreach{ write-error "$_.message" } #parameters | |
} | |
} | |
if ($errors.Count -eq 0) | |
#if there were errors, then it gives up at this stage and reports the errors. | |
{ | |
$DataDatabaseObject.Tables | Select Schema, Name | | |
foreach { | |
$filename = "$($_.schema)_$($_.Name)" -replace '[\\\/\:\.]', '-' | |
$Tablename = "[$($_.schema)].[$($_.Name)]" | |
$result = $_.Name | |
#execute this script, using the existing connection | |
$params =@" | |
DECLARE @Json NVARCHAR(MAX) | |
EXECUTE #SaveExtendedJsonDataFromTable @TableSpec='$tablename',@JSONData=@json OUTPUT | |
Select @json | |
"@ | |
$sqlCommand = $sqlConnection.CreateCommand() | |
$sqlCommand.CommandText = $params | |
try | |
{ | |
$sqlCommand.ExecuteScalar() | Out-file -FilePath "$OutputPath\$filename" -Encoding utf8 -Force | |
} | |
catch [System.Exception] | |
{ | |
$_.Exception.InnerException.Errors | foreach{ write-error $_.message } #parameters | |
} | |
} | |
} | |
$SqlConnection.Close() | |
if ($Errors.count -gt 0) #if we couldn't import something | |
{ | |
$Errors | foreach{ | |
write-warning "There was an error '$($_)'" | |
} | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment