Simple way to Automate Generating SQL Stored Procedure Backup Script.

Requirements:

• From SQL Server 2005 version onwards
• Windows Powershell  enabled

Steps:

• Create a SQL Agent job and paste the below code (See in the attached snapshot)

#PLEASE PUT THE DRIVE NAME YOU WANT TO WRITE TO BELOW EXAMPLE "C:"
$rootDrive = "R:\RDAT8\SQL\SCRIPTS"
#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
   }
  }
}
}

• Change the folder location and filter out the system generated stored procedures (Highlited in the snapshot)
• Schedule it to run according to your need.
• The script will create a folder called Databasescripts under  the specified folder and will create each database folder under it followed by the datestamp folder.

Benefits:

• When  the database Backups become huge in size , we don't have space to restore them to  restore the older version of the stored procedures/functions etc. for troubleshooting or rollback purposes.
• We can have the table schema and stored procedure scripted out for quick references and also versioning it.