Restore DB to a network server
-
Hi,
I have a PROD server A and dev server B. I would like the prod backup from A to be restored to B, from A itself.
Can someone help me with the query which I can run from A to restore the DB in B, please.
(Database already exists in B.)
Many Thanks,
Manu
-
Manu,
You would have to run the restore from a connection to server B pointing to the backup file on server A. So, from SSMS connected to server B run something like the following;
restore database ABC
from disk = '\\serverA\d$\mssql\backups\ABC123.bak'
with ......
Obviously pieces missing from the ending of the script but BOL has some great examples.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
yes; it worked for me.
I referred BOL for the complete syntax...
thanks a lot
-
I would recommend copying the backup file over to the other server and restoring locally. Network latency can cause problems with the restore process, and in all it would be faster.
-
I agree with nicolas
Abhijit - http://abhijitmore.wordpress.com
-
i ws having issues in copying the files over network.
My bak files were of size 20GB+ and copying failed multiple times saying "path is too deep" and all.
Today i tried restore over network and even though it took time, it worked fine for me.
thanks anyways for all the inputs.
-
That's an unusual issue.
I'd recommend downloading robocopy(part of the window resources kits) for large file copies. It handles logging, automatic continuation in the event of a problem, and whole host of other features and even better is free.
-
even i have herd abt this...bt needs to see if client agrees to it...
😀
cz we hardly do this copyin n restore...
lemme see if i cn push this in the next client meeting...
thnx man!!
-
Nicholas, in this case the data is being copied to dev server. I don't think restoring from a network path will be an issue. Sometimes (depending on how the server has being configured) restoring from a network share could be even faster than a local restore (for instance when the restore is being made to the same disk).
-
Restoring could be faster, though I'm not sure if the restore process is tolerant of network issues. The backup process is not.
-
Steve Jones - Editor (12/4/2008)
Restoring could be faster, though I'm not sure if the restore process is tolerant of network issues. The backup process is not.I agree on the fact that a network restore and for sure a backup is not the best option for production servers, but in this case is a dev server. I can not think of a reason for not doing the network restore.
-
Ignacio A. Salom Rangel (12/4/2008)
Steve Jones - Editor (12/4/2008)
Restoring could be faster, though I'm not sure if the restore process is tolerant of network issues. The backup process is not.I agree on the fact that a network restore and for sure a backup is not the best option for production servers, but in this case is a dev server. I can not think of a reason for not doing the network restore.
The one concern is that it's a restore from production to development. Depending on the way the network is set up and the interface which is being used between the two machines you could be looking at potential degradation of performance in returning data to the clients. A copy process might help that if done off hours. Of course if on a SAN a split mirror would be lightening like.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply