Today, I am going to show a simple script that uses dbatools to script out SQL Server Database level objects like Tables, Views, Stored Procedures, SQL Agent Jobs, Triggers, Database Users, etc.,
With dbatools, it is simple enough to also script out Instance level objects like logins, database mail profiles/accounts, credentials, SQL Agent objects, linked servers, Central Management Server objects, server configuration settings (sp_configure), user objects in systems databases, system triggers and backup devices
I am not going to elaborate any more as dbatools has excellent documentation.
Scripting (or Not) Specific Nuances! Please read 🙂
Something you may want to change however is what gets scripted like the “USE [dbname]” and “GO” separators etc., that you may want to configure. Just play with the options till you are happy with the results
$options = New-DbaScriptingOption
$options.ScriptSchema = $true
$options.ScriptData = $false #This is key as it scripts the data too!
$options.IncludeDatabaseContext = $true #Set to $false if you dont want the "USE [DbName]"
$options.IncludeHeaders = $false
$Options.NoCommandTerminator = $false
$Options.ScriptBatchTerminator = $true
#Specifies the Batch Separator to use. Uses the value from configuration Formatting.BatchSeparator by default. This is normally "GO" but below we set it to ";" instead of GO
#(Get-DbatoolsConfigValue -FullName 'Formatting.BatchSeparator')
Set-DbatoolsConfig -FullName 'Formatting.BatchSeparator' -Value ';'
Data Too?
It is not a good idea to script the data as scripts. You can script all the data in all the tables using the following option if your database is very small
$options.ScriptData = $true
If your database is large and if you want to script the data of only certain configuration tables, you can do so easily using Export-DbaDbTableData as shown in the example from the dbatools documentation
Get-DbaDbTable -SqlInstance sql2017 -Database AdventureWorks2014 -Table EmployeePayHistory |
Export-DbaDbTableData -Path C:\temp\export.sql -Append -BatchSeparator ';' -Encoding ASCII
How Output Looks
Given a Server name, a folder path and other details, generates scripts to the folder that look like this:
Scripting Other Object Types
If you wanted more object types to be scripted out, find out all the available objects that you can “Get” using the Get-Help
> Get-Help Get-Dba*
Name Category Module Synopsis
---- -------- ------ --------
Get-DbaBuildReference Alias Get-DbaBuild
Get-DbaDbModule Alias Get-DbaModule
Get-DbaRegisteredServer Alias Get-DbaRegServer
Get-DbaAgBackupHistory Function dbatools Returns backup history details for databases on a SQL Server Availability Group.
Get-DbaAgDatabase Function dbatools Gets availability group databases from one or more SQL Server instances.
Get-DbaAgentAlert Function dbatools Returns SQL Agent alerts on a SQL Server Agent.
....
Starter Script:
Once you know what you want, you can add more types to the script below following the pattern of what is already there. Make sure you replace the values at the top as needed
<#
#######################
.SYNOPSIS
Generates scripts of tables, views, stored procedures etc., to the specified folder
.DESCRIPTION
Generates scripts of database objects using dbatools module
Version History
v1.0 - Jana Sattainathan - May.10.2024
.LINK
https://sqljana.wordpress.com
#>
Import-Module dbatools
$server = 'myservername'
$dbs = @('MyDatabaseName1', 'MyDatabaseName2')
$dateStr = (Get-Date).ToString("yyyyMMddhh24mmss")
$folder = 'c:\MyExportFolder\DBScripts\'
$filePrefix = "$server-$($db.name)-$dateStr"
$options = New-DbaScriptingOption
$options.ScriptSchema = $true
$options.IncludeDatabaseContext = $true
$options.IncludeHeaders = $false
$Options.NoCommandTerminator = $false
$Options.ScriptBatchTerminator = $true
$Options.AnsiFile = $true
$Options.AppendToFile = $true
$options.DriPrimaryKey = $true
$options.DriForeignKeys = $true
$options.DriNonClustered = $true
$options.NonClusteredIndexes = $true
$options.DriChecks = $true
$options.DriDefaults = $true
$options.Indexes = $true
$options.IncludeIfNotExists = $true
$options.IncludeDatabaseRoleMemberships = $true
$options.Permissions = $true
#Instance level objects
#----------
#Exports SQL Server ALL database restore scripts, logins, database mail profiles/accounts, credentials, SQL Agent objects, linked servers, Central Management Server objects, server configuration settings (sp_configure), user objects in systems databases, system triggers and backup devices
# (if needed from one SQL Server to another)
Export-DbaInstance -SqlInstance $server -Path $folder -Exclude @('CentralManagementServer', 'Credentials')
#Logins
$file = Join-Path $folder -ChildPath "$filePrefix.logins.sql"
Get-DbaLogin -SqlInstance $server -EnableException |
Export-DbaScript -Encoding UTF8 -ScriptingOptionsObject $options -NoPrefix -Passthru |
Out-File -FilePath $file
#Loop through databases and script out
#----------------------------------------
$Databases = Get-DbaDatabase -SqlInstance $server -Database $dbs
foreach ($db in $Databases) {
#Database
#----------
$file = Join-Path $folder -ChildPath "$filePrefix.db.sql"
Export-DbaScript -InputObject $db -FilePath $file -Append -Encoding UTF8 -ScriptingOptionsObject $options -NoPrefix -EnableException
#Tables
#----------
#Passthru parameter is key below to get all the tables into a single script instead of individual script per table
$file = Join-Path $folder -ChildPath "$filePrefix.tables.sql"
Get-DbaDbTable -SqlInstance $server -Database $db.Name -EnableException |
Export-DbaScript -Encoding UTF8 -ScriptingOptionsObject $options -NoPrefix -Passthru |
Out-File -FilePath $file
#Triggers
#----------
#Passthru parameter is key below to get all the tables into a single script instead of individual script per table
$file = Join-Path $folder -ChildPath "$filePrefix.triggers.sql"
Get-DbaDbTrigger -SqlInstance $server -Database $db.Name -EnableException |
Export-DbaScript -Encoding UTF8 -ScriptingOptionsObject $options -NoPrefix -Passthru |
Out-File -FilePath $file
#Views
#----------
#Passthru parameter is key below to get all the tables into a single script instead of individual script per table
$file = Join-Path $folder -ChildPath "$filePrefix.views.sql"
Get-DbaDbView -SqlInstance $server -Database $db.Name -ExcludeSystemView -EnableException |
Export-DbaScript -Encoding UTF8 -ScriptingOptionsObject $options -NoPrefix -Passthru |
Out-File -FilePath $file
#Stored Procedures
#----------
$file = Join-Path $folder -ChildPath "$filePrefix.storedprocs.sql"
Get-DbaDbStoredProcedure -SqlInstance $server -Database $db.Name -ExcludeSystemSp -EnableException |
Export-DbaScript -Encoding UTF8 -ScriptingOptionsObject $options -NoPrefix -Passthru |
Out-File -FilePath $file
#DB Users
#----------
$file = Join-Path $folder -ChildPath "$filePrefix.dbusers.sql"
Get-DbaDbUser -SqlInstance $server -Database $db.Name -EnableException |
Export-DbaScript -Encoding UTF8 -ScriptingOptionsObject $options -NoPrefix -Passthru |
Out-File -FilePath $file
#DB Roles
#----------
#https://blog.robsewell.com/blog/export-sql-user-permissions-to-t-sql-script-using-powershell-and-dbatools/
$file = Join-Path $folder -ChildPath "$filePrefix.dbroles.sql"
Export-DbaDbRole -SqlInstance $server -Database $db.Name -EnableException -FilePath $file -Append
#Agent jobs
#----------
$file = Join-Path $folder -ChildPath "$filePrefix.jobs.sql"
Get-DbaAgentJob -SqlInstance $server -EnableException |
Export-DbaScript -Encoding UTF8 -ScriptingOptionsObject $options -NoPrefix -Passthru |
Out-File -FilePath $file
}
Final Thoughts
Thanks to the dbatools Team for their incredible work which makes coming up with something like the above a matter of minutes instead of days or even weeks and months!
SQL Jana 
One thought on “PowerShell: Script Out SQL Server Objects DDL To Folder Using dbatools”