Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Auto dump out database schema

By Neil Bryan, 2014/02/04

This is a PowerShell script that can be used to dump out a database schema to a text file (similar to the Generate Scripts option in SSMS).

I wrote it to dump out out live DB schemas which are not under source control (our dev schemas are).

We run the scripts once a week.

They are very useful when someone accidently overwrites say a stored procedure on the live server.

To run from the command line...

Powershell.exe -File "C:\PowerShell\Output_Schema.ps1" - $SQLInstance localhost\sqlexpress - $Output_Folder_Name "D:\Reports\Schema Dumps" - $Database Accounts_Database

In this example, the above script (Output_Schema.ps1) is placed in the folder c:\PowerShell the output text file is written to the folder D:\Reports\Schema Dumps and the database to use is Accounts_Database.

NOTE: The database parameter is optional, if omitted, then all databases on the server will documented.

A text file per database is generated.

#------------------------------------------------------------------------------------------------
# Name:       Output_Schema.ps1
# Author:     Neil Bryan
# Date:       20140120
# Description:Dumpout database creation scripts
# Params:     $SQLInstance	(Required) The SQL Server Instance Name
#			  $Database     (Optional) The database name to report on. if not supplied then all
#                                      databases
# Notes:      Examples of how to run from the DOS command line...
#
#             Powershell.exe -File "C:\PowerShell\Output_Schema.ps1" - $SQLInstance localhost\sqlexpress - $Output_Folder_Name "D:\Reports\Schema Dumps"
#             or
#             Powershell.exe -File "C:\PowerShell\Output_Schema.ps1" - $SQLInstance localhost\sqlexpress - $Output_Folder_Name "D:\Reports\Schema Dumps" - $Database Accounts_Database
#
# Amendment History
# -----------------
#
# Who          Date      Ref                 Description
# ---          ----      ---                 -----------
# Neil Bryan   20140120                      Created
#------------------------------------------------------------------------------------------------

#------------------------------------------------------------------------------------------------
# Setup parameters
#------------------------------------------------------------------------------------------------
param(
	[String] $SQLInstance, # the SQL instance to generate a script from, in Server\Instance format.
	[String] $Output_Folder_Name, # Output file name
	[String] $Database # the database to generate the index scripts from.

)
#------------------------------------------------------------------------------------------------
# Check parameters
#------------------------------------------------------------------------------------------------
if ((!$SQLInstance) -or (!$Output_Folder_Name)) 
{
	#------------------------------------------------------------------------------------------------
	# Parameters not setup correctky so bail out.
	#------------------------------------------------------------------------------------------------
	Write-Warning 'Syntax: & "Output_Schema.ps1" "SQLServer\Instance"  "OutputFolderName" '
	Write-Warning 'Syntax (Optional Parameters): '
	Write-Warning '  "DatabaseName" (Optional) '
	Write-Host ""
}
else
{
	#------------------------------------------------------------------------------------------------
	# OK, let's do this.
	#------------------------------------------------------------------------------------------------
	
	#------------------------------------------------------------------------------------------------
	# Is the output folder OK?
	#------------------------------------------------------------------------------------------------
	if ((Test-Path $Output_Folder_Name) -eq 0)
	{
		Write-Warning ('Cannot access output folder ' + $Output_Folder_Name);	
		exit
	}
	
	
	
	#------------------------------------------------------------------------------------------------
	# Load SMO, connect to server and database
	#------------------------------------------------------------------------------------------------
	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
	Try
	{
		$server = New-Object ('Microsoft.SqlServer.Management.SMO.Server') "$SQLInstance"
		$date = Get-Date -format yyyyMMddhhmmss
		$String_Length = $SQLInstance.length
		$Counter = 0
		$File_Name = ""
		$Char = ""
		$Valid_Chars = "0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","Y","Z"
		while ($Counter -lt $String_Length)
		{
			$Char = $SQLInstance.Substring($Counter,1)
			if ( $Valid_Chars -contains $Char.ToUpper())
			{
				$File_Name = ($File_name + $Char)
			}
			else
			{
				$File_Name = ($File_name + "-")
			}
			$counter++
		}
		$String_Length = $Output_Folder_Name.Length
		if ($Output_Folder_Name.Substring($String_Length-1,1) -ne "\" )
		{
			$Output_Folder_Name = ($Output_Folder_Name + "\")
		}
		$Output_File_Name = ($Output_Folder_Name + $File_name + "_" + $date + ".sql")
	
		echo ("Generating server details to file:" + $Output_File_Name)
	}
	Catch [system.Exception]
	{
		$Error_File = ($Output_Folder_Name + "Output_Schema_Errors_" + $date + ".txt")
		$Error | Out-File $Error_File -Append
		echo $Error
		exit
	}

 

	"-------------------------------------------------------------------------" | Out-File $Output_File_Name 
	("--  Genering Database Objects for Server: " + $SQLInstance)  | Out-File $Output_File_Name -Append  
	("--  Date:                                 " + $date)  | Out-File $Output_File_Name -Append
	"-------------------------------------------------------------------------"   | Out-File $Output_File_Name -Append
	""  | Out-File $Output_File_Name -Append
	""  | Out-File $Output_File_Name -Append

	#------------------------------------------------------------------------------------------------
	# Create the server logins
	#------------------------------------------------------------------------------------------------
	"------------------------------------------------------------------------"  | Out-File $Output_File_Name -Append
	"-- Create Server Logins" | Out-File $Output_File_Name -Append
	"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
	
	Try
	{
		foreach ($login in $server.Logins)
		{
			$login.script() | Out-File $Output_File_Name -Append
		}
		"" | Out-File $Output_File_Name -Append
		"" | Out-File $Output_File_Name -Append
	}
	Catch [system.Exception]
	{
		$Error_File = ($Output_Folder_Name + "Output_Schema_Errors_" + $date + ".txt")
		$Error | Out-File $Error_File -Append
		echo $Error
		exit
	}
	
 
	#------------------------------------------------------------------------------------------------
	# Create the linked servers
	#------------------------------------------------------------------------------------------------
	"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
	"-- Create Linked Server" | Out-File $Output_File_Name -Append
	"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
	Try
	{
		foreach ($linked_server in $server.LinkedServers)
		{
			$linked_server.script()  | Out-File $Output_File_Name -Append
		}
		""  | Out-File $Output_File_Name -Append
		""  | Out-File $Output_File_Name -Append
	}
	Catch [system.Exception]	
	{
		$Error_File = ($Output_Folder_Name + "Output_Schema_Errors_" + $date + ".txt")
		$Error | Out-File $Error_File -Append
		echo $Error
		exit
	}
  
	#------------------------------------------------------------------------------------------------
	# Loop around the databases on the server
	#------------------------------------------------------------------------------------------------
	$System_Databases = "MASTER","MSDB","TEMPDB","MODEL"

	foreach ($db in $server.Databases)
	{
	
		Try
		{
	
			#------------------------------------------------------------------------------------------------
			# If a database name was specified, only do it for that database. 
			#------------------------------------------------------------------------------------------------
			if ((!$Database) ) 
			{
			}
			else
			{
				if ($db.Name -ne $Database)
				{
					continue
				}
			}
			
			#------------------------------------------------------------------------------------------------
			#Ignore the system databases probably a better way of doing it!!
			#------------------------------------------------------------------------------------------------
			if ($System_Databases -contains $db.Name.ToUpper())
			{
				continue
			}
		
			#------------------------------------------------------------------------------------------------
			# Don't consider abnormal databases
			#------------------------------------------------------------------------------------------------
			if ($db.Status -ne "Normal")
			{
				("Database " + $db.Name + " is unavailable. Discontinuing with this database.") | Out-File $Output_File_Name 
				echo ("Database " + $db.Name + " is unavailable. Discontinuing with this database.")
				continue
			}
			
			$Output_File_Name = ($Output_Folder_Name + $File_name + "_" + $db.Name + "_" + $date + ".sql")
			echo ("Generating details for database " + $db.Name + " to file:" + $Output_File_Name)
			
			"------------------------------------------------------------------------" | Out-File $Output_File_Name 
			("-- Scripting database " + $db.Name) | Out-File $Output_File_Name -Append
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			""  | Out-File $Output_File_Name -Append
			#------------------------------------------------------------------------------------------------
			# Script the database
			#------------------------------------------------------------------------------------------------
			$db.Script()  | Out-File $Output_File_Name -Append
			"" | Out-File $Output_File_Name -Append
			"" | Out-File $Output_File_Name -Append
	
			#------------------------------------------------------------------------------------------------
			# Create the database roles
			#------------------------------------------------------------------------------------------------
			("USE [" + $db.Name + "]") | Out-File $Output_File_Name -Append
			"GO" | Out-File $Output_File_Name -Append
			"" | Out-File $Output_File_Name -Append
			"" | Out-File $Output_File_Name -Append
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			("-- Scripting database " + $db.Name + " Roles" ) | Out-File $Output_File_Name -Append
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			foreach ($role in $db.Roles)
			{
				$role.script()  | Out-File $Output_File_Name -Append
			}
			"" | Out-File $Output_File_Name -Append
			"" | Out-File $Output_File_Name -Append
		
			#------------------------------------------------------------------------------------------------
			# Create the database users
			#------------------------------------------------------------------------------------------------
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			("-- Scripting database " + $db.Name + " Users" ) | Out-File $Output_File_Name -Append
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			foreach ($user in $db.Users)
			{
				$user.script() | Out-File $Output_File_Name -Append
			}
			
			#------------------------------------------------------------------------------------------------
			# Create the database shemas
			#------------------------------------------------------------------------------------------------
			foreach ($schema in $db.Schemas)
			{
				$schema.script() | Out-File $Output_File_Name -Append
			}
			""  | Out-File $Output_File_Name -Append
			""  | Out-File $Output_File_Name -Append
	
			#------------------------------------------------------------------------------------------------
			# Create the database tables
			#------------------------------------------------------------------------------------------------
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			("-- Scripting database " + $db.Name + " Tables and Indexes" ) | Out-File $Output_File_Name -Append
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			foreach ($table in $db.Tables)
			{
				$table.Script() | Out-File $Output_File_Name -Append
				
				#------------------------------------------------------------------------------------------------
				# Create the table indexes
				#------------------------------------------------------------------------------------------------
				foreach($ix in $table.Indexes )
				{
					"" | Out-File $Output_File_Name -Append
					$ix.Script()  | Out-File $Output_File_Name -Append
				}
				"" | Out-File $Output_File_Name -Append
				"" | Out-File $Output_File_Name -Append
			}
	
			#------------------------------------------------------------------------------------------------
			# Create the database views
			#------------------------------------------------------------------------------------------------
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			("-- Scripting database " + $db.Name + " Views" ) | Out-File $Output_File_Name -Append
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			foreach ($views in $db.Views)
			{
				"" | Out-File $Output_File_Name -Append
				$views.Script() | Out-File $Output_File_Name -Append
			}
			"" | Out-File $Output_File_Name -Append
			"" | Out-File $Output_File_Name -Append
			
			
			#------------------------------------------------------------------------------------------------
			# Create the database stored procedures 
			#------------------------------------------------------------------------------------------------
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			("-- Scripting database " + $db.Name + " Stored procedures" ) | Out-File $Output_File_Name -Append
			"------------------------------------------------------------------------" | Out-File $Output_File_Name -Append
			foreach ($Stored_Procedure in $db.StoredProcedures)
			{
	
				if ($Stored_Procedure.Schema.ToString() -ne 'sys')
				{
					$Stored_Procedure.Script() | Out-File $Output_File_Name -Append
				}
				"" | Out-File $Output_File_Name -Append
				"" | Out-File $Output_File_Name -Append
			}
			"" | Out-File $Output_File_Name -Append
			"" | Out-File $Output_File_Name -Append
			"" | Out-File $Output_File_Name -Append
			"" | Out-File $Output_File_Name -Append
		}
		catch [system.Exception]	
		{
			$Error_File = ($Output_Folder_Name + "Output_Schema_Errors_" + $date + ".txt")
			$Error | Out-File $Error_File -Append
			echo $Error
		}
	}
	echo ""
	echo ""
}