PowerShell

A few weeks back I wrote a post for the Hey Scripting Guy Blog on TechNet on scripting out databases with PowerShell. The post was inspired by an issue I had using Red-Gate’s SQL Source Control tool. (If you aren’t familiar with SQL Source Control then check it out. The tool is so full of awesome that I wrote code to get around the only problem I found with it. )

While using the product I ran into a single small problem. When I check a database into Team Foundation Server via SQL Source Control it doesn’t include the drop statements. The deployment procedures at my company just go smoother with DROP statements in each of the stored procedures. Similarly, for databases that haven’t been deployed outside of the Development environment it was better for us to drop and re-create the tables from scratch. It isn’t the kind of thing you’d ever do in production, but given the length of some development projects it is very useful if you can do this when needed.

The version of SQL Source Control I used doesn’t script drops or permissions by default. If there is an option to change this I can’t find it. Rather than switch tools I used this as an excuse to learn how to work with the different scripting options available inside the SMO exposed by PowerShell. Find your options this way:

$dbname="AdventureWorks"
$server="WIN7NetBook"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $server
$db = $SMOserver.databases[$dbname]

$Scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
$Scriptr.Options | Get-Member

To work around this problem I linked my database to my TFS server (from SQL Source Control inside of SSMS). I committed all the changes to TFS so that everything was checked in. Then I opened Visual Studio, checked out out all the objects on to my local machine, generated the scripts with PowerShell just the way I wanted them, overwrote the local TFS code with the code I had just generated, and finally, I checked everything back in to TFS. Doing all this was pretty quick, worked great, and SQL Source Control didn’t care that the scripts weren’t the ones that it had generated.

Be forewarned, when you include the ScriptDrops option it only generates the drop statement. To work around this see my tips here. Now all that is left is to add the Permissions option.

The resulting code also does some cool things like divide objects into folders by type; put everything under a folder structure that includes the name of the database; and another folder that is simply datatime so that you can run it multiple times as you tweak the code to fit your environment, or, just keep old versions of the code laying around in case someone dropped something that hadn’t been checked into TFS yet. I’ve also included the portion of code that only adds the drop statements to objects like stored procedures but not tables.

You can download the script here. Or just take a look at it here. When/if you run the script, it creates a PowerShell function. To call it all you have to do is something like this:

Script-DBObjectsIntoFolders "AdventureWorks" "WIN7NetBook"

And you could even call it in a loop to script out all of your user databases:

foreach ($dbn in invoke-sqlcmd -query "SELECT name
  FROM sys.databases WHERE owner_sid !=0x01"`
 -database master -serverinstance WIN7NetBook )

{ #Begin Loop
$dbn;
Script-DBObjectsIntoFolders  $($dbn.name) "WIN7NetBook"
} #End Loop

Script-DBObjectsIntoFolders

function global:Script-DBObjectsIntoFolders([string]$dbname, [string]$server){
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $server
$db = $SMOserver.databases[$dbname]

$Objects = $db.Tables
$Objects += $db.Views
$Objects += $db.StoredProcedures
$Objects += $db.UserDefinedFunctions

#Build this portion of the directory structure out here in case scripting takes more than one minute.
$SavePath = "C:\TEMP\Databases\" + $($dbname)
$DateFolder = get-date -format yyyyMMddHHmm
new-item -type directory -name "$DateFolder"-path "$SavePath"

foreach ($ScriptThis in $Objects | where {!($_.IsSystemObject)}) {
#Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name 
$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
$scriptr.Options.AppendToFile = $True
$scriptr.Options.AllowSystemObjects = $False
$scriptr.Options.ClusteredIndexes = $True
$scriptr.Options.DriAll = $True
$scriptr.Options.ScriptDrops = $False
$scriptr.Options.IncludeHeaders = $True
$scriptr.Options.ToFileOnly = $True
$scriptr.Options.Indexes = $True
$scriptr.Options.Permissions = $True
$scriptr.Options.WithDependencies = $False
<#Script the Drop too#>
$ScriptDrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
$ScriptDrop.Options.AppendToFile = $True
$ScriptDrop.Options.AllowSystemObjects = $False
$ScriptDrop.Options.ClusteredIndexes = $True
$ScriptDrop.Options.DriAll = $True
$ScriptDrop.Options.ScriptDrops = $True
$ScriptDrop.Options.IncludeHeaders = $True
$ScriptDrop.Options.ToFileOnly = $True
$ScriptDrop.Options.Indexes = $True
$ScriptDrop.Options.WithDependencies = $False

<#This section builds folder structures.  Remove the date folder if you want to overwrite#>
$TypeFolder=$ScriptThis.GetType().Name
if ((Test-Path -Path "$SavePath\$DateFolder\$TypeFolder") -eq "true") `
        {"Scripting Out $TypeFolder $ScriptThis"} `
    else {new-item -type directory -name "$TypeFolder"-path "$SavePath\$DateFolder"}
$ScriptFile = $ScriptThis -replace "\[|\]"
$ScriptDrop.Options.FileName = "" + $($SavePath) + "\" + $($DateFolder) + "\" + $($TypeFolder) + "\" + $($ScriptFile) + ".SQL"
$scriptr.Options.FileName = "$SavePath\$DateFolder\$TypeFolder\$ScriptFile.SQL"

#This is where each object actually gets scripted one at a time.
IF ($ScriptThis.GetType().Name -NE "Table") { $ScriptDrop.Script($ScriptThis) }
$scriptr.Script($ScriptThis)
} #This ends the loop
} #This completes the function