Recently I was looking to export all of the objects in one of my databases into text files so that I can add them to SVN (source code control). After investigating and writing a few T-SQL scripts that accomplished this task I ran across an article that used Power Shell to do the same function. Only it did it better. After taking a Crash Power Shell self course, I modified the example script and achieved my goal of saving the create scripts for adding them to SVN. After some tweaking, I am now able to save the db objects to text files. The way I export them, SVN will only flag changes to the files not the fact that the file date and time has changed. Cool feature of SVN to actually analyze the content.

There are a number of script options that can be set to modify the way the script gets saved. For the most part they are the same options available when you generate scripts from SSMS.

I am looking forward to using Power Shell in general server management in the future.

The script I created is listed below. Please leave a comment if you download, use it or have any suggestions for improvements.

<#
.SYNOPSIS
Save Sql Server DB Object to folder for storage into Source Code Control System.
.DESCRIPTION
This scriptlet saves SQL Server data base objects, Tables, Stored Procedures, Views and Functions to a directory
so that they can be stored in a source code control system such as SVN for safe keeping. Starting the script can be with parameters.
If no parameters are specified the program will promt for them. The files can be saved in a standard place or can be saved
in a new directory each time with the data and time as the directory name.

Parameter:
-srvname = “some default DB if desired” the SQL server we are connecting to – you can set a default in the script or you can delete it an always require the entery
-username =”sa” the SQL Server user name – you can set a default in the script or delete it to always require a value
-password the password needed for the SQL server login you will be promoted if you do not enter a value
-path the output path – you can set a default in the script or delete it to always require a value
-CreateVerbosePath =$false this setting if set to true will cause the output files to be stored in a unique directory named Date and Time of the export
.NOTES
File Name : Save_SQL_Server_DB_Objects_To_Folder.ps1
Author : Richard Siena rich@richardsiena.com
Prerequisite : PowerShell V3 over Vista and upper.
Copyright 2014 – Richard Siena
.LINK
Script posted over:
http://www.richardsiena.com/techblog
.EXAMPLE
Save_SQL_Server_DB_Objects_To_Folder.ps1
.EXAMPLE
Save_SQL_Server_DB_Objects_To_Folder.ps1 -password xyz
#>

[CmdletBinding()]
Param(
[Parameter()] [string]$srvname = “defautl server”,
[Parameter()] [string]$dbname=”default db”,
[Parameter()] [string]$username =”sa”,
[Parameter()] [string]$password ,
[Parameter()] [boolean]$CreateVerbosePath =$false,
[Parameter()] [string]$Path = “$home\Documents\SQL Server Management Studio\Projects\SQL\SQLObjects\”
)

#—————————Test for accepatable values———————
if ($srvname.Length -eq 0 ) { $srvname = $( Read-Host “Enter SQL Server host name: ” ) }
if ($dbname.Length -eq 0 ) { $dbname = $( Read-Host “Enter SQL Server dbname name: ” ) }
if ($username.length -eq 0 ) { $username = $( Read-Host “Enter User name: ” ) }
if ($password.Length -eq 0 ) { $password = $( Read-Host “Enter Password: ” ) }
if ($Path.Length -eq 0 ) { $Path = $( Read-Host “Enter Path: ” ) }

if (($srvname.Length -eq 0 ) -or ($username.length -eq 0 ) -or ($password.Length -eq 0 ) -or ($Path.Length -eq 0 ) -or ($dbname.Length -eq 0 ) )
{
write-output “Must enter all parameters use get-help for more information”
exit
}

#———— debugging data ———————–
<#
Write-Debug “srvname = ” $srvname
write-Debug “DBname = ” $dbname
write-Debug “username = ” $username
write-Debug “password = ” $password
write-Debug “CreateVerbosePath = ” $CreateVerbosePath
write-Debug “Path = ” $Path
#>
#————–done with params————–
$FullPath=””

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

$SMOserverConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$SMOserverConn.ServerInstance=$srvname
$SMOserverConn.LoginSecure = $false
$SMOserverConn.Login = $username
$SMOserverConn.Password = $password

$SMOserver = new-object Microsoft.SqlServer.Management.SMO.Server($SMOserverConn)
$db = $SMOserver.Databases[$dbname]
#————we are now logged into the server————————–

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

#—-build paths
if ($CreateVerbosePath) {
$SavePath = $Path + $($dbname)
$DateFolder = get-date -format yyyyMMddHHmm
new-item -type directory -name “$DateFolder”-path “$SavePath”
}
else
{
$SavePath = $Path
}
#———Get the item and save it——————–
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.IncludeHeaders = $False
$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 = $False
$ScriptDrop.Options.AllowSystemObjects = $False
$ScriptDrop.Options.ClusteredIndexes = $True
$ScriptDrop.Options.DriAll = $True
$scriptDrop.Options.IncludeIfNotExists = $True
$ScriptDrop.Options.ScriptDrops = $True
$ScriptDrop.Options.IncludeHeaders = $False
$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 ($CreateVerbosePath) {

if ((Test-Path -Path “$SavePath\$DateFolder\$TypeFolder”) -eq “true”) {
#”Scripting Out $TypeFolder $ScriptThis”
}
else {
new-item -type directory -name “$TypeFolder”-path “$SavePath\$DateFolder”
}
$FullPath = “$SavePath\$DateFolder”
}
else {

if ((Test-Path -Path “$SavePath\$TypeFolder”) -eq “true”) {
#”Scripting Out $TypeFolder $ScriptThis”
}
else {
new-item -type directory -name “$TypeFolder”-path “$SavePath”
}
$FullPath = “$SavePath”
}

“Scripting Out $TypeFolder $ScriptThis”

$ScriptFile = $ScriptThis -replace “\[|\]”
$ScriptDrop.Options.FileName = “” + $($FullPath) + “\” + $($TypeFolder) + “\” + $($ScriptFile) + “.SQL”
$scriptr.Options.FileName = “$FullPath\$TypeFolder\$ScriptFile.SQL”

#This is where each object actually gets scripted one at a time.
$ScriptDrop.Script($ScriptThis)
$scriptr.Script($ScriptThis)

} #This ends the loop

$SMOserver.ConnectionContext.Disconnect()

Share This