October 18, 2018 at 7:35 am
Hi,
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
Mike Scalise, PMP
https://www.michaelscalise.com
October 18, 2018 at 2:43 pm
You will see "backupthread" waits during restores and backups. It all depends on your database size and if you are using compression.
You can check the progress of the backup/restore using:
SELECT session_id AS SPID, command, a.text AS Query, start_time, percent_complete,
DATEADD(SECOND, estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN ('BACKUP DATABASE', 'RESTORE DATABASE');
Also you can setup log shipping with standby\read-only.
October 19, 2018 at 7:33 am
AlexSQLForums - Thursday, October 18, 2018 2:43 PMYou will see "backupthread" waits during restores and backups. It all depends on your database size and if you are using compression.You can check the progress of the backup/restore using:
SELECT session_id AS SPID, command, a.text AS Query, start_time, percent_complete,
DATEADD(SECOND, estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN ('BACKUP DATABASE', 'RESTORE DATABASE');Also you can setup log shipping with standby\read-only.
Thanks, Alex.
First, my database size is about 70gb. I'm not using backup compression yet, though that is the plan.
Here's my concern...
I have a query similar to the one you provided. Yours actually returns 0 rows during the restore and it's because you're not looking for "RESTORE HEADERONLY" in the IN operator. Ok, so if i run this:
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE', 'RESTORE HEADERONLY')
I get one row. The percent complete never goes above 0.00. I don't get an ETA Min or ETA hours. All 0.00. All that's moving in the result set is the elapsed time (Elapsed Min). I know 70GB isn't nothing, but it's not terabytes either. I feel like something can't be right here...like it's not actually even starting the restore process--maybe because it can't actually read the file. But no error--just chugging along.....
I'd be interested to hear if you think differently and if there's anything else I might be able to try.
Again, thank you.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
October 22, 2018 at 2:48 pm
Mike Scalise - Friday, October 19, 2018 7:33 AMAlexSQLForums - Thursday, October 18, 2018 2:43 PMYou will see "backupthread" waits during restores and backups. It all depends on your database size and if you are using compression.You can check the progress of the backup/restore using:
SELECT session_id AS SPID, command, a.text AS Query, start_time, percent_complete,
DATEADD(SECOND, estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN ('BACKUP DATABASE', 'RESTORE DATABASE');Also you can setup log shipping with standby\read-only.
Thanks, Alex.
First, my database size is about 70gb. I'm not using backup compression yet, though that is the plan.
Here's my concern...
I have a query similar to the one you provided. Yours actually returns 0 rows during the restore and it's because you're not looking for "RESTORE HEADERONLY" in the IN operator. Ok, so if i run this:
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE', 'RESTORE HEADERONLY')I get one row. The percent complete never goes above 0.00. I don't get an ETA Min or ETA hours. All 0.00. All that's moving in the result set is the elapsed time (Elapsed Min). I know 70GB isn't nothing, but it's not terabytes either. I feel like something can't be right here...like it's not actually even starting the restore process--maybe because it can't actually read the file. But no error--just chugging along.....
I'd be interested to hear if you think differently and if there's anything else I might be able to try.
Again, thank you.
Mike
Hey Mike -
I'd guess that something is not quite right. I just thought of this (since I just posted something related) but you may be interested. Not many are actually interested but I really think one of the best ways to see what's going on with something like this is to try that restore using trace flag 3004
It's actually kind of cool and something you might like - it just does verbose logging for the restore so you can see the details and what steps it's stuck on. And on some of the obscure processes, I've usually been able to query on those and find a blog or two on msdn. Check this blog for more info (there are some other MS blogs using it if you want to search on it):
How It Works: What is Restore/Backup Doing?
Sue
October 22, 2018 at 7:37 pm
Sometime ago we had an issue with DB restore in a new server, though it was working fine in old server.
That was problem with "Instant File Initialization", adding SQL service account to local Administrator group resolved the issue for us.
The issue we faced was kind of same, it wouldn't just show any progress and stand still "0" forever.
In your case, hope as it is the same server and backup was working just fine earlier when done from the local server itself.
Otherwise try the option I mentioned above.
Also, what about service account permissions of the destination server on the source server ?.
October 24, 2018 at 12:39 pm
These are great suggestions. Thank you! I'll be looking into them in the coming days. Hopefully one (or I suppose a combination of both) will help me see what's going on here.
As always, thanks!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply