sqlbackup support RESTORE HEADERONLY to find logical names?

jerryhung

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''"'

fatherjack2

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

jerryhung

by the way, I notice there is no WITH STATS option either to see the progress of the restore?

Thanks

jerryhung

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

Brian Donahue

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.

Matthew Flatt

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.

petey

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.

fatherjack2

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

petey

What was the command you used, that did not work?

Thanks.

fatherjack2

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.

petey

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.

fatherjack2

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