THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Ben Miller

Final DB Scripter using Powershell

I decided to finish the scripter even though I could not get the Add-Content to work without giving me a multitude of ??? in the file.  I decided that I could script out the Tables, Views and DB and also output the contents of the INSERT statements into 2 files instead of 1, then concat the files together or just use one for the DDL and the other for the DML.  (good separation of Schema and Data huh?) 

To run this and get both outputs, you can run it like this:  (inside Powershell)

PS C:\Scripts > ./ScriptDB.ps1 >somefile.txt

Have a great one and enjoy.

# ScriptDB.ps1
# Scripts db, tables, views in database of your choice
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null

$dbname = "Database Name"
$filename = "c:\temp\output.txt"
$serverName = "Server Name"

$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")

$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases[$dbname]

$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"

$scr.Server = $srv
$options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$options.DriAll = $True
$options.ClusteredIndexes = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.IncludeHeaders = $true
$options.AppendToFile = $false
$options.FileName = $filename
$options.ToFileOnly = $true

$scr.Options = $options
$options.AppendToFile = $true
$views = $db.Views | where {$_.IsSystemObject -eq $false}

$tree = $scr.DiscoverDependencies($db.Tables, $True)

$depwalker = New-Object "Microsoft.SqlServer.Management.SMO.DependencyWalker"
$depcoll = $depwalker.WalkDependencies($tree)

#Using the sp_generate_inserts from the webpage

$col = $depcoll | foreach {
 "EXEC sp_generate_inserts '" + $_.Urn.GetAttribute("Name") + "'"

$ds = New-Object "System.Data.DataSet"
$ds = $db.ExecuteWithResults($col)

# using the Piping features to go from Tables, to Table, to Rows, to Row and to Column 1 $_[0]
$ds.Tables | foreach { $_.Rows | foreach {$_[0]} }


Published Tuesday, November 27, 2007 12:36 PM by dbaduck
Filed under:



rmaw said:

Just one small thing, which may be important for some people, you need to add the schema name to the sp_generate_inserts calls or it does not always work. The calls should have the form:

EXEC sp_generate_inserts @table_name='TableName',@owner='SchemaName'

To get the schema is just a matter of calling Urn.GetAttribute with "Schema" as a parameter.

December 13, 2007 1:41 PM

preguntoncojonero said:

how can do it using c# ?? thanks

April 29, 2009 2:05 PM

Ganesh said:

I am getting the below error, can you please help ?

Exception calling "Script" with "1" argument(s). "An exception occurred in SMO"

$scr.Script(<<<< $views)

August 24, 2009 8:58 PM
New Comments to this post are disabled

About dbaduck

Ben Miller is a Senior Database Administrator for HealthEquity in Draper, UT. He has been working with SQL since SQL Server 6.0 (1998) and has had a variety of roles in his career, including SQL Support and MVP Lead at Microsoft.
Privacy Statement