solving sql server problems for millions of dbas and developers since 2006



SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Twitter Page MSSQLTips Google+ Page

Top SQL Server Tools








Generating SQL Scripts using Windows PowerShell

By: | Read Comments (18) | Print

Edwin works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

Related Tips: More

Problem
In a previous tip on SQL Script Generation Programmatically with SMO, you've seen how you can use SMO to generate SQL scripts programmatically. In this tip I will cover how to generate scripts using Windows PowerShell.

Solution
SQL scripting in SMO is controlled either by the Scripter object and its child objects, or the Script method on individual objects as highlighted in the previous tip. We will be using the same approaches to generate SQL scripts using Windows PowerShell.

Using the Script() method

Similar to what we have done in previous Windows PowerShell tips with SQL Server, we will create an instance of the Server object and connect to it. And as I've always mentioned in all the previous Windows PowerShell tips I've written, the only aspect of the code that we will change from the scripts in the previous tips is the last line, i.e. adding new properties or methods for the new objects we will be working with, highlighting the power and simplicity of Windows PowerShell. In this particular example, we just introduced the Script() method from the database object collection, selecting the Northwind as the target database.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2005_1" 

$dbs=$s.Databases

$dbs["Northwind"].Script() 

That's about it. The Script() method will generate the SQL script for creating the Northwind database - all in a single line of code. Of course, this would not mean anything at all to us until we send the output to a script file. We can then pipe the results of calling the Script() method to a file using the Out-File cmdlet as we did in another previous tip.

$dbs["Northwind"].Script() | Out-File D:\PSScripts\Northwind.sql

The results of the generated SQL script is shown below

CREATE DATABASE [Northwind] ON  PRIMARY 
( NAME = N'Northwind', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Northwind.mdf' , 
SIZE = 3456KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'Northwind_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Northwind_1.ldf' , 
SIZE = 4224KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
EXEC dbo.sp_dbcmptlevel @dbname=N'Northwind', @new_cmptlevel=90
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [Northwind].[dbo].[sp_fulltext_database] @action = 'disable'
END
ALTER DATABASE [Northwind] SET ANSI_NULL_DEFAULT OFF 
ALTER DATABASE [Northwind] SET ANSI_NULLS OFF 
ALTER DATABASE [Northwind] SET ANSI_PADDING OFF 
ALTER DATABASE [Northwind] SET ANSI_WARNINGS OFF 
ALTER DATABASE [Northwind] SET ARITHABORT OFF 
ALTER DATABASE [Northwind] SET AUTO_CLOSE OFF 
ALTER DATABASE [Northwind] SET AUTO_CREATE_STATISTICS ON 
ALTER DATABASE [Northwind] SET AUTO_SHRINK OFF 
ALTER DATABASE [Northwind] SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE [Northwind] SET CURSOR_CLOSE_ON_COMMIT OFF 
ALTER DATABASE [Northwind] SET CURSOR_DEFAULT  GLOBAL 
ALTER DATABASE [Northwind] SET CONCAT_NULL_YIELDS_NULL OFF 
ALTER DATABASE [Northwind] SET NUMERIC_ROUNDABORT OFF 
ALTER DATABASE [Northwind] SET QUOTED_IDENTIFIER OFF 
ALTER DATABASE [Northwind] SET RECURSIVE_TRIGGERS OFF 
ALTER DATABASE [Northwind] SET  DISABLE_BROKER 
ALTER DATABASE [Northwind] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
ALTER DATABASE [Northwind] SET DATE_CORRELATION_OPTIMIZATION OFF 
ALTER DATABASE [Northwind] SET TRUSTWORTHY OFF 
ALTER DATABASE [Northwind] SET ALLOW_SNAPSHOT_ISOLATION OFF 
ALTER DATABASE [Northwind] SET PARAMETERIZATION SIMPLE 
ALTER DATABASE [Northwind] SET READ_COMMITTED_SNAPSHOT OFF 
ALTER DATABASE [Northwind] SET  READ_WRITE 
ALTER DATABASE [Northwind] SET RECOVERY FULL 
ALTER DATABASE [Northwind] SET  MULTI_USER 
ALTER DATABASE [Northwind] SET PAGE_VERIFY TORN_PAGE_DETECTION  
ALTER DATABASE [Northwind] SET DB_CHAINING OFF 

Scripting tables

We can then iterate thru the Tables collection in the Northwind database and call the Script() method once more

#Generate script for all tables
foreach ($tables in $dbs["Northwind"].Tables)
{
    $tables.Script() + "`r GO `r " | out-File D:\PSScripts\Scripts.sql -Append
}

Notice that I have done three things here. First, I have included the carriage return symbol - `r - just so I can display the results properly. The Script() method returns a StringCollection where each string in the collection contains one of the statements generated by the script. Second, I have appended the GO statement to treat each CREATE TABLE statement as a batch. Since the Script() method will generate CREATE TABLE statements, it wouldn't be much of an issue even without the GO statement. But if it will generate scripts for other database objects, like views and stored procedures, you definitely need to include a GO statement to treat the individual CREATE statements as a batch. And third, I used the -Append parameter of the Out-File cmdlet to append the output of the Script() method to the existing file.

Scripting indexes

Since we're already generating scripts for table creation, we might as well generate the scripts for the corresponding indexes within tables. We just need to iterate thru the Indexes collection, again calling the Script() method and appending the results in the output file

#Generate script for all tables
foreach ($tables in $dbs["Northwind"].Tables)
{
    $tables.Script() + "`r GO `r " | out-File D:\PSScripts\Scripts.sql -Append

    #Generate script for all indexes in the specified table
    foreach($index in $tables.Indexes)
    {
        $index.Script() + "`r GO `r" | out-File D:\PSScripts\Scripts.sql -Append
    }
}

A section of the resulting SQL script is shown below. Notice how you can easily generate SQL scripts with just a few lines of code in Windows PowerShell with SMO.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Categories](
   [CategoryID] [int] IDENTITY(1,1) NOT NULL,
   [CategoryName] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO 
CREATE NONCLUSTERED INDEX [CategoryName] ON [dbo].[Categories] 
(
   [CategoryName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 GO 

Using the Scripter object

Another option to generate SQL scripts is by using the Scripter object. The Scripter object is the overall, top-level object for managing scripting operations in SQL Server. As it is a full blown object, it has a lot more capabilities than the Script() method. For example, it has the ability to discover relationships between objects and provide you with more scripting options similar to using SQL Server Management Studio.

In our Windows PowerShell script, we will be introducing this new object using the Microsoft.SqlServer.Management.Smo.Scripter namespace.

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

Where $s is an instance of the Server object we have previously defined. Once we have defined Scripter object, we can then specify the different Options property. These properties define how the Scripter object will generate the SQL script. Below are some of the options I took from SQL Server Management Studio as an example.

$scrp.Options.AppendToFile = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $True 
$scrp.Options.IncludeHeaders = $True
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True

$scrp.Options.FileName = "D:\PSScripts\NWind.SQL" 

Here's a brief description of the properties mentioned in the script

  • AppendToFile - specifies whether to append the script to the output file or overwrite it. It's the equivalent of what we did with the Out-File cmdlet using the -append parameter
  • ClusteredIndexes - specifies whether to include the clustered index definitions
  • DriAll - specifies whether to include the DRI - declarative referential integrity - objects
  • ScriptDrops - specifies whether to include the IF EXISTS..DROP statements
  • IncludeHeaders - specifies whether to include header information llike the date and time the script was generated
  • ToFileOnly - specifies whether to simply generate the script to a file or store it in a string as an output as well
  • Indexes - specifies whether to include indexes
  • WithDependencies - specifies whether to include all other dependent objects in the script

The key property here is the WithDependencies property as you can then generate scripts for all other objects, such as views and stored procedures, that are dependent on the object that you want to script out. Unlike when simply using the Script() method where we need to explicitly call all the other object collections to generate the corresponding scripts, this property tells the Scripter object to simply look at the relationships and object dependencies as the basis for generating the script.

Finally, we need to call the Script() method of the Scripter object, passing the database object collection that we want to generate scripts for

$scrp.Script($dbs["Northwind"].Tables)

The complete code listing for using the Scripter object is shown below.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null

# Create an SMO connection to the instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2005_1"

$dbs = $s.Databases
$dbs["Northwind"].Script() | Out-File D:\PSScripts\NWind.SQL

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scrp.Options.AppendToFile = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $True
$scrp.Options.IncludeIfNotExists = $True
$scrp.Options.IncludeHeaders = $True
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True

$scrp.Options.FileName = "D:\PSScripts\NWind.SQL"
$scrp.Script($dbs["Northwind"].Tables)



Related Tips: More | Become a paid author


Last Update: 9/22/2009

Share: Share 



Free SQL Server Learning

Free webcast - Introduction to SQL Azure


Demystify TempDB Performance and Manageability



Comments and Feedback:

Tuesday, September 22, 2009 - 7:21:34 AM - lkoravi Read The Tip
I tried the script at the end and got this error: Multiple ambiguous overloads found for "Script" and the argument count: "1". At C:\Documents and Settings\riyer\My Documents\scripts\scripter.ps1:22 char:13 + $scrp.Script <<<

Tuesday, September 22, 2009 - 8:40:08 AM - bass_player Read The Tip

 I copied the exact same script and it worked perfectly fine. Check that you have the Northwind database created in your SQL Server instance. You also need to check your instance name in Line 4 and make sure that you have the correct folders and path in lines 7 and 21


Friday, October 02, 2009 - 12:03:12 PM - Repriser Read The Tip

Not practical. Spending the time to learn Power Script, you could have had it done in T-SQL. This script has no particular rules. It is not practical to script for a new task. You will be spending weeks to find out how to write something and without a debugger to help.

Saturday, October 17, 2009 - 4:02:56 PM - bass_player Read The Tip

I totally agree with you. Learning Windows PowerShell is not as easy as it seems as you have to deal with a ton of .NET-related objects. Which is why MSSQLTips is here to make the learning process a bit easier than doing it on your own. But it's like learning how to walk - it's hard to start but you have to. Microsoft is defining the next wave of server products to include manageability using PowerShell (they've already started out in Exchange 2007) so learning PowerShell will enable any administrator to write scripts that can manage just about any Microsoft server product, not just SQL Server. And since SQL Server 2005 and higher has been built on top of the .NET Framework with SMO, you can take advantage of the exposed APIs to administer SQL Server by using PowerShell

BTW, if you are looking for a nice IDE for writing PowerShell scripts, check out PowerGUI from Quest Software. It's a free IDE and script editor for Windows PowerShell that enables you to do stuff such as debug your scripts. 

http://www.powergui.org/index.jspa


Wednesday, January 27, 2010 - 1:14:43 AM - samshah101 Read The Tip

hi

what did you do to get over this.  i am getting the same error and cant find a solution.  any help would be very handy.

Multiple ambiguous overloads found for "Script" and the argument count: "1".

Wednesday, January 27, 2010 - 2:39:36 PM - bass_player Read The Tip

Can you post your entire script?

Friday, February 26, 2010 - 4:30:04 PM - hallozen Read The Tip

i am also receiving the ambiguous error. can someone take a look?

 windows server 2008 R2
SQL Server 2008 Sp1
Powershell 2.0

it's the last line that gets the error:


PS C:\> [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
PS C:\> $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"
PS C:\> $dbs=$s.Databases
PS C:\> $dbs["AdventureWorks"].Script() | Out-File C:\temp\myscript.SQL
PS C:\> $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
PS C:\> $scrp.Options.AppendToFile = $True
PS C:\> $scrp.Options.ClusteredIndexes = $True
PS C:\> $scrp.Options.DriAll = $True
PS C:\> $scrp.Options.ScriptDrops = $True
PS C:\> $scrp.Options.IncludeHeaders = $True
PS C:\> $scrp.Options.ToFileOnly = $True
PS C:\> $scrp.Options.Indexes = $True
PS C:\> $scrp.Options.WithDependencies = $True
PS C:\> $scrp.Options.FileName = "C:\temp\myscript.SQL"
PS C:\> $scrp.Script($dbs["AdventureWorks"].Tables)
Multiple ambiguous overloads found for "Script" and the argument count: "1".
At line:1 char:13
+ $scrp.Script <<<< ($dbs["AdventureWorks"].Tables)
    + CategoryInfo          : NotSpecified: (:) [, MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

 

 

thanks in advance

Wednesday, March 03, 2010 - 3:08:52 PM - samshah101 Read The Tip

has anyone found a cure to this.

 

Thursday, March 11, 2010 - 12:25:48 PM - debettap Read The Tip

Cast it to a collection of SqlSmoObject and it works. :-) 

$scrp.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]$dbs["AdventureWorks"].Tables)

Thursday, March 11, 2010 - 3:02:10 PM - stittdba Read The Tip

[quote user="debettap"]

Cast it to a collection of SqlSmoObject and it works. :-) 

$scrp.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[$dbs["AdventureWorks"].Tables)

[/quote]

You need one more closed bracket

$scrp.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$dbs["AdventureWorks"].Tables)

Tuesday, March 16, 2010 - 4:04:02 PM - hallozen Read The Tip

@debettap  & stittdba

 

Thanks that worked!

Thursday, March 18, 2010 - 1:09:33 PM - debettap Read The Tip

@stittdba - Nice catch. Thanks for that.

Thursday, March 18, 2010 - 1:40:51 PM - debettap Read The Tip

When the ScriptDrops option is true, only drop scripts are generated. If you want create scripts, this option must be false:

$scrp.Options.ScriptDrops = $False

If you want both drops and creates, you need to Script with this option set to false then Script again with this option set to true.

 

Thursday, March 18, 2010 - 2:04:13 PM - stittdba Read The Tip

[quote user="debettap"]

When the ScriptDrops option is true, only drop scripts are generated. If you want create scripts, this option must be false:

$scrp.Options.ScriptDrops = $False

If you want both drops and creates, you need to Script with this option set to false then Script again with this option set to true.

 

[/quote]

Some things are so obvious, but yet they escape you. Thanks.

Friday, March 19, 2010 - 3:47:41 PM - stittdba Read The Tip

[quote user="debettap"]

When the ScriptDrops option is true, only drop scripts are generated. If you want create scripts, this option must be false:

$scrp.Options.ScriptDrops = $False

If you want both drops and creates, you need to Script with this option set to false then Script again with this option set to true.

 

[/quote]

When I set the ScriptDrops to False, the create statements for the views and procedures are not included. However, when ScriptDrops is set to True, the views and procedures are part of the drop statements. How can I get the views and procedures to be part of the create statements. I had hoped the WithDependencies option would catch them, but SSMS doesn't show that the views and procedures have a dependency on their corresponding tables. Any ideas?

Friday, February 25, 2011 - 3:06:12 PM - Tom Powell Read The Tip

Edwin,

I want to thank you for taking the effort to write this article.  I had a litle trouble with one of the scripts too but it helped me dig into the code and figure out what was going on.  I added a few bells and whistles and had a great time doing it.  I'm going to blog it but I'd like to credit your post too.  Once I get it ready you can, if you wish, view the reference here:  Philergia.WordPress.com.

Thanks for the help! 

Wednesday, March 07, 2012 - 9:54:16 PM - Graham Read The Tip

Thanks Edwin

Nice article. Powershell comes into its own area when applying this sort of thing to a few hundred databases and instances.

Regards

Graham

Saturday, May 05, 2012 - 1:57:57 AM - gingendes Read The Tip

When i tried scripting data with this, it says "This Method doesnot support Scripting Data", any cure?

 


Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
Comments
*Enter Code refresh code


 
Sponsor Information
Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis!

Time-strapped database professionals use SQL Monitor to look after their servers. Try it online.

Need SQL Server help and not sure where to turn? Reach out to the Edgewood experts for a Health Check starting at $995.

Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy.

Join the over million SQL Server Professionals who get their issues resolved daily.

Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....

Copyright (c) 2006-2012 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups
Some names and products listed are the registered trademarks of their respective owners.

Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com