locked
PowerShell Script to Get the Objects schema Backup RRS feed

  • Question

  • Hi All,

    I am using below power shell script to get the all the Objects Schema backup. It is working fine to get all the objects. Now we need to filter the objects based on object create date. which is not working .Could any one please help on this.

    "

     foreach ($objs in $db.$Type | Where-object {-not $_.IsSystemObject -and $_.create_date -gt $date_ })

    "

    In the above  $_.create_date -gt $date_  condition is not working . Below is the total powershell script.

    # this will filter systme databses and system tables

    $date_ = (date -f yyyyMMdd)
    $currentDate = Get-Date
    $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 | 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 $_.create_date -gt $date_ })
                  {
                   
                         If ($ExcludeSchemas -notcontains $objs.Schema ) 
                          {
                               $ObjName = "$objs".replace("[","").replace("]","")                  
                               $OutFile = "$objpath" + "$ObjName" + ".sql"
                               $objs.Script($so)+"GO" | out-File $OutFile
                          }
                  }
           }     
    }
    




    Thanks and Regards Rajesh


    • Edited by Rajesh.Ch Tuesday, November 12, 2019 6:17 AM Followed standard process
    Monday, November 11, 2019 10:48 AM

Answers

  • Hi,

    Thanks It is working with the below code.

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

    Hi Jrv,

    Sorry, actually i am new to the Poweshell and i got this code from the below link and trying to enhance based on the requirement. I have changed the original post as well

    https://www.red-gate.com/simple-talk/blogs/powershell-script-all-objects-on-all-databases-to-files/ 

    Thanks and Regards Rajesh

    • Marked as answer by Rajesh.Ch Tuesday, November 12, 2019 6:19 AM
    Tuesday, November 12, 2019 6:19 AM

All replies

  • The below commands return a string instead of a datetime object.

    $date_ = (date -f yyyyMMdd)

    You are comparing a string object, but you require a datetime, donot format the output of (get-date) and explicitly convert the other one if it is already not in the datetime format, try below if might work:

    $date_ = (date)
    # OR $date_ = (Get-Date)
    
    # And while comparing try below 
    foreach ($objs in $db.$Type | Where-object {!($_.IsSystemObject) -and [datetime]$_.create_date -gt $date_ })
    {
    
    #......... SOME CODE
    
    }


    • Edited by DumbleD0re Monday, November 11, 2019 11:16 AM
    Monday, November 11, 2019 11:15 AM
  • You cannot compare string dates. Strings can never be compared as dates.

    The following line is not PowerShell:

    $date_ = (date -f yyyyMMdd)

    Please post code correctly

    Edit and fix your original post.

    Ask the original author of this script for help.

    Before trying to edit PS code you should take the time to learn PowerShell.

    The script you have posted could never have worked because there ae too many logic and syntax errors.  Either it was written by someone who did not know PowerShell or a script was edited by someone who did not know PowerShell.


    \_(ツ)_/


    • Edited by jrv Monday, November 11, 2019 11:20 AM
    Monday, November 11, 2019 11:17 AM
  • Hi,

    Thanks . But we are not getting value for the $_.create_Date. We have to get this value from SQL DB like object created date. DO you have any idea how can we get the object created date from sql.


    Thanks and Regards Rajesh

    Monday, November 11, 2019 12:02 PM
  • Hi,

    Thanks . But we are not getting value for the $_.create_Date. We have to get this value from SQL DB like object created date. DO you have any idea how can we get the object created date from sql.


    Thanks and Regards Rajesh

    Have you looked at the properties of the object? A DB object has a a "CreateDate" which is a datetine object.

    $serverInstance.Databases[0].CreateDate


    \_(ツ)_/

    Monday, November 11, 2019 12:40 PM
  • Before pursuing this please fix your original post and post the code correctly. It is unreadable and cannot be copied with the HTML causing the copy to be corrupted. That is why we have the code posting tool


    \_(ツ)_/

    Monday, November 11, 2019 12:41 PM
  • Hi,

    Thanks It is working with the below code.

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

    Hi Jrv,

    Sorry, actually i am new to the Poweshell and i got this code from the below link and trying to enhance based on the requirement. I have changed the original post as well

    https://www.red-gate.com/simple-talk/blogs/powershell-script-all-objects-on-all-databases-to-files/ 

    Thanks and Regards Rajesh

    • Marked as answer by Rajesh.Ch Tuesday, November 12, 2019 6:19 AM
    Tuesday, November 12, 2019 6:19 AM
  • Not necessary to do al of that.

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

    Assuming date is:

    $date_ = [datetime]::Today


    \_(ツ)_/


    • Edited by jrv Tuesday, November 12, 2019 6:28 AM
    Tuesday, November 12, 2019 6:25 AM