Last Updated: 01/19/2012 11:49:00 AM

Using Powershell to backup your Stored Procedures and Triggers

Using Powershell to backup your Stored Procedures and Triggers

Powershell is like a bat file on steriods. Wiki defines it this way: "Windows PowerShell is Microsoft's task automation framework, consisting of a command-line shell and associated scripting language built on top of, and integrated with, the .NET Framework. PowerShell provides full access to COM and WMI, enabling administrators to perform administrative tasks on both local and remote Windows systems." 

Well said.  I still like my definition better. 

In our development environment we daily backup all of our code from our Coldfusion server and SQL Server.  As we grew and the MSSQL databases grew in size the backups just took too long.  Plus we were backing up junk test data, when really all we needed was the stored procedures and triggers.  The following powershell script connects to a mapped drive and writes all the SPs and Triggers to individual files for every database on the target system.  The mapped drives are actually a "Tape Sucks" drive array with 7 One TB hard drives.  Weekly we swap the drives and take them to the bank lock box.

 

 

 

 

 

 

#PLEASE PUT THE DRIVE NAME YOU WANT TO WRITE TO BELOW EXAMPLE "C:"
$rootDrive = "H:"


###############################################################################################
#
######## HOW TO INSTALL
#
# 1. Download powershell from microsoft website. Use your Googlefu to find out how.
# 2. Run Powershell from the start menu.
# 3. At the PS command prompt type Set-ExecutionPolicy RemoteSigned 
#	4. Save this file as scriptAllDbs.ps1 to root folder where the DatabaseScripts folder will be.
#	5. In scriptAllDbs.ps1 change the $rootDrive variable above to be where the DatabaseScripts Folder will be created.
# 6. Test the script by typing in the powerscript window:
#					H:\scriptAllDbs.ps1
#			"H:\" should be the drive where you saved the scriptAllDbs.ps1 file
# 7. Check that all folders are populated correctly.
#	8. Create a bat file on the same drive named scriptAllDbs.bat
#		  The bat file should contain one line: 
#			powershell -command "& 'H:\scriptAllDbs.ps1' "
#	9. Schedule a windows scheduled job to run every four hours between 6 AM and 6 PM
#
#
################################################################################################



#PLEASE PUT THE DRIVE NAME YOU WANT TO WRITE TO BELOW EXAMPLE "C:"
$rootDrive = "H:"

#the full path of the file that you want to script the stored procs to
$strDate = (get-Date).tostring("yyyyMMddHHssmm")

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

$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
foreach($sqlDatabase in $srv.databases)
{
	$procs = $sqlDatabase.StoredProcedures
	$views = $sqlDatabase.views
	$tables = $sqlDatabase.tables
	$udfs = $sqlDatabase.UserDefinedFunctions
	$sqlDatabaseName = $sqlDatabase.name
	$MyScripter.Server=$srv
	
	
	"************* $sqlDatabaseName"
	
	
	
	

		
	#STORED PROCEDURES
	if($procs -ne $null)
	{
		foreach ($proc in $procs)
		{
			#Assuming that all non-system stored procs have proper naming convention and don't use prefixes like "sp_"
			if ( $proc.Name.IndexOf("sp_") -eq -1 -and $proc.Name.IndexOf("xp_") -eq -1  -and $proc.Name.IndexOf("dt_") -eq -1)
			{
				
				$fileName = $proc.name
				"Scripting SP $fileName"
				$scriptfile = "$rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\StoredProcedures\$filename.sql"
				New-Item $rootDrive\DatabaseScripts -type directory -force | out-null
				New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName -type directory -force | out-null
				New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate -type directory -force | out-null
				New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\StoredProcedures -type directory -force | out-null
				$MyScripter.Options.FileName = $scriptfile
				#AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end
				$MyScripter.Options.AppendToFile = "true"
				$MyScripter.Script($proc)|out-null
			}
		} 
	}
	
	#VIEWS
	if($views -ne $null)
	{
		foreach ($view in $views)
		{
			#Only script views that are properly named
			if ( $view.Name.IndexOf("View") -eq 0)
			{

				
				$fileName = $view.name
				"Scripting View $fileName"
				$scriptfile = "$rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\Views\$fileName.sql"
				New-Item $rootDrive\DatabaseScripts -type directory -force | out-null
				New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName -type directory -force | out-null
				New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate -type directory -force | out-null
				New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\Views -type directory -force | out-null
				$MyScripter.Options.FileName = $scriptfile
				#AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end
				$MyScripter.Options.AppendToFile = "true"
				$MyScripter.Script($view)|out-null
			}
		} 
	}
	
	
		#TABLES
	if($tables -ne $null)
	{
						
				$scriptfile = "$rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\AllTables.sql"
				New-Item $rootDrive\DatabaseScripts -type directory -force | out-null
				New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName -type directory -force | out-null
				New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate -type directory -force | out-null
				$MyScripter.Options.FileName = $scriptfile
				#AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end
				"Scripting out creation script for all tables in $sqlDatabasename"
				$MyScripter.Options.AppendToFile = "true"
				$MyScripter.Script($tables)|out-null
		foreach ($table in $tables)
		{			
				$tableName = $table.name
				#TRIGGERS
				if($table.triggers -ne $null)
				{
					foreach ($trigger in $table.triggers)
					{
						
						$fileName = $trigger.name
						"Scripting trigger $fileName"
						$scriptfile = "$rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\Triggers\$fileName.sql"
						New-Item $rootDrive\DatabaseScripts -type directory -force | out-null
						New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName -type directory -force | out-null
						New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate -type directory -force | out-null
						New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\Triggers -type directory -force | out-null
						$MyScripter.Options.FileName = $scriptfile
						#AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end
						$MyScripter.Options.AppendToFile = "true"
						$MyScripter.Script($trigger)|out-null
					}
				}
				
				
				
		} 
	}
	
	#USER DEFINED FUNCTIONS
	if($udfs -ne $null)
	{
		foreach ($udf in $udfs)
		{
			if ( $udf.Name.IndexOf("dm_") -eq -1 -and $udf.Name.IndexOf("fn_") -eq -1)
				{
					$fileName = $udf.name
					"Scripting UDF $fileName"
					$scriptfile = "$rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\UDFs\$fileName.sql"
					New-Item $rootDrive\DatabaseScripts -type directory -force | out-null
					New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName -type directory -force | out-null
					New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate -type directory -force | out-null
					New-Item $rootDrive\DatabaseScripts\$sqlDatabaseName\$strDate\UDFs -type directory -force | out-null
					$MyScripter.Options.FileName = $scriptfile
					#AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end
					$MyScripter.Options.AppendToFile = "true"
					$MyScripter.Script($udf)|out-null
				}
		}
	} 





}