SQL Server Database Scripting with PowerShell

Edition: Saturday, March 2nd, 2024

Welcome to March!  

Today, I want to talk about one common challenge a DBA may encounter.  Scripting a database(s).  There are a few common scenarios that may require this.  Here are a few:

• Needing to create an empty shell of an existing database for testing purposes.
• Adding the schema into source control.
• Creating a copy of the database without data.

There are several options for accomplishing this task.  Those include but are not limited to:

• SQL Server Management Studio
• Azure Data Studio
• SQL Server Management Objects 
• DBCC CLONEDATABASE (if you just need a read-only copy of the schema on the same instance) - DBCC CLONEDATABASE (Transact-SQL) - SQL Server | Microsoft Learn
• 3rd Party Tools - Red Gate Schema Compare for example.

I've found using SQL Server Management Objects (SMO) works well.  Especially if you need to automate this process or script multiple database schemas at once.  In 2018, I built a PowerShell function to help with this and I'd like to share that with you today. Here's how I use it to accomplish the following tasks in my test environment:

1. Create a SQL Server database.
2. Deploy multiple objects that I may use to help manage each instance.  
3. Script all objects created in the utility database(s).  
4. Add the generated t-sql script to an Ansible role in source control.  This way, whenever I need to build a new instance, using Ansible, I can be sure it also includes this database and the latest script.  

Here's what I'll cover:

• Getting Started
• Scripting a database
• Conclusion

Getting Started:

Features of the current version:

• Comprehensive Scripting: The Invoke-ScriptSqlDatabase function scripts out not just the database schema and objects but also includes extended properties, making it a beneficial tool for database documentation.
• Flexible Authentication: Supports both Windows and SQL Server authentication, providing flexibility in connecting to SQL Server instances.
• Customizable Output: Users can specify the output directory for the script files, allowing for organized storage and easy access to the scripted databases.
• Enhanced Scripting Options: Utilizes a range of `ScriptingOptions` from the SQL Server Management Objects (SMO) library, offering granular control over the scripting process.  Options such as `ScriptingBatchTerminator`, `IncludeHeaders`, and `ExtendedProperties` ensure that the scripts are comprehensive and ready for use in a variety of scenarios.

Invoke-ScriptSqlDatabase.ps1 has a dependency on the SMO library.  You can install the library via several different methods.  The easiest is to do the following:

1. Open a PowerShell window and run the following to check for the existence of the SqlServer module.
   1. Get-Module -ListAvailable | Where-Object { $_.Name -eq "SqlServer" }) 
2. If you see the output below, then you already have the module installed.
   1. 
3. If it's not found, install it using this command - "install-module SQLServer"

Next, grab the script below and save it.  

Script SQL Server database (github.com)

Scripting a database:

To use it, after you review it, of course, save it and open a PowerShell window. 

1. Navigate to the directory where the script is located.
2. Type ".\Invoke-ScriptSqlDatabase.ps1" and press enter.  This will dot source the script and make the function within it accessible in your current session.  You could rename the file extension to .psm1 and drop it in your C:\users\userName\Documents\WindowsPowershell folder as well.  It would be loaded each time you open PowerShell afterward.  I wouldn't do that with a script you just downloaded from the internet until fully reviewed and tested though.
3.  Next, run "get-help Invoke-ScriptSqlDatabase -examples."  You'll see the output below.
   1. 
4. The function expects the following parameters:
   1. SqlInstance - Instance hosting the database you wish to script.
   2. Database - Name of the database to script.  This can be multiple databases.
   3. SqlAuthentication - If true, you'll see a prompt to enter credentials.  Set this to $false, or just exclude the option if using Windows Authentication.
   4. Directory - This is the directory where your script will be located.  It'll be created if it doesn't already exist.

This function leverages the following `ScriptingOptions` (adjust these as necessary):

• ScriptBatchTerminator: Adds a GO statement after each batch, separating SQL statements clearly.
• IncludeHeaders: Includes descriptive headers in the scripted SQL files, providing context and making the scripts more readable.
• ExtendedProperties: Scripts out the extended properties of database objects, ensuring that all descriptive metadata is preserved.
• ToFileOnly: Directs the script output to files, making it easy to save and manage the scripts.
• FileName & Encoding: Specifies the output file path and encoding, ensuring scripts are saved correctly and are readable.
• AppendToFile: Allows for appending to the script files, enabling the consolidation of multiple scripts into a single file.
• IncludeIfNotExists: Scripts the IF NOT EXISTS check for objects, making the scripts idempotent and safe for re-execution without causing errors.  This is set to false due to an issue encountered when scripting natively compiled procedures.  If your database doesn't contain these types of objects, you can set this to true.

 Here's an example of generating a script using the WideWorldImporters database.  The generated .SQL file will include a date and time stamp. 

Conclusion:

If you're a DBA who's not too familiar with PowerShell I'd recommend diving into it.  It'll make your life a lot easier and help with automation.  Of course, other scripting languages can be used, but I've found PowerShell to be one of the easiest to learn.  Combine it with SQL Server Management Objects and you can do some cool stuff in a fraction of the time when compared to manual or GUI-based operations.

If you haven't done so, I invite you to join the weekly DBA Challenges newsletter -  DBA Challenges - Newsletter (automatesql.com)

That's it for this week.  Have a great week!