Backup Database Task (Maintenance Plan) file name question

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  June 9, 2010 at 7:09 am

  #233619

  I recently upgraded to SQL 2008 from SQL 2005 on my backup production machine (primarily used for testing). I use a maintenance plan to create my backups. Under 2005 the file name that was created was the database name with "_backup_datetime.bak" appended. For example:

  TestDB_backup_201005260400.bak

  After the upgrade I'm getting filenames similiar to the following:

  TestDB_backup_2010_06_09_040006_8466528.bak

  As you can see there is a considerable difference in the file names. Does anyone know how to get it to use the old file name?

  The reason (if you curios) is I use scripts the move and restore the database from production to the test machine and need to know the actual file name to execute the restore operation. While I'm sure I can write a script to determine the file name from the OS it was much easier when I KNEW what the file name would be since the DB backed up every day at the same time (we are not talking about huge databases here, so 99% of the time I knew what the file name, down to the minute, would be).

  Thanks,

  James.

• -=JLK=-

  SSCertifiable

  Points: 5315

  More actions

  June 17, 2010 at 7:47 am

  #1181563

  I found the solution in another post: http://qa.sqlservercentral.com/Forums/Topic506723-357-1.aspx

  Here is my implementation -

  declare @FileName_ varchar(100)

  declare @RestoreFile_ varchar(200)

  declare @Path_ varchar(1000)

  Set @Path_ = 'D:\Backups\DB_Backups\Dev15\Production'

  declare @t_ table (subdirectory_ varchar(500),depth_ int,file_ int) --columns match output from xp_dirtree

  insert into @t_ EXECUTE xp_dirtree @Path_, 1, 1 --NOTE: xp_dirtree is an undocumented proc

  --File_ = 1 (true) = 0 (false), 0 = non-file like a directory name

  delete from @t_ where depth_ <> 1 or file_ <> 1 or subdirectory_ not like 'mydbname_backup%'

  select @filename_ = max(subdirectory_) from @t_

  print 'Backup File Name = ' + @filename_

  set @RestoreFile_ = '' + @Path_ + '\' + @FileName_ + ''

  RESTORE FILELISTONLY FROM

  DISK= @RestoreFile_;

  ALTER DATABASE mydbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  RESTORE DATABASE mydbname

  FROM DISK = @RestoreFile_

  WITH REPLACE,

  MOVE 'mydbname_Dat01' TO 'D:\SQLData\mydbname_Dat01.mdf',

  MOVE 'mydbname_Log01' TO 'D:\SQLData\mydbname_Log01.ldf',

  MOVE 'ftrow_mydbname_FTCatalog_' TO 'D:\SQLData\FullTextCatalogs\mydbname_FTCatalog_',

  RECOVERY;

  --EXEC sp_dboption 'DISAM_Internal', 'read only', 'true'

  ALTER DATABASE mydbname SET RECOVERY SIMPLE, MULTI_USER WITH ROLLBACK IMMEDIATE