Script databases and objects using Powershell

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.

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
    }

}

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

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.
1. Shell
  
  sudo cp Invoke-ScriptSqlDatabase.psm1 /usr/local/share/powershell/Modules/Invoke-ScriptSqlDatabase
  
  1
  
  sudo cp Invoke-ScriptSqlDatabase.psm1 /usr/local/share/powershell/Modules/Invoke-ScriptSqlDatabase
Open a powershell session.
1. Shell
  
  pwsh
  
  1
  
  pwsh
Run the following to ensure the module can be loaded.
1. PowerShell
  
  get-module -listavailable | where Name -eq "Invoke-ScriptSqlDatabase"
  
  1
  
  get-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.
1. PowerShell
  
  Invoke-ScriptSqlDatabase -SqlInstance RHEL1 -Database "Adventureworks2017","AdventureWorksDW2017" -SqlAuthentication $true -Directory "/home/luke/sqlScripts"
  
  1
  
  Invoke-ScriptSqlDatabase -SqlInstance RHEL1 -Database "Adventureworks2017","AdventureWorksDW2017" -SqlAuthentication $true -Directory "/home/luke/sqlScripts"
Once completed, exit powershell and open SQL Operations Studio.
1. Type exit at the PS prompt.
2. 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!

Script databases and objects using Powershell

SQL Server Management Objects

SqlServer Powershell Module

Script

Linux

Like this:

Related

One thought to “Script databases and objects using Powershell”

Leave a Reply Cancel reply

SQL Server Management Objects

SqlServer Powershell Module

Script

Linux

Share this:

Like this:

Related

One thought to “Script databases and objects using Powershell”

Leave a Reply Cancel reply