Stairway to Server Management Objects (SMO) Level 4: Scripting and Copying Small Databases

,

In the third level of this series I outlined the goodies of the Database Classes, some cool operations  to do with them, and automating simple tasks Now let´s take a look into some usual tasks from DBAs that sometimes can be a pain if not scripted

Scripting a Database

I bet you already have to script a database. Every DBA needs to do it at some time and built-in SQL Server we only have the SSMS for that. This process is easy using the GUI if you need to deal with a few databases, but even for them if you need to do it constantly or for a specific process to automate – like a step in a disaster recovery plan, you will feel how hard this can be. The pain is much worse for several databases.

With SMO and its scripting and transfer classes, this job is a piece of cake (chocolate one)

Using the Scripter Class

This class is more flexible since you can choose what objects you want to script.

Scripting only the database script:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DeathStar'
try {
    $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
    $server.ConnectionContext.connect() #connecting
    $DatabaseToScript = $Server.Databases['AdventureWorks2016']
        
    $Script = new-object 'Microsoft.SqlServer.Management.Smo.Scripter' $server
    
    $script.Options.ToFileOnly = $true #using the inherit of ScriptingOptions Class to set the options to my script
    
    $DatabaseNameToScript = $DatabaseToScript -replace '\[|\]' #removing the [] from the database name
    
    $script.Options.FileName = "C:\temp\$($DatabaseNameToScript).sql" #setting the file name
    $Script.script($DatabaseToScript) #scripting the database  only
    
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

As you can see we are using some options to script the object ($script.Options.ToFileOnly) This options are inheriting of the ScriptingOptions class . You will see this a lot as we use these options, since practically in all scripting and transfer class their members  are inherited . To get a full list of the options click in the link above or use

$script.Options | Get-Member

There are a lot of options to play with, as you can see below:

Choosing the Objects to Script

In this example, we will look at only the Database, stored procedures and user defined functions, including “if not exists” clause and create a .sql file for each one of these groups of objects:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DeathStar'
try {
    $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
    $server.ConnectionContext.connect() #connecting
    $DatabaseToScript = $server.databases['AdventureWorks2014']
    $ObjectsToScript = $DatabaseToScript, $DatabaseToScript.Tables,$DatabaseToScript.StoredProcedures, $DatabaseToScript.UserDefinedFunctions #selecting the objects from adventureworks2014
        
    $Script = new-object 'Microsoft.SqlServer.Management.Smo.Scripter' $server
    
    $script.Options.ScriptBatchTerminator = $true #adds the Go batch terminator. only to file.  
    
    $script.Options.ToFileOnly = $true #using the inherit of ScriptingOptions Class to set the options to my script
    $script.Options.AppendToFile = $true #appending to the file. When the same name it appends
    $script.Options.IncludeIfNotExists = $true
    
    $DatabaseNameToScript = $DatabaseToScript -replace '\[|\]' #removing the [] from the database name
    
    $ObjectsToScript | #foreach to the each SMO Collection created
    foreach-object {
      $ObjectType = (((($_ | Get-Member).TypeName)) | Select-Object -First 1).split('.')[-1] #getting the Type of the object (table,storedprocedure..etc) to build the .sql file
      $script.Options.FileName = "C:\temp\$($DatabaseNameToScript)_Objects_$($ObjectType).sql" #setting the file name  
      $NotSystemObjects = ($_).Where{(!($_.IsSystemObject))}  #excluding systemobjects
      $script.Script([ Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$NotSystemObjects) #need to explict the type to avoid overloads 
   
    }
    
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

This produces only a few scripts, as you can see below:

Dependent Objects

There is a very cool option in the ScriptingOption class called WithDependencies. When its set to $true, the class make sure to include all other database objects that are dependent of the one is actually scripting. Let’s create a table and a view that refers to this table:

USE [dagobah]
GO
/****** Object:  Table [dbo].[Table1]    Script Date: 12-Jun-16 12:34:16 PM ******/DROP TABLE [dbo].[Table1]
GO
/****** Object:  Table [dbo].[Table1]    Script Date: 12-Jun-16 12:34:16 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[codigo] [int] NULL
) ON [PRIMARY]
GO
USE [dagobah]
GO
/****** Object:  View [dbo].[vw_dependstable1]    Script Date: 12-Jun-16 12:34:33 PM ******/DROP VIEW [dbo].[vw_dependstable1]
GO
/****** Object:  View [dbo].[vw_dependstable1]    Script Date: 12-Jun-16 12:34:33 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create view [dbo].[vw_dependstable1]
as
select * from table1
GO

And then let’s just select the views to be scripting out , but with the WithDependency set to $true:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DESKTOP-SQEVVO1\SQL2017'
try {
    $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
    $server.ConnectionContext.connect() #connecting
    $DatabaseToScript = $server.databases['simpletalk']
    $ObjectsToScript = $DatabaseToScript.Views #selecting the objects from adventureworks2014 only views
        
    $Script = new-object 'Microsoft.SqlServer.Management.Smo.Scripter' $server
    $script.Options.ScriptBatchTerminator = $true #adds the Go batch terminator. only to file.  
    
    $script.Options.ToFileOnly = $true #using the inherit of ScriptingOptions Class to set the options to my script
    $script.Options.AppendToFile = $true #appending to the file. When the same name it appends
    $script.Options.IncludeIfNotExists = $true #inclduing the ifnotexists
    $script.Options.WithDependencies = $true #including dependent objects
    
    $DatabaseNameToScript = $DatabaseToScript -replace '\[|\]' #removing the [] from the database name
    
    $script.Options.FileName = "c:\temp\level4\$($DatabaseNameToScript)_ViewsWithDependency.sql"
    $ObjectsToScript |
    ForEach-Object {
        $NotSystemObjects = ($_).Where{(!($_.IsSystemObject))}  #excluding systemobjects
        $script.Script([ Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$NotSystemObjects) #need to explict the type to avoid overloads issues
    }
    
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

The results show this:

Another option is using DependencyWalker Class, which the Scripter Class inherits from. This class is very useful since it will discover dependent relationships.

Warning: this script may take a while to run.

 

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DeathStar'
try {
    $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
    $server.ConnectionContext.connect() #connecting
    $DatabaseToScript = $server.databases['AdventureWorks2016']
    $ObjectsToScript = $DatabaseToScript.Tables,$DatabaseToScript.StoredProcedures, $DatabaseToScript.UserDefinedFunctions #selecting the objects from adventureworks2014
        
    $Script = new-object 'Microsoft.SqlServer.Management.Smo.Scripter' $server
    $script.Options.ScriptBatchTerminator = $true #adds the Go batch terminator. only to file.  
    
    $script.Options.ToFileOnly = $true #using the inherit of ScriptingOptions Class to set the options to my script
    $script.Options.AppendToFile = $true #appending to the file. When the same name it appends
    $script.Options.IncludeIfNotExists = $true
    
    $DatabaseNameToScript = $DatabaseToScript -replace '\[|\]' #removing the [] from the database name
    $script.Options.FileName = "C:\temp\$($DatabaseNameToScript)_WalkDependencies.sql" #setting the file name  
    
    $ObjectsToScript | #foreach to the each SMO Collection created
    foreach-object {
      $NotSystemObjects = ($_).Where{(!($_.IsSystemObject))}  #excluidng System Objects
      $DependencyTree = $script.DiscoverDependencies([ Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$NotSystemObjects,[Microsoft.SqlServer.Management.Smo.DependencyType]::Children) #discovery the childrens #need to explict the type to avoid overloads issues
      $DependencyColletion = $script.WalkDependencies($DependencyTree)
      $Script.script($DependencyColletion.urn ) #scripting the Objects Selected only #need to use the urn
    }
    
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

Using the Transfer Class

Another approach is to using the transfer class. This class is very useful when to copy small databases as we will see in this level, but also it can be used if you need to script all objects in a database.

Lets script the entire database AdventureWorks2016 with this code:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DeathStar'
try {
    $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
    $server.ConnectionContext.connect() #connecting
    $DatabaseToScript = $server.Databases['AdventureWorks2016'] #selecting the Database to Transfer
    $Transfer = new-object 'Microsoft.SqlServer.Management.Smo.Transfer' $DatabaseToScript #new transfer object to AdventureWorks2014
    $Transfer.Options.ScriptBatchTerminator = $true #adds the Go batch terminator. only to file. 
    $Transfer.Options.ToFileOnly = $true #only to file
    $Transfer.Options.DriAll = $false
    $DatabaseNameToScript = $DatabaseToScript -replace '\[|\]' #remnoving the [] from the databasename
    $Transfer.Options.FileName = "C:\temp\$($DatabaseNameToScript).sql"
    $Transfer.ScriptTransfer() #Scripting
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

As you can see, we also can use the ScriptOptions Class and options.

Some Cool Operations Using the Scripter and Transfer Classes

You may want only script a specific table, such as the AwBuildVersion in the AdventureWorsk2016 database. This code will do that:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DeathStar'
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DeathStar'
try {
    $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
    $server.ConnectionContext.connect() #connecting
    $ObjectsToScript = $server.Databases['AdventureWorks2016'].Tables['AWBuildVersion'] #selecting only a table
        
    $Script = new-object 'Microsoft.SqlServer.Management.Smo.Scripter' $server
    
    $script.Options.ScriptBatchTerminator = $true #adds the Go batch terminator. only to file.  
    
    $script.Options.ToFileOnly = $true #using the inherit of ScriptingOptions Class to set the options to my script
    $script.Options.AppendToFile = $true #appending to the file. When the same name it appends
    $script.Options.IncludeIfNotExists = $true
    $script.Options.FileName = 'C:\temp\AdventureWorks2016_Table_AWBuildVersion.sql' #setting the file name  
    $script.Script([ Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$ObjectsToScript) #need to explict the type to avoid overloads 
    
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

Or maybe you want only objects with "business" in the name.

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DeathStar'
try {
    $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
    $server.ConnectionContext.connect() #connecting
    $ObjectsToScript = ($server.Databases['AdventureWorks2016'].Tables).Where({$_.name -like '*Business*'}) #selecting only the tables with Business in the name
        
    $Script = new-object 'Microsoft.SqlServer.Management.Smo.Scripter' $server
    
    $script.Options.ScriptBatchTerminator = $true #adds the Go batch terminator. only to file.  
    
    $script.Options.ToFileOnly = $true #using the inherit of ScriptingOptions Class to set the options to my script
    $script.Options.AppendToFile = $true #appending to the file. When the same name it appends
    $script.Options.IncludeIfNotExists = $true
    $script.Options.FileName = 'C:\temp\AdventureWorks2016_TablesWithBusinessName.sql' #setting the file name  
    $script.Script([ Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$ObjectsToScript) #need to explict the type to avoid overloads 
    
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

Or even the objects of an specific schema . This script looks at the database AdventureWorks2016, and all objects from the schema Person:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DeathStar'
try {
    $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
    $server.ConnectionContext.connect() #connecting
    $ObjectsToScript = $server.Databases['AdventureWorks2016'].Schemas['person'].EnumOwnedObjects()  #selecting the Objects with Person Schema
        
    $Script = new-object 'Microsoft.SqlServer.Management.Smo.Scripter' $server
    
    $script.Options.ScriptBatchTerminator = $true #adds the Go batch terminator. only to file.  
    
    $script.Options.ToFileOnly = $true #using the inherit of ScriptingOptions Class to set the options to my script
    $script.Options.AppendToFile = $true #appending to the file. When the same name it appends
    $script.Options.IncludeIfNotExists = $true
    $script.Options.FileName = 'C:\temp\AdventureWorks2016_ObjectsWithSchemaPerson.sql' #setting the file name  
    $script.Script($ObjectsToScript)
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

We can also script schema and data:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DeathStar'
try {
    $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
    $server.ConnectionContext.connect() #connecting
    $ObjectsToScript = $server.Databases['AdventureWorks2016'].Tables['AWBuildVersion'] #selecting only a table
        
    $Script = new-object 'Microsoft.SqlServer.Management.Smo.Scripter' $server
    
    $script.Options.ToFileOnly = $true #using the inherit of ScriptingOptions Class to set the options to my script
    $script.Options.AppendToFile = $true #appending to the file. When the same name it appends
    $script.Options.FileName = 'C:\temp\AdventureWorks2016_Table_OnlyData_AWBuildVersion.sql' #setting the file name  
    $script.Options.ScriptData = $true # scripting data
    
    $script.EnumScript($ObjectsToScript) #scripting
    
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

Copying a Small Database

One of the cool features off the the Transfer Class is the ability to copy Data and Schema. This was a tool that comes in handy for a small database.

This script will create a new database, Dagobah_copy, and copy all the tables and dependencies from the source database, Dagobah. You can see that the main structure to copy the database is to set the source server and database and the destination server and database. Then it is just transfers the scripts and data.

Another point is we are using a more sophisticated error handle in the transfer structure (Error handling legacy from SQLPSX and my good friend Chad Miller). That's because SMO uses an object, called InnerException that can be several layers deep. In this case the InnerException is used. To get the full detailed error message, we need to traverse the object.

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies

$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' 'DeathStar'
try {
  $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
  $server.ConnectionContext.connect() #connecting
  $DatabaseToScript = $server.Databases['DagoBah'] #selecting the Database to Transfer    
    
  $CopyDB = New-Object 'Microsoft.SqlServer.Management.SMO.Database'  $Server , 'DagoBah_Copy' #creating a database to copy
  $CopyDB.Create() #creating a database to copy
    
  $Transfer = new-object 'Microsoft.SqlServer.Management.Smo.Transfer' $DatabaseToScript #new transfer object from Dagobah
      
  $Transfer.CopyAllTables = $true #all tables
  $Transfer.Options.WithDependencies = $true #with dependencies
  $Transfer.Options.ContinueScriptingOnError =$true #continue
  $Transfer.DestinationDatabase = $CopyDB -replace '\[|\]'  #destination database removing []
  $Transfer.DestinationServer = $Server.Name #server destination
  $Transfer.DestinationLoginSecure = $true
  $Transfer.CopySchema = $true
  try {     
    #$Transfer.ScriptTransfer() optional if you want to generate the scripts
    $Transfer.TransferData()
  } catch {
    $ex = $_.Exception
    $message = $ex.message
    $ex = $ex.InnerException
    while ($ex.InnerException)
    {
      $message += "`n$ex.InnerException.message"
      $ex = $ex.InnerException
    }
    Write-Error $message
  }
    
} catch {
  Write-Error $_.Exception.Message
}

There are a lot more options to use with this class. All stored procedures, views, etc. Just check the results from this for the options:

$Transfer | Get-Member

Or the MSDN documentation Transfer Class. You can see all the options below:

Automating a Simple Task

You may want to script only the data from all tables in the Person schema, with all the files separated by the name of the table. We can do this with this code:

[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') #loading the assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') #loading the assemblies
$InstanceName = 'DeathStar'
$DatabaseName = 'AdventureWorks2014'
$Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $InstanceName
try {
    $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
    $server.ConnectionContext.connect() #connecting
    $ObjectsToScript = ($server.Databases[$DatabaseName].Tables).Where({$_.schema -eq 'Person'}) #selecting all tables only schema person
        
    $Script = new-object 'Microsoft.SqlServer.Management.Smo.Scripter' $server
    
    $script.Options.ToFileOnly = $true #using the inherit of ScriptingOptions Class to set the options to my script
    $script.Options.AppendToFile = $true #appending to the file. When the same name it appends
    $script.Options.FileName = 'C:\temp\AdventureWorks2014_Table_OnlyData_AWBuildVersion.sql' #setting the file name  
    $script.Options.ScriptData = $true # scripting data
    
    $ObjectsToScript |
    ForEach-Object {
      $script.Options.FileName = "C:\temp\$($DatabaseName)_Table_$($_.name)_OnlyData.sql" #setting the file name  
      $Script.EnumScript($_)
    }
    
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

Summing Up

In this article we covered how to scripting databases, tables, stored procedures ,depending objects using scripter , transfer class and coping a small database. That has come in handy when I am scripting objects.

This article is part of the parent stairway Stairway to Server Management Objects (SMO)

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

Related content

Stairway to SQLCLR

Stairway to SQLCLR Level 2: Sample Stored Procedure and Function

In the second level of our Stairway to SQLCLR, we look at how to enable the SQLCLR in SQL Server. We then build an assembly, store procedure, and a function that can be called from your T-SQL code.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (6)

You rated this post out of 5. Change rating

2020-07-09 (first published: )

26,206 reads