I'm trying to restore on a SQL Server instance a backup that exists on a different network share (i.e., \\server\share\mybackup.bak). When I run my restore command, SQL Server just hangs. I found that there was a "backupthread" wait. According to Denny Cherry, this could be completely normal (https://itknowledgeexchange.techtarget.com/sql-server/what-is-the-backupthread-wait-type/), but since I don't usually do a backups directly from network shares, I'm not sure how long this could/should take. Does anyone have experience with this situation?
And to give you an idea of my goal--currently, I restore production backups onto a separate server daily. The way that I do this is that I move the backup file itself to the destination server (via scheduled task), then restore locally. I was hoping to reduce the number of steps by having the destination server read directly from where the backups are taken, alleviating the need to do the file move--in hopes to save time and resources...but who knows, this restore from the network share could end up being longer than the copy+restore locally method...
And my ultimate ultimate goal was to use PowerShell with dbatools to automate it all, but when I try to do the restore with that, it also hangs, for a similar reason I believe. PREEMPTIVE_OS_GETPROCADDRESS when running xp_fileexist against the share as it looks for the .bak
Would love some feedback if you have any.
Thanks in advance,
Mike Scalise, PMP