Backup Database Task: How to override the backup file name?

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  August 23, 2011 at 9:33 am

  #242744

  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?

• John Couture

  SSC Enthusiast

  Points: 134

  More actions

  January 3, 2014 at 10:46 am

  #1677381

  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

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 3, 2014 at 11:45 am

  #1677400

  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

• John Couture

  SSC Enthusiast

  Points: 134

  More actions

  January 6, 2014 at 7:27 am

  #1677744

  Excellent. It worked perfectly.

  Thank you very much.

  JC.