THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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
$scr.Script($db)
$scr.Script($db.Tables)
$options.AppendToFile = $true
$views = $db.Views | where {$_.IsSystemObject -eq $false}
$scr.Script($views)

$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 http://vyaskn.tripod.com/code.htm

$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:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

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.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement