Hi, we've been fighting an odd backup issue for more than a year now.
Some (quite big) courses don't backup properly. The backup just hangs, always in the last file annotations step. The courses that cause problem don't just have big files, rather they have lots of questions or scorm objects with many, many files.
I'd posted about this before here http://moodle.org/mod/forum/discuss.php?d=193694#p843670
My most recent troubleshooting attempts lead me to force the backup and restore process to use a normal table, not a temp table, so that I could see better what was happening and why my query wasn't returning. When I did this, the backup worked.
My latest hypothesis is that because mssql's temp tables are tied to database sessions, if the process needs to reconnect to the db at some point, it will lose access to the temp tables it had created. Don't know if this is a sensible hypothesis.
The issue of course with using non-temp tables is that because the backup/restore deletes and recreates the tables as it runs, only one backup/restore can happen at a time, or they'll stomp over each other. Still, it allows our automated backups to work again, and we're getting some courses bigger than 1Gb getting backed up ok.
Can anyone offer any other clues, or info I should check into before posting this in the tracker? It still feels vague and wooly to me.