sqlbackup support RESTORE HEADERONLY to find logical names?

jerryhungjerryhung Posts: 138
edited December 23, 2009 4:57AM in SQL Backup Previous Versions
I am modifying my auto-restore scripts to use with SQL Backup extended procedures

I currently use a procedure [usp_RestoreFromAllFilesInDirectory] to restore all backup files under a directory

I use RESTORE WITH FILELISTONLY and RESTORE HEADERONLY to find the logical name and etc

this way, I can restore ALL files inside a folder (BAK/DIFF/TLOG) without having to worry about their Logical Names or File names

Is there such option for the sqlbackup procedure? 
EXECUTE master..sqlbackup '-SQL 
"RESTORE DATABASE [db] FROM DISK = ''"E:\Backup\db_production.sqb'' 
WITH NORECOVERY
, MOVE ''data'' TO ''E:\MSSQL\DATA\db.mdf''
, MOVE ''log'' TO ''E:\MSSQL\LOG\db.ldf''"'
Jerry Hung
DBA, MCITP
· Share on Twitter

Comments

  • fatherjack2fatherjack2 Posts: 311
    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
    Senior DBA
    Careers South West Ltd
    · Share on Twitter
  • jerryhungjerryhung Posts: 138
    by the way, I notice there is no WITH STATS option either to see the progress of the restore?

    Thanks
    Jerry Hung
    DBA, MCITP
    · Share on Twitter
  • jerryhungjerryhung Posts: 138
    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
    Jerry Hung
    DBA, MCITP
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    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
    Redgate Foundry
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • fatherjack2fatherjack2 Posts: 311
    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
    Senior DBA
    Careers South West Ltd
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    What was the command you used, that did not work?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • fatherjack2fatherjack2 Posts: 311
    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.
    Senior DBA
    Careers South West Ltd
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • fatherjack2fatherjack2 Posts: 311
    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
    Senior DBA
    Careers South West Ltd
    · Share on Twitter
Sign In or Register to comment.