sqlbackup support RESTORE HEADERONLY to find logical names?

• 
  Nice call Jerry,
  
  automated restores are something that I would love to see integrated into SQLBackup and being able to use these would be a good move towards it.
  
  Jonathan

  fatherjack2 November 20, 2009 06:23

  - 0 +
• 
  by the way, I notice there is no WITH STATS option either to see the progress of the restore?
  
  Thanks

  jerryhung November 20, 2009 15:26

  - 0 +
• 
  This is surprising, I usually get a RedGate reply within 24~48 hours
  
  May I request a reply?
  
  or, who can I contact to ask this question as we have 1-year support, thanks?
  
  
  Thanks in advance

  jerryhung November 23, 2009 15:20

  - 0 +
• 
  Hi Jerry,
  
  The forum is for community support, although we do moderate it, and in fact do end up answering most of the new topics. Since you had a reply to this topic, we did not raise a support incident for it.
  
  If you have a support contract, please contact us directly by phone or email.

  Brian Donahue November 24, 2009 04:57

  - 0 +
• 
  Hi Jerry,
  
  SQL Backup does have the RESTORE FILELISTONLY, RESTORE HEADERONLY and RESTORE SQBHEADERONLY syntax which can be used as normal something like this:
  
  EXECUTE master..sqlbackup '-SQL "RESTORE FILELISTONLY FROM DISK = ''E:\Backup\mybackup.sqb''"'
  
  Info can also be found here at the bottom and here.
  
  If using this as part of a script, you may also want to use the SINGLERESULTSET option so the details returned are easily readable by your script.
  
  I hope that helps.

  Matthew Flatt November 24, 2009 14:14

  - 0 +
• 

  fatherjack wrote:

  Nice call Jerry,
  
  automated restores are something that I would love to see integrated into SQLBackup and being able to use these would be a good move towards it.
  
  Jonathan

  
  The recently released version 6.3 goes some way towards achieving this. The following syntax:
  

  EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM BACKUPHISTORY LATEST_FULL"'
  

  will cause SQL Backup to look in its backup history records and simply restore the latest full backup that it finds. You can also use LATEST_DIFF to restore the latest differential backup, but the state of your database obviously needs to be in a non-recovery/read-only state, and is the correct base backup for the latest differential restore.
  
  Another point to note is that RESTORE ... FROM BACKUPHISTORY only works if you are restoring on the server instance where the backup was created, since that is where the backup history records reside. If you are restoring on another server instance, you can use the following:
  

  EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [g:\backups\AdventureWorks*] LATEST_FULL"'
  

  will cause SQL Backup to find all files matching the AdventureWorks* search pattern in the g:\backups\ folder, determine the latest complete full backup set that's available, and restore it. Again, LATEST_DIFF is also available here.
  
  One last point is that all the other restore options can still be used with the above syntax e.g. MOVE, NORECOVERY, STANDBY etc.
  
  Thanks.

  petey November 25, 2009 22:02

  - 0 +
• 
  Peter, I have only now had a chance to try this out on our test rig and I am consistently getting the same error :
  Error 507: No valid backup sets found from provided folder(s).
  I have tried from a local drive and a network location with no difference. Running the full SQL Backup script with the file name explicitly named works fine from both locations ... Can you give me any further advice please?
  
  [Edit: I'm using v6.3.0.48]
  
  [Edit 2: oddly, this works;]
  EXEC master..sqlbackup '-sql "RESTORE DATABASE [RESTORE_AdventureWorks] FROM BACKUPHISTORY = [adventureworks] LATEST_FULL with recovery ....
  
  
  regards
  
  Jonathan

  fatherjack2 December 22, 2009 06:29

  - 0 +
• 
  What was the command you used, that did not work?
  
  Thanks.

  petey December 22, 2009 12:17

  - 0 +
• 
  This fails - with a remote share

  EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [Restore_Adventureworks] FROM DISK = [\\backup1\backups\sql\rutherford\rutherford_planck\adventureworks\FULL*.sqb]  LATEST_FULL  WITH RECOVERY, MOVE [AdventureWorks_Data] TO [C:\Program Files\Microsoft SQL Server\MSSQL10.PLANCK\MSSQL\Data\Restore_Adventureworks.mdf], MOVE [AdventureWorks_Log] TO [C:\Program Files\Microsoft SQL Server\MSSQL10.PLANCK\MSSQL\Data\Restore_Adventureworks_Log.ldf], ORPHAN_CHECK"'
  

  
  This also fails - with a local file location

  EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [Restore_Adventureworks] FROM DISK = [D:\Test\FULL*.sqb]  LATEST_FULL   WITH RECOVERY, MOVE [AdventureWorks_Data] TO [C:\Program Files\Microsoft SQL Server\MSSQL10.PLANCK\MSSQL\Data\Restore_Adventureworks.mdf], MOVE [AdventureWorks_Log] TO [C:\Program Files\Microsoft SQL Server\MSSQL10.PLANCK\MSSQL\Data\Restore_Adventureworks_Log.ldf], ORPHAN_CHECK"'
  

  
  they both give the same error message to I took that to mean the network share is a valid option... :-/
  
  Both locations only had SQL Backup full backup files for the adventureworks database from the last 4 days.

  fatherjack2 December 23, 2009 04:23

  - 0 +
• 
  Thanks for the details. The problem lies with the database name. Restoring the latest backup sets from folders require the original and current database name to be identical i.e. AdventureWorks. The syntax for restoring from the backup history allows you to specify the original database name, but not when restoring from folders.
  
  This is a known issue (SB-4445), and is under consideration for a fix in a future release.

  petey December 23, 2009 04:30

  - 0 +
• 
  Pete, thanks for the prompt response.
  
  We run trial restores of our backups on a development server and prefix the database names with "RESTORE_" so that we know which they are and can drop them safely once the restore is done.
  
  It looks like I need the database rename part of one format and the 'cross server' ability to get to the network share of the other format!!
  
  Oh well, I'll hang on for the full(er) functionality from the future release.
  
  thanks again

  fatherjack2 December 23, 2009 04:57

  - 0 +

Add comment

Please sign in to leave a comment.