SQL Server: Restoring database from a remote location directly vs. copy .bak file and then restore

Question

SQL Server: Restoring database from a remote location directly vs. copy .bak file and then restore

Will anybody know why restoring directly from a LAN location is far faster than the total time require for copying the .bak file first and then restoring.

Example: Restoring directly from a remote location takes 55 minutes
Copying from remote to local to SQL Server takes 98 minutes
Restoring from local location takes 2 minutes.

Why is that?

Answer 1

If you aren't willing to try to improve on the file copy process, then there's not much we can do. My experience is that the GUI file copy is provocatingly slow, and that it gets worse with every version of Windows. So, I again, suggest that you investigate (yourself or ask in an OS Q&A/forum) on the file copy aspect. I'd just tak a reasonable sized file and give it a few tries. And remember to try various command-line options (I believe that there's a /x option for copy command to not cache the data - but my memory might be failing me). If you go that path, pls let us know if you get any significant improvement of the file copy times.

Answer 2

Hi sy27295,

Usually restoring a backup file locally is more reliable and faster than remotely restoring from a network drive.
You can see that it only takes 2 minutes to restore the database locally, but it takes 55 minutes to restore the database remotely.

The problem is on remote copy operation. What is the size of the backup file? It took 98 minutes to copy from remote to local. You need to take multiple tests to obtain results. Certain factors (such as network and server performance) may affect the file copy.

===============================================
If the response helped, do "Accept Answer" and upvote it.

Answer 3

When you restore directly, you only read the data to be restore once. When copying the file, you first read the original file, copy it to a local file. And then read it again (the restore operation). I.e., more work, result in longer time.

Or are you saying that the copy operation alone takes 98 minutes? Then you need to look into that operation. Use a proper copy command (xcopy, robocopy etc), check out what command line switches are available (like don't cache the data that you copy) etc.

Another possibility is that the backup file has more than one backup. I.e., when you restore remote you only read what you need. But my guess would be on something weird with your copy command. Oh, and never use the GUI to copy large files...

Answer 4

Yes, I am using the Windows File Manager GUI WAN copy (file server in Boston to db server in Houston where the copying happens in a server ) with the assumption that Microsoft must have optimized it up the the gazulu (just invented this word); other than posting % stuff back to the client.

Full Backup copy .... 200 Gig Compressed single backup, no trans - simplest possible backup.

Option A ..... restore from file server in Boston to the Database in Houston server 55 minutes.

Option B ..... copy the 200 Gig file from Boston to Houston 98 minutes and restore once the files is in Houston locally 2 minutes; total time 100 minutes.

Answer 5

Copying from remote to local takes more time because some things could affect for example,
-Disk IOPS, there are reads from disk remote,

• Network, if you use your computer as a bridge from source and target all the traffic is passing through your computer. Network speed r/w.
• More IOPS, in the target computer(disk) there are write tasks
• Integrity, there is a checksum at the end of the copy task