August 23, 2011 at 9:33 am
I am creating an SSIS task to Backup all databases on a server and then restore them on a test server. I would like to use the BackupDatabase task but I've noticed that the backup is named with the date concatenated on the end. I want to run an exe SQL task to restore the db from the backup but the date in the name is throwing a monkey wrench into my idea. I tried using the asterisk in the restore tsql but it failed. Any ideas?
January 3, 2014 at 10:46 am
I'm running into the same problem. For now, I'm working around it by using the "Execute T-SQL Statement Task" instead of the "Back Up Database Task".
If you open up the "Back Up Database Task", you should be able to view the T-SQL associated with the task. I copied that T-SQL code to use as my starting point and modified it to remove the date/time stamp appended to the backup names. I then disabled my "Back Up Database Task" and added an "Execute T-SQL Statement Task" to the SSIS package and used my modified T-SQL statements as the T-SQL for the task.
I'm new to using SSIS so I'm sure there's a better solution for this. For now I'll use this until I figure it out.
- JC
January 3, 2014 at 11:45 am
You can run a query on the source server to get the filename:
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'
and bs.type = 'D'
order by
bs.backup_start_date desc
database_name physical_device_name
------------- --------------------------------------------------------------
master \\MyServer\MyShare\master_backup_2014_01_03_041514_5884854.bak
January 6, 2014 at 7:27 am
Excellent. It worked perfectly.
Thank you very much.
JC.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply