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.

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:

Open a new powershell session, confirm the module is available, and execute:

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.

  1. Install PowerShell Core.  For instructions, see http://bit.ly/2vlVhPU.
  2. 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).
  3. Copy the Invoke-ScriptSqlDatabase.psm1 file to your Linux host using scp or  your favorite copy program.
  4. Run “ls” from the prompt to ensure the module exists in your home directory.
  5. Copy the file from your home directory to /usr/local/share/powershell/Modules.
  6. Open a powershell session.
  7.  Run the following to ensure the module can be loaded.
  8. You should see an output matching the screenshot below:
  9.   The function is now ready to execute.  One caveat, it must use Sql Server Authentication.
  10.  Once completed, exit powershell and open SQL Operations Studio.
    1. Type exit at the PS prompt.
    2. Type sqlops at the bash prompt.
  11.  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!

One thought to “Script databases and objects using Powershell”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.