2010-07-30

Powershell Script - Database Snapshot Tool

Greetings everyone!

Sorry it's been so long since I've posted any meaningful code--sometimes life gets in the way of productivity.

Now, to the meat and potatoes. Powershell is a fascinating tool--really an extension of Windows. It allows you to work with objects and assemblies in a more programmie sort of way, but from a command prompt. There are troves of information out there, and I won't even begin to start with any of that now.

However, I've been working with Sql Server Management Objects to script out my database... I've created the following PowerShell script. Point it at a database, and it'll script out DML for Triggers, Stored Procedures, Tables, Primary Keys and Foreign Keys, and other Constraints. It'll also look for an extended properties on tables, and if it's found (and the value is true), it'll treat it as a reference table, and automatically build appropriate insert statements. There's a lot of things I'd like to do to finish this off, and maybe one of my wonderful readers will be able to fix a few of the bugs... but this deserves to be seen!
# Documentation on the SMO can be found here:
# http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx
# PowerShell "$(ProjectDir)\PowerShell\BuildDatabase.ps1" -server 'localhost' -instance 'default' -database 'yourdbname' -newDatabase 'newdbname', -file 'C:\databasescript.sql'

# define parameters
param
(
$server = $(read-host "Server ('localhost' okay)"),
$instance = $(read-host "Instance ('default' okay)"),
$database = $(read-host "Database"),
$newDatabase = $(read-host "New Database Name"),
$file = $(read-host "Script path and file name")
)

###
### Replaces a String withing a file
###
function Replace-String($find, $replace, $replaceFile)
{
(Get-Content $replaceFile) | Foreach-Object {$_ -replace $find, $replace} | Set-Content $replaceFile
}

# Get the date for use elsewhere
$timeStamp = (((Get-Date).GetDateTimeFormats())[94]).Replace("-", "").Replace(":", "").Replace(" ", "_")



# Load the SQL Server Management Object
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server
$dbs=$s.Databases

# This Sql Connection is the .NET SQL Connection, used for Querying data, not writing DDL

# Create SqlConnection object and define connection string
$connection = New-Object System.Data.SqlClient.SqlConnection
if ($database -ne "default")
{
$connection.ConnectionString = "Data Source=" + $server + ";Initial Catalog=" + $database + ";Integrated Security=SSPI;"
}
else
{
$connection.ConnectionString = "Data Source=" + $server + "\" + $instance + ";Initial Catalog=" + $database + ";Integrated Security=SSPI;"
}




# Scripting OPtions
$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.AllowSystemObjects = $false
$so.AnsiPadding = $false
$so.AnsiFile = $false
$so.DdlHeaderOnly = $false
$so.IncludeHeaders = $false

"USE [master]" | Out-File $file
"GO" | Out-File $file -append

#if ($newDatabase -eq "")
#{
"DECLARE @spid int, @killstatement nvarchar(10)" | Out-File $file -append
"declare c1 cursor for select request_session_id from sys.dm_tran_locks where resource_type='DATABASE' AND DB_NAME(resource_database_id) = '" + $newDatabase + "'" | Out-File $file -append
"open c1" | Out-File $file -append
"fetch next from c1 into @spid" | Out-File $file -append
"while @@FETCH_STATUS = 0" | Out-File $file -append
"begin" | Out-File $file -append
" IF @@SPID <> @spid" | Out-File $file -append
" begin" | Out-File $file -append
" set @killstatement = 'KILL ' + cast(@spid as varchar(3))" | Out-File $file -append
" exec sp_executesql @killstatement" | Out-File $file -append
" end" | Out-File $file -append
" fetch next from c1 into @spid" | Out-File $file -append
"end" | Out-File $file -append
"close c1" | Out-File $file -append
"deallocate c1" | Out-File $file -append
"GO" | Out-File $file -append
"IF EXISTS(SELECT name FROM sys.databases WHERE name = '" + $newDatabase + "')" | Out-File $file -append
"BEGIN" | Out-File $file -append
"BACKUP DATABASE " + $database | Out-File $file -append
" TO DISK = '" + $database + "_" + $timeStamp + ".bak'" | Out-File $file -append
" WITH FORMAT," | Out-File $file -append
" MEDIANAME = 'Z_SQLServerBackups'," | Out-File $file -append
" NAME = 'Full Backup of Chatty Chef before Auto Restore Dated " + $timeStamp + "'" | Out-File $file -append
"END" | Out-File $file -append
"GO" | Out-File $file -append
"IF EXISTS(SELECT name FROM sys.databases WHERE name = '" + $newDatabase + "')" | Out-File $file -append
"BEGIN" | Out-File $file -append
"DROP DATABASE " + $newDatabase | Out-File $file -append
"END" | Out-File $file -append
"GO" | Out-File $file -append
# $newDatabase = $database
#}
"CREATE DATABASE [" + $newDatabase + "]" | Out-File $file -append
"GO" | Out-File $file -append
"USE [" + $newDatabase + "]" | Out-File $file -append
"GO" | Out-File $file -append

#Generate script for all Roles
#foreach ($Roles in $dbs[$database].Roles)
#{
# $Roles.Script($so) " | out-File $file -append
#}

foreach ($User in $dbs[$database].Users)
{
if ($User.Name -like "db_*" -or
$User.Name -like "dbo" -or
$User.Name -like "sys" -or
$User.Name -like "guest" -or
$User.Name -like "INFORMATION_SCHEMA"
)
{}
else
{
"IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = '" + $User.Name + "')" | out-File $file -append
"BEGIN" | out-File $file -append
" CREATE LOGIN ["+ $User.Name +"] WITH PASSWORD = '" + $User.Name + "', CHECK_POLICY = OFF" | out-File $file -append
"END" | out-File $file -append
"GO" | out-file $file -append
$User.Script($so) | out-File $file -append

"GO" | out-file $file -append
}
}


#Generate script for all schemas

"-----------------------------" | out-File $file -append
"-- Schemas" | out-File $file -append
"-----------------------------" | out-File $file -append
foreach ($schemas in $dbs[$database].Schemas)
{
if ($schemas.Name -like "db_*" -or
$schemas.Name -like "dbo" -or
$schemas.Name -like "sys" -or
$schemas.Name -like "guest" -or
$schemas.Name -like "INFORMATION_SCHEMA"
)
{}
else
{
$schemas.Script($so) | out-File $file -append
"GO" | out-file $file -append
}
}



#Generate script for all tables

"-----------------------------" | out-File $file -append
"-- Tables Part 1 - Table Structure" | out-File $file -append
"-----------------------------" | out-File $file -append
foreach ($tables in $dbs[$database].Tables)
{
if ($tables.Name -ne "sysdiagrams" -and
$tables.Name -ne "sys"
)
{
$tables.Script($so) | out-File $file -append
"GO" | out-File $file -append
}
}



"-----------------------------" | out-File $file -append
"-- Tables Part 2 - Indices and Triggers" | out-File $file -append
"-----------------------------" | out-File $file -append
foreach ($tables in $dbs[$database].Tables)
{
if ($tables.Name -ne "sysdiagrams" -and
$tables.Name -ne "sys"
)
{
#Generate script for all indexes in the specified table
foreach($index in $tables.Indexes)
{
$index.Script($so) | out-File $file -append
"GO" | out-File $file -append
}

foreach($tableTriggers in $tables.Triggers)
{
$tableTriggers.Script($so) | out-File $file -append
"GO" | out-File $file -append
}
}
}


"-----------------------------" | out-File $file -append
"-- Tables Part 3 - Generate Reference Table Data" | out-File $file -append
"-----------------------------" | out-File $file -append
foreach ($table in $dbs[$database].Tables)
{
if ($table.Name -ne "sysdiagrams" -and
$table.Name -ne "sys"
)
{
#Generate script for all indexes in the specified table
foreach($extendedProperty in $table.ExtendedProperties)
{
if
(
$extendedProperty.Name -eq "IsReferenceTable" -and
$extendedProperty.Value -eq "True"
)
{
"EXEC sys.sp_addextendedproperty " | out-File $file -append
"@name = N'IsReferenceTable', " | out-File $file -append
"@value = N'true', " | out-File $file -append
"@level0type = N'SCHEMA', @level0name = " + $table.Schema + ", " | out-File $file -append
"@level1type = N'TABLE', @level1name = " + $table.Name + " " | out-File $file -append
"GO" | out-File $file -append
"INSERT INTO [" + $table.Schema + "].[" + $table.Name + "]" | out-File $file -append
"(" | out-File $file -append
$columnCounter = 0
foreach ($column in $table.Columns)
{
if ($columnCounter -gt 0)
{
", [" + $column.Name + "]" | out-File $file -append
}
else
{
" [" + $column.Name + "]" | out-File $file -append
}
$columnCounter++
}
")" | out-File $file -append
"VALUES" | out-File $file -append
"(" | out-File $file -append
# Build a Query to Load in the Data from the reference table
$query = "SELECT "
$columnCounter = 0
foreach ($column in $table.Columns)
{
if ($columnCounter -gt 0)
{
$query = $query + ",[" + $column.Name + "]"
}
else
{
$query = $query + "[" + $column.Name + "]"
}
$columnCounter++
}
$query = $query + " FROM [" + $table.Schema + "].[" + $table.Name + "]"
# "-- " + $query | out-File $file -append
$connection.open()

# Create SqlCommand object, define command text, and set the connection
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $query
$cmd.Connection = $connection

# Create SqlDataReader
$dr = $cmd.ExecuteReader()

if ($dr.HasRows)
{
While ($dr.Read())
{
# Write-Host $dr["FirstName"] $dr["LastName"]
$columnCounter = 0
foreach ($column in $table.Columns)
{
# Write-Host $column.Name $column.DataType
Write-Host $columnCounter
if ($columnCounter -eq 0)
{
if
(
$column.DataType.ToString() -eq "int" -OR
$column.DataType.ToString() -eq "bit"
)
{
" " + $dr[$column.Name] | out-File $file -append
}
else
{
" '" + $dr[$column.Name] + "'" | out-File $file -append
}
}
else
{
if
(
($column.DataType.ToString() -eq "int") -OR
($column.DataType.ToString() -eq "bit")
)
{
", " + $dr[$column.Name] | out-File $file -append
}
else
{
", '" + $dr[$column.Name] + "'" | out-File $file -append
}
}
$columnCounter++
}
}
}
Else
{
Write-Host The DataReader contains no rows.
}

# Close the data reader and the connection
$dr.Close()
$connection.Close()
")" | out-File $file -append
}
}
}
}

#Generate script for all extended Stored Procedures
"-----------------------------" | out-File $file -append
"-- Extended Stored Procedures" | out-File $file -append
"-----------------------------" | out-File $file -append
foreach ($Extendedprocedures in $dbs[$database].ExtendedStoredProcedures)
{
if ($Extendedprocedures.Schema -ne "sys" -and
$Extendedprocedures.Schema -ne "sys"
)
{
"GO" | out-File $file -append
$Extendedprocedures.Script($so) | out-File $file -append
"GO" | out-File $file -append
}
}


$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.AllowSystemObjects = $false
$so.AnsiPadding = $false
$so.AnsiFile = $false
$so.IncludeHeaders = $false

#Generate script for all Stored Procedures
"-----------------------------" | out-File $file -append
"-- Stored Procedures" | out-File $file -append
"-----------------------------" | out-File $file -append
foreach ($procedures in $dbs[$database].StoredProcedures)
{
if ($procedures.Schema -ne "sys")
{
"GO" | out-File $file -append
$procedures.Script($so) | out-File $file -append
"GO" | out-File $file -append
}
}

#Generate script for all Views
"-----------------------------" | out-File $file -append
"-- Views" | out-File $file -append
"-----------------------------" | out-File $file -append
foreach ($Views in $dbs[$database].Views)
{
if ($Views.Schema -ne "sys" -and
$Views.Schema -ne "INFORMATION_SCHEMA")
{
"GO" | out-File $file -append
$Views.Script($so) | out-File $file -append
"GO" | out-File $file -append
}
}

#Generate scription for all UserDefinedFunctions
"-----------------------------" | out-File $file -append
"-- User Defined Functions" | out-File $file -append
"-----------------------------" | out-File $file -append
foreach ($UserDefinedFunction in $dbs[$database].UserDefinedFunctions)
{
if ($UserDefinedFunction.schema -ne "sys")
{
$UserDefinedFunction.Script($so) | out-File $file -append
"GO" | out-File $file -append
}
}


#Generate script for database Triggers
"-----------------------------" | out-File $file -append
"-- Triggers" | out-File $file -append
"-----------------------------" | out-File $file -append
foreach ($Triggers in $dbs[$database].Triggers)
{
if ($Triggers.Schema -ne "sys")
{
$Triggers.Script($so) | out-File $file -append
"GO" | out-File $file -append
}
}

Replace-String "SET ANSI_NULLS ON" "" $file
Replace-String "SET QUOTED_IDENTIFIER ON" "" $file

Zoidberg Away!