Script SQL Server Objects with PowerShell

Standard

export_boxScripting SQL Server objects manually through the SQL Server Management Studio can be a tedious task. Last week I published a script to export database objects with PowerShell. I wanted to take this a little further and create a solution to export SQL Server objects as well.

Because this would be a nice addition to the PSSQLLib module, this function is also included in the library from today.

How it works

The function works by selecting all the different server objects needed and loops through the list to export to a specific directory of the type of object.

The script will export the following types of objects:

  • Jobs
  • Linked Servers
  • Logins
  • Server Roles
  • Triggers
  • Mail

You can exclude objects by specifying a include parameters. For instance, if you want to exclude jobs:

The same method works for the rest of the objects by using the other parameters.

The code

The finished code look like this:

The Result

The result is a script that will export all your objects to “.sql” files. The result of the execution of the script can be seen below:

Export-SQLServerObject_working

I hope this helps you out. Any comment or feedback is appreciated.

Leave a Reply