What are the challenges you face when working across database platforms? Take the survey

Restore Single Log file works - But not "restore ... *.sqb"

RichardBRichardB Posts: 35
edited October 3, 2006 9:55PM in SQL Backup Previous Versions
Having a bit of a mare this morning.

I have 2 databases = call them DB1 and DB2.
I have 3 Log ship restore jobs, every 5 minutes 1 job restores DB1 and another job restores DB2. The logs for DB1 are then moved to a seperate directory and on a daily basis job 3 is supposed (and has been for some months now) to restore those to DB1 and move the files someplace else.

Now job 3 has given up. Running the sql by hand doesn't work either - it just hands there. Copying in the actual name of the next file to restore works fine. The other 2 jobs that run every 5 minutes and restore *.sqb work fine.

Probably clear as mud:

Job 1 - 5min schedule - Restore log db1 from disk = 'x:\dir1\Log_db1_*.sqb' with standby... moveto 'x:\dir2'

Job 2 - 5min schedule - Restore log db2 from disk = 'x:\dir1\Log_db2_*.sqb' with standby... moveto 'x:\dir2'

Job 3 - Daily 0600 Restore log db1 from disk = 'x:\dir2\Log_db1_*.sqb' with standby... moveto 'x:\dir3'

Jobs 1 and 2 fine, job 3 killed after 28 hours, won't function. Run just as code in QA hangs. Run with explicit filename - runs fine. With the _*.sqb this one just does nothing, no files are moved etc etc.

Any help gratefully received :)



  • Options
    Ok got a little more info:

    I copied a bunch of logs to a new directory, ran the script over those, it was fine.

    Copied all the logs to this directory and it failed.

    Altered the mask to _20060930_09*.sqb and its fine (as was 07* and 08*). Altering it to _1* then leads to failure.

    Something is not right here, would appreciate some feedback - it was working fine before!!?!
  • Options
    peteypetey Posts: 2,358 New member
    My guess is that one of the transaction log files is causing an error. Could you pls run the restore using the command line, to determine which file is causing the error, and send me the file? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.