RESTORE ... STOPAT

NicolasNicolas Posts: 10
edited September 1, 2010 9:41PM in SQL Backup Previous Versions
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
· Share on Twitter

Comments

  • chriskellychriskelly Posts: 378
    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' "
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • NicolasNicolas Posts: 10
    Thanks for confirming Peter.
    And yes, of course I was missing the wildcard possibility for step 2.

    Nick
    · Share on Twitter
  • willgartwillgart Posts: 8
    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?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • willgartwillgart Posts: 8
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • willgartwillgart Posts: 8
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • willgartwillgart Posts: 8
    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.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Don't you just love it when you use sensible naming conventions :wink:

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