Script New Objects for User Databases Using Powershell


Run into a question how to script newly created objects of user databases with a datetime filter,

The key part is the following:

  

$limit  = (Get-Date).AddDays(-7)  # past 7 days

#....
foreach ($objs in $db.$Type | Where-object {-not $_.IsSystemObject -and ( ([datetime]$_.createdate).Date -ge $limit  ) })

 

 

The whole code section:

  

$date_ = (date -f yyyyMMdd)
$currentDate = Get-Date
$ServerName = "MC\SQLDEV2017Instance"  
$path = "c:\temp\"+"$date_"

$limit  = (Get-Date).AddDays(-7) 

        #foreach ($objs in $db.$Type | Where-object {-not $_.IsSystemObject -and ( ([datetime]$_.createdate).Date -gt $date_ ) } )
			  # foreach ($objs in $db.$Type | Where-object {-not $_.IsSystemObject -and ($_.createdate | Where-object {$_.Date -ge $date_ } )
#[datetime]::parseexact($date_ , 'yyyyMMdd', $null)

[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 | Where-Object {!($_.Name -in ("master","model","msdb","ReportServer","ReportServerTempDB","tempdb"))}  #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 | where {!($_.IsSystemObject)})
               foreach ($objs in $db.$Type | Where-object {-not $_.IsSystemObject -and ( ([datetime]$_.createdate).Date -ge $limit  ) })
              {

                     If ($ExcludeSchemas -notcontains $objs.Schema ) 
                      {
                           $ObjName = "$objs".replace("[","").replace("]","")                  
                           $OutFile = "$objpath" + "$ObjName" + ".sql"
                           $objs.Script($so)+"GO" | out-File $OutFile
                      }
              }
       }     
}

 

The original question:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a6912dd-f577-4589-bc60-cc687cbb4afe/powershell-script-to-get-the-objects-schema-backup?forum=transactsql

Advertisement

One Comment on “Script New Objects for User Databases Using Powershell”

  1. […] Jingyang Li – Script New Objects for User Databases Using Powershell […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s