Options

master..sqlbackup takes 5 minutes to fail if files missing!

dylanbeattiedylanbeattie Posts: 5 Bronze 1
edited May 28, 2015 8:42PM in SQL Backup Previous Versions
I'm running the following command, which is one of a whole batch of commands generated by a migration script:
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [mydb] FROM DISK = ''Y:SQL-BACKUPDataFULL_(local)_mydb_*.sqb'' WITH REPLACE, PASSWORD = ''*****'', RECOVERY,  MOVE ''mydb'' TO ''E:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDatamydb.MDF'',  MOVE ''mydb_log'' TO ''E:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDatamydb_log.LDF''"'

In this case, there's no backup file - Y:SQL-BACKUPDataFULL_(local)_mydb_*.sqb doesn't exist.

The problem is - the job takes five minutes to fail, which makes it quite time-consuming to debug restore batches! Is there some way I can specify a more aggressive timeout? I'm restoring files from local SSD storage so not concerned about network latency or anything.

Thanks,

-D-
· Share on Twitter

Comments

  • Options
    KelvinSmithKelvinSmith Posts: 1
    Did you try performing a Native Full and Differential backup?
    Load Balanced Web Servers
    · Share on Twitter
  • Options
    peteypetey Posts: 2,358 New member
    By default, SQL Backup makes 10 attempts in intervals of 30 seconds to locate the file. You can adjust these two values by using the DISKRETRYCOUNT and DISKRETRYINTERVAL options. To turn this off, use a DISKRETRYCOUNT value of 0 e.g.
    EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [mydb] FROM DISK = ''Y:SQL-BACKUPDataFULL_(local)_mydb_*.sqb'' WITH REPLACE, PASSWORD = ''*****'', RECOVERY,  MOVE ''mydb'' TO ''E:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDatamydb.MDF'',  MOVE ''mydb_log'' TO ''E:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDatamydb_log.LDF'', DISKRETRYCOUNT = 0"'
    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.