RESTORE ... STOPAT

Nicolas

Hello,

the most common syntax we often need to use for restoring would be (something like):

SQLBackupC -SQL "RESTORE DATABASE Sales_Prod FROM DISK = 'C:\Backups\Sales*.sqb' LATEST_ALL WITH RECOVERY STOPAT='2010-08-26 11:50:00.000' "

Instead of the more cumbersome

SQLBackupC - SQL RESTORE DATABASE ... Full backup WITH NORECOVERY
SQLBackupC - SQL RESTORE LOG ... Log backup 1 WITH NORECOVERY
SQLBackupC - SQL RESTORE LOG ... Log backup 2 WITH NORECOVERY
SQLBackupC - SQL RESTORE LOG ... Log backup 3 WITH NORECOVERY
SQLBackupC - SQL RESTORE LOG ... Log backup 4 WITH NORECOVERY
SQLBackupC - SQL RESTORE LOG ... Log backup 5 WITH RECOVERY STOPAT='2010-08-26 11:50:00.000'

Which requires us to dig up the exact files and their names for the required STOPAT time.

Am I correct in saying that the first syntax is not supported (yet)?

Regards, Nick

chriskelly

In principle your first script can be used. However you need something like this:

EXECUTE master..sqlbackup -SQL "RESTORE DATABASE Sales_Prod FROM DISK = 'C:\Backups\Sales*.sqb' LATEST_ALL WITH RECOVERY STOPAT='2010-08-26 11:50:00.000' "

petey

Sorry, that syntax isn't yet supported.

Workaround would be in two steps:

1. restore latest full e.g.

SQLBackupC -SQL "RESTORE DATABASE Sales_Prod FROM DISK = 'C:\Backups\Sales*.sqb' LATEST_FULL WITH NORECOVERY"

2. restore logs with STOPAT e.g.

SQLBackupC -SQL "RESTORE LOG Sales_Prod FROM DISK = 'C:\Backups\<log file name pattern>' WITH RECOVERY, STOPAT = '2010-08-26 11:50:00.000' "

Note that in step 2, the file name pattern must only pick up the transaction log backup files. It must not pick up files of other backup types.

Nicolas

Thanks for confirming Peter.
And yes, of course I was missing the wildcard possibility for step 2.

Nick

willgart

LATEST_FULL or LATEST_ALL doesn't works for me.

I'm trying to restore on a different server and red gate says:
Error 507: No valid backup sets found from provided folder(s).

here the command:

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [dbname] FROM DISK = [e:\bkp\*.sqb] LATEST_ALL WITH RECOVERY, REPLACE, DISCONNECT_EXISTING"'

I have updated the binaries files to the .1001 version
and I continue to have the same error.

Apparently this works if the backup was made on the same server.
Any idea why?

petey

There was a bug that was fixed in patch 1015, which you can download from here:

ftp://support.red-gate.com/Patches/sql_ ... 0_1015.zip

Also, is the database name you are restoring to the same as the original database name?

Thanks.

willgart

Yes, I try using the same DB name, but doesn't works.
and I install the latest release (1016), but doesn't work too

with the version 1015 , the SQL Agent service failed to start.

petey

In the folder 'e:\bkp\*.sqb', do you at least have a full database backup set in there? The LATEST_ALL option needs a full database backup to start off with, and then it'll pick up any differential and transaction log backups when deemed necessary.

Thanks.

willgart

forsure, I have this:
FULL_(local)_MyBD_20100831_200000.sqb
LOG_MyDB_20100831220000.sqb
...
more than 20 backup Log files

Doing an explicit restore of the FULL_(local)_MyBD_20100831_200000.sqb file, and then doing a restore log e:\ods\log*.sqb
works fine, all the log files are processed.

I have created a script using the SQBHeaderOnly command (which returns all the file name matching my criteria) to identify the name of the latest full backup file, and then my script use this file name to do the restore as describe just above.
This produce the same result as the LATEST_FULL option.
Little complicated, But this works.

petey

Could you please send me the headers of the full backup file and some of the subsequent transaction log backup files? Basically, I need the first 1024 bytes from each of those files. You can use the SQL Backup Test utility:

ftp://support.red-gate.com/Patches/sql_backup/SBaTU.zip

select the 'Tools\Extract file header' menu item, and extract the first 1024 bytes from each of the above files. Please send the header files to peter.yeoh@red-gate.com.

Thanks.

willgart

oohh...
I have just discover a way to simplify my script...

Because the BKP folder contains ONLY 1 full backup and a lot of log backups, I can use this statement:
RESTORE DATABASE MyDB FROM DISK = ''e:\bkp\FULL*.sqb'' WITH NORECOVERY, REPLACE, DISCONNECT_EXISTING

the FULL*.SQB returns only 1 file and so the full backup command doesn't required an explicit full file name.

petey

Don't you just love it when you use sensible naming conventions

I'll still like to get to the bottom of why the LATEST_ALL option isn't working for you. If you can send me the headers for the files, I'll take a look at it first thing tomorrow. It's already 1:30 a.m where I am, and I can barely keep my eyes open :shock:

Thanks.

petey

RESTORE_LATEST did not work for willgart because SQL Backup 6 failed to recognise version 5 files for the RESTORE_LATEST option. This has now been fixed in patch 1017. If anybody else if affected by this i.e. you want to use the RESTORE_LATEST option on SQL Backup 5 and earlier files, you can download the patch from here

ftp://support.red-gate.com/Patches/sql_ ... 0_1017.zip

willgart, thanks for helping us troubleshoot the issue and test the patch.