RESTORE ... STOPAT

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

  chriskelly August 27, 2010 10:49

  - 0 +
• 
  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.

  petey August 30, 2010 02:40

  - 0 +
• 
  Thanks for confirming Peter.
  And yes, of course I was missing the wildcard possibility for step 2.
  
  Nick

  Nicolas August 30, 2010 04:23

  - 0 +
• 
  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?

  willgart September 01, 2010 11:51

  - 0 +
• 
  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.

  petey September 01, 2010 11:57

  - 0 +
• 
  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.

  willgart September 01, 2010 12:35

  - 0 +
• 
  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.

  petey September 01, 2010 12:57

  - 0 +
• 
  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.

  willgart September 01, 2010 13:04

  - 0 +
• 
  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.

  petey September 01, 2010 13:14

  - 0 +
• 
  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.

  willgart September 01, 2010 13:18

  - 0 +
• 
  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 September 01, 2010 13:27

  - 0 +
• 
  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.

  petey September 01, 2010 21:41

  - 0 +

Add comment

Please sign in to leave a comment.