Powershell SMO - LastBackupDate without Copy_only Backup

  • Satheesh E.P.

    SSCrazy

    Points: 2509

    I am trying to automate restore process of a database (Backup is taken through different process).  I need to pick the last full backup and latest differential backup and restore the database.
    "[Database Name].LastBackupDate" is giving me the date and time of last full backup taken but it is considering the full backup taken with copy_only parameter also.
    Is there a way, I can get the LastBackupDate of backup taken without copy_only parameter on a database through PowerShell SMO?

    P.S:  I am trying to write the script only with PowerShell SMO and not to use SQLPS.

    ---------------------------------------------------
    Thanks,
    Satheesh.

  • Satheesh E.P.

    SSCrazy

    Points: 2509

    For now, I had achieved the same by using ExecuteWithResults().

    ---------------------------------------------------
    Thanks,
    Satheesh.

  • Jeff Moden

    SSC Guru

    Points: 993883

    I guess I don't understand why you're using PowerShell to do this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Johan Bijnens

    SSC Guru

    Points: 134254

    That info is to be found in msdb!

    /*
    Backup overview for current database
    */

    Declare @BU_DbName sysname
    Set @BU_DbName = db_name()

    select --@RefDtFullBU as RefDtFullBU
         BU.server_name
         , BU.machine_name
         , BU.database_name
         , BU.name as BUName
         , BU.backup_start_date
         , BU.backup_finish_date
         , BU.first_lsn
         , BU.last_lsn
         , BU.database_backup_lsn
         , BU.[TYPE]
         , case BU.[TYPE]
          when 'D' then 'Full'
          when 'I' then 'Diff'
          when 'L' then 'Log'
          when 'F' then 'File or filegroup'
          when 'G' then 'Diff file'
          when 'P' then 'Partial'
          when 'Q' then 'Diff partial'
          else '???'
          end as BuType
              , BU.is_copy_only
         , CAST(BU.backup_size / 1024.0 / 1024 as decimal(18, 3)) as backup_size_MB
      /* SQL2008 added compressed_backup_size */
      , CAST(BU.compressed_backup_size / 1024 / 1024 as decimal(18, 3)) as COMPRESSED_BU_size_MB
         , BU.position
         , BU.[description]
      /* SQL2005 added BU.recovery_model */
      , BU.recovery_model
         , BU.[user_name]
    , BU.expiration_date
         , BMF.physical_device_name
    /* db-file details excluded
         , BF.logical_name
         , BF.file_type
         , BF.file_number
         , BF.physical_name
    */

       from  msdb.dbo.backupset BU
    /* db-file details excluded
       inner join msdb.dbo.backupfile BF
          on BF.backup_set_id = BU.backup_set_id
    */
       inner join msdb.dbo.backupmediaset BS
          on BS.media_set_id = BU.media_set_id
       inner join msdb.dbo.backupmediafamily BMF
          on BMF.media_set_id = BU.media_set_id
       inner join (
           select @@ServerName as Server_Name
             , D.name as DbName
             , min(BU.backup_start_date) as First_backup_start_date
             , min(BU.backup_finish_date) as First_backup_finish_date
             , max(BU.backup_start_date) as Last_backup_start_date
             , max(BU.backup_finish_date) as Last_backup_finish_date
           from master.dbo.sysdatabases D
           left join msdb.dbo.backupset BU
              on D.[Name] = BU.database_name
               and BU.[type] = 'D' -- D = FullDatabasebackup
           --where BU.backup_start_date >= DATEADD(dd, datediff(dd, 0, @RefDtFullBU), 0)
           group by D.name
                --order by D.name
           
           ) LastFullBU
          On LastFullBU.Server_Name = BU.server_name
           and LastFullBU.DbName = BU.database_name
       where BU.database_name = @BU_DbName
          and BU.backup_start_date >= dateadd(mi, -15, LastFullBU.First_backup_start_date)
    --   and BU.backup_start_date < dateadd(mi, 1, LastFullBU.First_backup_start_date)
       order by case when BU.[TYPE] in ( 'D', 'I', 'L', 'F', 'G', 'P', 'Q' ) then 1
            else 0
          end
         , BU.database_name
         , BU.backup_start_date desc
         , BU.backup_finish_date desc
         , BUName ;

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Satheesh E.P.

    SSCrazy

    Points: 2509

    @Jeff Moden

    I need to restore 7 databases from 5 different production servers and restore it into a development server. I am using Powershell to get last full backup and latest differential backup location from all production server and then move it to a single location in BLOB storage and restore it from there. My development server is Azure VM but production is both Azure and On-premises.

    @alzdba

    Many thanks for your script. Since I need only the last full backup location, I had written a simple script to get that detail.

    ---------------------------------------------------
    Thanks,
    Satheesh.

  • Jeff Moden

    SSC Guru

    Points: 993883

    Satheesh E.P. - Saturday, September 9, 2017 8:07 AM

    @Jeff Moden

    I need to restore 7 databases from 5 different production servers and restore it into a development server. I am using Powershell to get last full backup and latest differential backup location from all production server and then move it to a single location in BLOB storage and restore it from there. My development server is Azure VM but production is both Azure and On-premises.

    @alzdba

    Many thanks for your script. Since I need only the last full backup location, I had written a simple script to get that detail.

    Sounds interesting.  Any chance of you posting the "de-sensitized" version of your PowerShell routine for this?  It would likely help quite a few folks that need to do something similar especially since some of your DBs are on Azure.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply