SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Powershell SMO - LastBackupDate without Copy_only Backup


Powershell SMO - LastBackupDate without Copy_only Backup

Author
Message
Satheesh E.P.
Satheesh E.P.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1381 Visits: 4095
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.
Satheesh E.P.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1381 Visits: 4095
For now, I had achieved the same by using ExecuteWithResults().

---------------------------------------------------
Thanks,
Satheesh.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)

Group: General Forum Members
Points: 546472 Visits: 44668
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ALZDBA
ALZDBA
SSC Guru
SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)

Group: General Forum Members
Points: 76191 Visits: 9258
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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Satheesh E.P.
Satheesh E.P.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1381 Visits: 4095
@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
Jeff Moden
SSC Guru
SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)SSC Guru (546K reputation)

Group: General Forum Members
Points: 546472 Visits: 44668
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search