As a DBA, one of the requests I commonly get is to provide database scripts to developers from environments in which they may not have access to. These scripts are then used to setup dev environments, comparisons, etc. This is an easy enough request using SQL Server Management Studio, or if you’re lucky, using other tools such as Red Gate SQL Compare (http://bit.ly/2M6AGbG).
However, I needed a method for scripting databases quickly and provided the option to script multiple databases if desired. I decided to use SQL Server Management Objects (SMO) and Powershell for this task. If you’re not familiar with SMO check out http://bit.ly/2M4FD4S for more information. I’ll provide a quick description of each class, or property, used within the script.
SQL Server Management Objects
- Microsoft.SqlServer.Management.Smo.Server – This class represents an instance of SQL Server. Used to build the connection to a given SQL Server instance (on Windows or Linux).
- Microsoft.SqlServer.Management.Smo.Server.Databases – Represents a collection of Databases objects.
- Microsoft.SqlServer.Management.Smo.Transfer – The transfer object provides programmatic control over copying of schemas and data. I use it to generate a script to file.
- Microsoft.SqlServer.Management.Smo.ScriptingOptions – Provides options to the options that can be set for scripting operations. For a full list, see http://bit.ly/2M8GfGy.
SqlServer Powershell Module
This script has several dependencies on the SqlServer powershell module. If you don’t have it, grab it from http://bit.ly/2MmOZpk. FYI, if you need this module on a machine not having internet access, you can install on a machine that does and copy the SqlServer folder from C:\program files\WindowsPowershell\Modules to the machine you need to run the script from. While you’re there, create a directory named Invoke-ScriptSqlDatabase. When you’re finished, the directory should resemble the screenshot below:
Script
The script can be saved as Invoke-ScriptSqlDatabase.psm1 inside the folder created above.
|
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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 |
function Invoke-ScriptSqlDatabase { <# .SYNOPSIS Scripts a SQL Server database. .DESCRIPTION This function will connect to a SQL Server instance and script a particular database(s). .EXAMPLE Invoke-ScriptSqlDatabase -SqlInstance SQLTEST -Database Adventureworks2017 -SqlAuthentication $true -Directory "C:\output" .EXAMPLE Invoke-ScriptSqlDatabase -SqlInstance SQLTEST -Database "Adventureworks2017","AdventureworksDW2017" -SqlAuthentication $false -Directory "C:\output" .LINK For a full list of ScriptingOptions used by this script, please see http://bit.ly/2M8GfGy. .NOTES MIT License Copyright (c) 2018 sqlservermigrations.com Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. #> [CmdletBinding()] param( #SqlInstance - Source SQL Server instance. [Parameter(Mandatory = $true)] [string]$SqlInstance, #Database, or array of databases, to script. [Parameter(Mandatory = $true)] [array]$Database, #SqlAuthentication - Boolean. Default is false (use Windows Authentication) [Parameter(Mandatory = $false)] [boolean]$SqlAuthentication, #Directory - This is where the script will reside. [Parameter(Mandatory = $true)] [string]$Directory, #ContinueScriptingOnError - If an error is encountered while generating the script, continue if $true. [Parameter(Mandatory = $false)] [boolean]$ContinueScriptingOnError ) BEGIN { #import the SQLServer module if (-not(get-module -name SqlServer)) { if (Get-Module -ListAvailable | Where-Object { $_.Name -eq "SqlServer" }) { import-module "SqlServer" } else { throw "This function requires the SqlServer module. Get it here http://bit.ly/2MmOZpk" } } #append a timestamp to the generated script files $DateTimestamp = $(get-date -f "yyyyMMdd-HHmmss") #create the output directory if not exists if (-not( Test-Path "$Directory\" -PathType Container)) { New-Item -ItemType Directory -Path $Directory } #setup connection to the Sql instance. $SqlSMO = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SqlInstance #if SqlAuthentication is true, grab user credentials if ($SqlAuthentication) { $cred = Get-Credential $SqlSMO.ConnectionContext.LoginSecure = $false $SqlSMO.ConnectionContext.set_Login($cred.UserName) $SqlSMO.ConnectionContext.set_SecurePassword($cred.Password) } else { $SqlSMO.ConnectionContext.LoginSecure = $true } #try connecting try { $SqlSMO.ConnectionContext.Connect() } catch { throw "Can't connect to $SqlInstance." } } PROCESS { #trap errors Trap { # Handle the error $err = $_.Exception write-warning $err.Message while ( $err.InnerException ) { $err = $err.InnerException write-warning $err.Message }; # Stop the script. break } #loop over the array of databases and generate scripts $Database | ForEach-Object { #Check OS. If Linux or Windows. Update directory. if($env:OS) { $Filename = "$($Directory)\$($_)_$($DateTimestamp).sql" } else { $Filename = "$($Directory)/$($_)_$($DateTimeStamp).sql" } $db = $SqlSMO.Databases[$_] if (-not($db)) { throw "The $_ database was not found on $SqlInstance." } #Create database script options $databaseSCRP = New-Object Microsoft.SqlServer.Management.Smo.Scripter($SqlSmo) $databaseSCRP.Options.ScriptBatchTerminator = $true $databaseSCRP.Options.IncludeHeaders = $true $databaseSCRP.Options.ExtendedProperties = $true $databaseSCRP.Options.ToFileOnly = $true $databaseSCRP.Options.Filename = $Filename $databaseSCRP.Options.Encoding = [System.Text.Encoding]::UTF8 $databaseSCRP.Script($db) #Script database objects $transfer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Transfer -ArgumentList $db $ScriptOptions = New-Object -TypeName Microsoft.SqlServer.Management.Smo.ScriptingOptions #setup options $ScriptOptions.ExtendedProperties = $true $ScriptOptions.DriAll = $true $ScriptOptions.Indexes = $true $ScriptOptions.Triggers = $true $ScriptOptions.ScriptBatchTerminator = $true $ScriptOptions.IncludeHeaders = $true $ScriptOptions.Permissions = $true $ScriptOptions.Statistics = $true $ScriptOptions.ToFileOnly = $true $ScriptOptions.IncludeIfNotExists = $true $ScriptOptions.FileName = $FileName $ScriptOptions.AppendToFile = $true $ScriptOptions.Encoding = [System.Text.Encoding]::UTF8 #Continue scripting if an error is encountered? Errors can be seen if objects have dependencies on other objects that are no longer in place. if ($ContinueScriptingOnError) { $ScriptOptions.ContinueScriptingOnError = $true } $transfer.Options = $ScriptOptions $transfer.ScriptTransfer() } } END { write-host "Script complete. All scripts are located in the $Directory folder." -ForegroundColor Green } } |
Saving this module within the folder specified allows this function to be referenced by default within future powershell sessions. You can verify by running the command below:
|
1 |
get-module -listavailable | Where Name -eq "Invoke-ScriptSqlDatabase" |
Open a new powershell session, confirm the module is available, and execute:
|
1 |
Invoke-ScriptSqlDatabase -SqlInstance InstanceName -Database AdventureWorks2017,AdventureWorksDW2017 -SqlAuthentication $false -Directory "C:\output" |
Once completed, you’ll see the *.sql scripts in the provided directory.
For more examples, just run “get-help Invoke-ScriptSqlDatabase -examples.”
Linux
This module can also be used on a Linux client. The following examples show how it can be used on Red Hat Enterprise Linux 7. I’m using the MobaXterm ssh client.
- Install PowerShell Core. For instructions, see http://bit.ly/2vlVhPU.
- I’ve also installed SQL Operations Studio for viewing the .sql script once generated. For downloading and installation instructions, see http://bit.ly/2M9931N. If you plan to open the GUI from a ssh session, ensure X11 forwarding is enabled (https://red.ht/2M4CW3i).
- Copy the Invoke-ScriptSqlDatabase.psm1 file to your Linux host using scp or your favorite copy program.
- Run “ls” from the prompt to ensure the module exists in your home directory.
- Copy the file from your home directory to /usr/local/share/powershell/Modules.
-
1sudo cp Invoke-ScriptSqlDatabase.psm1 /usr/local/share/powershell/Modules/Invoke-ScriptSqlDatabase
-
- Open a powershell session.
-
1pwsh
-
- Run the following to ensure the module can be loaded.
-
1get-module -listavailable | where Name -eq "Invoke-ScriptSqlDatabase"
-
- You should see an output matching the screenshot below:
- The function is now ready to execute. One caveat, it must use Sql Server Authentication.
-
1Invoke-ScriptSqlDatabase -SqlInstance RHEL1 -Database "Adventureworks2017","AdventureWorksDW2017" -SqlAuthentication $true -Directory "/home/luke/sqlScripts"
-
- Once completed, exit powershell and open SQL Operations Studio.
- Type exit at the PS prompt.
- Type sqlops at the bash prompt.
- Within SQL Operations Studio, click file and then Open File. Navigate to the directory specified above and open the script.
I’d be interested in any suggestions on making this script better. Thanks for stopping by!
Great Article Luke.