Scripting out a database

Switching gears a little bit, much of my attention this week has revolved around backups. We have several large databases that have gotten to the point where full backups every night have become unwieldy. So I’ve been redesigning the maintenance plans for them around a full backup once a week and differentials nightly. So far it’s working out very well, saving us a lot of time, disk, and tape. Woot!

While I was thinking about backups in general, I also started thinking about a question that’s come up before: how to script out all the database objects in an automated fashion. Whether it’s so you can create a second ’empty shell” copy of your database, or so you can restore a single stored procedure that you accidentally dropped, or just because it’s a development environment and you don’t care about the data so much as the object definitions. (Or because it’s just cool.)  Whatever the reason, I personally think this is something that could come in handy, and I hadn’t come across any way in SQL Server to automate the scripting a database.

So i did some searching and found a few third party tools (nah), some options that revolved around VB or C# or something (yeah, have I mentioned I’m not a programmer?). Then I came across a few partial solutions using powershell. Now, again me != programmer, but I can handle a little bit of powershell. They were pretty straightforward, but they weren’t quite… enough. So I took a little of each, threw in a few touches of my own, and voila, a solution that I’m pretty darn pleased with. Can it be improved upon? I fairly confident it can be. And probably will be. But in the meantime it gets the job done. And it can be scheduled either through a database job or via the Windows scheduler, which was the whole point.  And so, without further ado, I present… the script… (ooooooo!)

# SQLExportDBSchema.ps1
# Usage -
# From PS:  .SQLExportDBSchema.ps1 "[output location]" "[Server]" "[Database]"
# From CMD: powershell -command "& 'D:powershellSQLExportDBSchema.ps1' "[output location]" "[Server]" "[Database]" "

param(
	[string]$filepath=$null,
	[string]$srv=$null,
	[string]$database=$null
	)

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

$filepath = $filepath+""

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $srv

$db=$s.Databases[$database]

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

$scrp.Options.AppendToFile = $False
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $False
$scrp.Options.IncludeHeaders = $True
$scrp.Options.IncludeIfNotExists = $True
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.Triggers = $True
$scrp.Options.Permissions = $True

#CREATE DATABASE
$outfile =$filepath+$database+"_create.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db)

#SECURITY
$outfile = $filepath+$database+"_security.sql"
$scrp.Options.FileName = $outfile
$scrp.Options.IncludeDatabaseRoleMemberships = $true
$scrp.Script($db.Roles)
$scrp.Options.AppendToFile = $True
$scrp.Script($db.ApplicationRoles)
$scrp.Script($db.Users)
$scrp.Script($db.Schemas)

$scrp.Options.AppendToFile = $False
$scrp.Options.WithDependencies = $True

#PARTITIONING
$outfile = $filepath+$database+"_partitioning.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db.PartitionFunctions)
$scrp.Options.AppendToFile = $True
$scrp.Script($db.PartitionSchemes)

$scrp.Options.AppendToFile = $False

#TABLES
$outfile = $filepath+$database+"_tables.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db.Tables)

#DDLTRIGGERS
$outfile = $filepath+$database+"_triggers.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db.Triggers)

#VIEWS
$outfile = $filepath+$database+"_views.sql"
$scrp.Options.FileName = $outfile
$views = $db.Views | where {$_.IsSystemObject -eq $false}
$scrp.Script($views)

#FUNCTIONS
$outfile = $filepath+$database+"_functions.sql"
$scrp.Options.FileName = $outfile
$functions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
$scrp.Script($functions)

#STORED PROCS
$outfile = $filepath+$database+"_storedProcs.sql"
$scrp.Options.FileName = $outfile
$storedprocs = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
$scrp.Script($storedprocs)

#SYNONYMS
$outfile = $filepath+$database+"_synonyms.sql"
$scrp.Options.FileName = $outfile
$scrp.Script($db.Synonyms)

Also recommended: