question

techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 asked ·

Run DB restore and backup at same time?

Hello, say I have the following scenario:

1 - Perform a long running restore of a large DB (from a backup of another SQL Server as source) with brand new name (this DB doesn't yet exist on target SQL Server) i.e. starts at 9pm takes two hours to complete restore

2 - We have a scheduled nightly SQL Agent DB backup job starts at 10pm on this target server that the long restore is running so both restore and backup processes would overlap

Could this be potentially dangerous and cause DB corruption or any other things I should be concerned/aware of?

Thanks in advance.

sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

No, the operation as such cannot cause corruption. What could happen is that you overtax the I/O subsystem. But as long as the I/O subsystem is safe and sound it should not go clobbering any disks. However, you may have to accept that both operations are taking longer time than if they had not overlapped.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered ·

Hi @techresearch7777777-7743,

No, there will not cause corruption. But it will take a long time if you run backup and restore at the same time. Because backup and restore operations are I/O intensive, and backup and restore throughput depends on how well the underlying I/O subsystem is optimized to handle the I/O volume.
When the backup operation is finished, you can use below T-SQL to verify the backup from disk:

 RESTORE VERIFYONLY FROM DISK = 'D:\AdventureWorks.bak';
 GO

Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ·

See Concurrent Administrative Operations => Backup vs File operations (like restore), they can not run at the same time.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 answered ·

Thanks everyone for the replies, much appreciated.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.