August 28, 2017 at 2:32 am
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.
September 7, 2017 at 11:21 am
For now, I had achieved the same by using ExecuteWithResults().
---------------------------------------------------
Thanks,
Satheesh.
September 7, 2017 at 8:28 pm
I guess I don't understand why you're using PowerShell to do this.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2017 at 2:56 am
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
Learn to play, play to learn !
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 but most of the time this is me
September 9, 2017 at 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.
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.
September 9, 2017 at 9:40 am
Satheesh E.P. - Saturday, September 9, 2017 8:07 AM@jeff ModenI 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.
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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply