Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (3)   |   Related Tips: 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 @job_id=@jobId, @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 @job_id=@jobId, @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 @job_id=@jobId, @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 Update:






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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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?

 


Learn more about SQL Server tools