backup database task in SSIS

Question

I am new to SSIS ,and I am trying to make a package that automatically creates a backup of the Prod server and restores it on Dev server. For that I am using a backup database task in control flow . I am trying to store the name of the backup file that is created into a variable and then pass the same to the restore task . But I am not able to understand the format in which this filename is created . It is like \Configurations_backup_2015_07_29_133104_1546397.bak'.

Can anyone help me with how to fetch this name and pass it directly to restore task. Thanks

Answer 1

Try executing below query to get the backup name created by the task. The same can be stored into a variable and passed on for restore.

select top 1
    bs.database_name,
    bmf.physical_device_name
from
    msdb.dbo.backupset as bs
    inner join
    msdb.dbo.backupmediafamily as bmf
    on bmf.media_set_id = bs.media_set_id
where
    bs.database_name = 'master' --mention your database name
    and bs.type = 'D'
order by
    bs.backup_start_date desc