Generate T-SQL Scripts for all SQL Server Databases and all Objects using PowerShell


By:   |   Updated: 2016-12-15   |   Comments (12)   |   Related: More > PowerShell

Problem

As a DBA, you must control the objects that exist in the databases for your environment. In an ideal environment, no one would have permissions to modify a table, a stored procedure, or a view, but the reality is that's not the case and changes are made without the DBA always knowing.  Because of this it would be great to have previous versions of code, so you can always check to see if something has changed.  You could restore an old database and compare objects, but having the objects scripted would be easier to access the code.

Solution

Using PowerShell and SQL Server Agent we can create a scheduled job that runs each day and produces scripts for all objects in all databases for an instance of SQL Server and that is what this tip does.

Here is the PowerShell code to generate a script for each object in the database.  The below code will script out table definitions, stored procedures, views, user defined functions and triggers.  This will generate scripts for every database in the SQL Server instance.

You need to supply the SQL Server name and the path where the objects are to be created.

$date_ = (date -f yyyyMMdd)
$ServerName = "." #If you have a named instance, you should put the name. 
$path = "c:\SQL_Server\Backup\Objects\"+"$date_"
 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup. 
$ExcludeSchemas = @("sys","Information_Schema")
$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')

 
$dbs=$serverInstance.Databases #you can change this variable for a query for filter yours databases.
foreach ($db in $dbs)
{
       $dbname = "$db".replace("[","").replace("]","")
       $dbpath = "$path"+ "\"+"$dbname" + "\"
    if ( !(Test-Path $dbpath))
           {$null=new-item -type directory -name "$dbname"-path "$path"}
 
       foreach ($Type in $IncludeTypes)
       {
              $objpath = "$dbpath" + "$Type" + "\"
         if ( !(Test-Path $objpath))
           {$null=new-item -type directory -name "$Type"-path "$dbpath"}
              foreach ($objs in $db.$Type)
              {
                     If ($ExcludeSchemas -notcontains $objs.Schema ) 
                      {
                           $ObjName = "$objs".replace("[","").replace("]","")                  
                           $OutFile = "$objpath" + "$ObjName" + ".sql"
                           $objs.Script($so)+"GO" | out-File $OutFile
                      }
              }
       }     
}

When the PowerShell code is run, it will create the folder and all of the subfolders for the objects like the following. Each folder will contain the objects for that database.

folder structure

In addition, to help keep the file system clean we can create another step to delete older files and folders.

Here is PowerShell script.  You need to specify the limit value of how many days you want to keep and the path which should be the same path as above.

$limit = (Get-Date).AddDays(-7) 
$path = "c:\SQL_Server\Backup\Objects" 

# Delete files older than the $limit.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit -and $_.Name.EndsWith("sql") } | Remove-Item -Force

# Delete any empty directories left behind after deleting the old files.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { $_.PSIsContainer -and (Get-ChildItem -Path $_.FullName -Recurse -Force | Where-Object { !$_.PSIsContainer }) -eq $null } | Remove-Item -Force -Recurse', 

To pull this all together, we can create a SQL Server Agent Job to run each day.

Here is the script to create the SQL Server Agent Job. Remember to update the values to match your environment, like the ServerName, Path, etc.  Also, the below script uses a SQL Agent proxy account named "dba".  This is used to grant file permissions in the file system to generate the scripts. 

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Backup' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Backup'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Maint_BACKUP_DB_OBJECTS', 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=0, 
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'No Description', 
  @category_name=N'Backup', 
  @owner_login_name=N'sa', 
  @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BACKUP_OBJECT]    Script Date: 22/11/2016 15:37:07 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N'BACKUP_OBJECT', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=3, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, 
  @subsystem=N'PowerShell', 
  @command=N'$date_ = (date -f yyyyMMdd)
$ServerName = "." #If you haven4t a default instance, you should put the name. 
$path = "c:\SQL_Server\Backup\Objects\"+"$date_"
 
[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.SMO'')
$serverInstance = New-Object (''Microsoft.SqlServer.Management.Smo.Server'') $ServerName
$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #object you want do backup. 
$ExcludeSchemas = @("sys","Information_Schema")
$so = new-object (''Microsoft.SqlServer.Management.Smo.ScriptingOptions'')

 
$dbs=$serverInstance.Databases #you can change this variable for a query for filter yours databases.
foreach ($db in $dbs)
{
       $dbname = "$db".replace("[","").replace("]","")
       $dbpath = "$path"+ "\"+"$dbname" + "\"
    if ( !(Test-Path $dbpath))
           {$null=new-item -type directory -name "$dbname"-path "$path"}
 
       foreach ($Type in $IncludeTypes)
       {
              $objpath = "$dbpath" + "$Type" + "\"
         if ( !(Test-Path $objpath))
           {$null=new-item -type directory -name "$Type"-path "$dbpath"}
              foreach ($objs in $db.$Type)
              {
                     If ($ExcludeSchemas -notcontains $objs.Schema ) 
                      {
                           $ObjName = "$objs".replace("[","").replace("]","")                  
                           $OutFile = "$objpath" + "$ObjName" + ".sql"
                           $objs.Script($so)+"GO" | out-File $OutFile
                      }
              }
       }     
}', 
  @database_name=N'master', 
  @flags=0, 
  @proxy_name=N'dba'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DELETE]    Script Date: 22/11/2016 15:37:07 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N'DELETE', 
  @step_id=2, 
  @cmdexec_success_code=0, 
  @on_success_action=1, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, 
  @subsystem=N'PowerShell', 
  @command=N'$limit = (Get-Date).AddDays(-7) 
$path = "c:\SQL_Server\Backup\Objects" 

# Delete files older than the $limit.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit -and $_.Name.EndsWith("sql") } | Remove-Item -Force

# Delete any empty directories left behind after deleting the old files.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { $_.PSIsContainer -and (Get-ChildItem -Path $_.FullName -Recurse -Force | Where-Object { !$_.PSIsContainer }) -eq $null } | Remove-Item -Force -Recurse', 
  @database_name=N'master', 
  @flags=0, 
  @proxy_name=N'dba'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email protected], @name=N'EveryDay', 
  @enabled=1, 
  @freq_type=8, 
  @freq_interval=62, 
  @freq_subday_type=1, 
  @freq_subday_interval=0, 
  @freq_relative_interval=0, 
  @freq_recurrence_factor=1, 
  @active_start_date=20160126, 
  @active_end_date=99991231, 
  @active_start_time=210000, 
  @active_end_time=235959, 
  @schedule_uid=N'9ce8b816-3403-4111-9d5d-e8af5c553473'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
Next Steps


Last Updated: 2016-12-15


get scripts

next tip button



About the author
MSSQLTips author Angel Gomez Angel Gomez is the head of SQL Server Database Administration and works with more than 1000 databases and 15TB of data.

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, February 14, 2020 - 11:44:36 AM - Rajeev Chaudhary Back To Top

Very useful script. However, when I ran it, it did not script out constraints and foreign keys. 


Wednesday, October 09, 2019 - 7:48:23 AM - Manny Back To Top

A job step received an error at line 40 in a PowerShell script. The corresponding line is '                           $objs.Script($so)+"GO" | out-File $OutFile'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "Script" with "1" argument(s): "Script failed for StoredProcedure 'dbo.usp_CheckDBIntegrity'. "  '.  Process Exit Code -1.  The step failed.

I get the above when the script is wired into an Agent Job. When run directly via Powershell, it works without incident.

Any ideas?


Monday, July 08, 2019 - 10:33:51 AM - Angel Back To Top

Satya Krovvidi, Do you have offline databases?


Monday, July 08, 2019 - 4:32:46 AM - Satya Krovvidi Back To Top

this script executes for few DB's and after that it through the below error

A job step received an error at line 25 in a PowerShell script. The corresponding line is '             foreach ($objs in $db.$Type)  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The following exception occurred while trying to enumerate the collection: "Unable to cast object of type 'System.DBNull' to type 'System.String'.".  '.  Process Exit Code -1.  The step failed.


Thursday, April 04, 2019 - 9:26:43 AM - Ronald Kraijesteijn Back To Top

Nice script, put it in production :-) 


Monday, January 07, 2019 - 11:44:34 AM - John Back To Top

Hello, Thank you very much for this script I dreamt about such a long time !

It works really well.   

Just one thing, I can't get Triggers, is there a specific option to use ?

Thanks again ;-)


Wednesday, June 20, 2018 - 5:30:26 PM - David Waller Back To Top

 This is a great tool and am testing it out. However, I need to exclude systems databases and a few user DB's that I dont want scripted out. How can I do this?

 


Tuesday, August 22, 2017 - 8:00:32 PM - Mark Kitchens Back To Top

This is really great!

Question: in PowerShell, is there a way to decrypt an encrypted database object?

Thanks,

Mark

 


Friday, June 30, 2017 - 9:54:57 AM - Surendra Back To Top

 Awesome script..

 


Friday, December 16, 2016 - 4:22:43 PM - J.C.Alexandres Back To Top

 Excellent! Thank you!

 

 


Friday, December 16, 2016 - 3:21:07 PM - ANGEL GOMEZ Back To Top

 Hi J.C.Alexandres.

 

I have this job in SQL 2005, 2008R2 and 2012, obviusly in standar and enterprise edition

 

thanks for your comment.

 


Friday, December 16, 2016 - 9:17:08 AM - J.C.Alexandres Back To Top

Thank you very much for sharing these scripts Angel, I am sure a lot of people will appreciate it.

I have a question, for what version (s) of SQL server is this code tested?

 



download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools