May 21, 2013 at 1:06 am
Hi.
My requirement is Restoring database from network place. when i am trying the restore the drive is not display in my browse button. How to restore DB in network place. please give me reply.....
regards...
shiva
May 21, 2013 at 1:49 am
1. Share the backup folder on your source server - \\sourceservername\backupfolder$
2. Set read permissions on that share for database engine service account (destination server).
3. Run restore, select from device, add and type \\destinationserver\backupfolder\database2restore.bak
4. Restore
May 21, 2013 at 1:59 am
That's right. You have to specify a URL, not a mapped drive. The drive is only mapped in the context of your login session, and not that of the session that is running SQL Server.
John
May 21, 2013 at 5:57 am
gaddamshiva (5/21/2013)
Hi.My requirement is Restoring database from network place. when i am trying the restore the drive is not display in my browse button. How to restore DB in network place. please give me reply.....
regards...
shiva
Although that is your requirement, I would seriously think about another idea.
Copy the backup to the server on which is will be restored to. If you don't have the space then use a USB drive if the database size allows for it. Restore the database from there using a local drive letter.
Restoring over the network is only really viable if a) the database is relatively small and b) if you can guarantee you won't stop other applications or users from performing normally because you have taken the remaining available bandwidth.
Unless of course you understand how to bandwidth throttle on your routers and switches.....
May 21, 2013 at 6:33 am
Well, if you do that, you're going to use that bandwidth up when you do the copy instead of when you do the restore, so I wouldn't worry too much about that. We do backups and restores across the network without bringing the business to its knees.
John
May 21, 2013 at 6:58 am
So do we in Oracles Data Guard and the restoration of our 3TB databases to our network test location with DUPLICATE DATABASE. We have a 20GB connection between the two though.
My point was that the OP needs to be aware of the restrictions he might have imposed on him. We (me and you) are not privvy to the amount of bandwidth he has available. Perhaps his company is still one of the few using hubs instead of switches, perhaps he needs to consider a period in time whereby other applications/users are utilising the server.
I have also been able to map network locations with NET USE which has also been successfully implemented in restoring databases from network locations but (as you correctly pointed out) although copying the backup through the network does still consume bandwidth, it consumes less for less time, also something to consider in this case.
May 21, 2013 at 7:07 am
I've never used NET USE when restoring backups. The only way it would work is if run in the context of the service account, which would mean a two-stage process: mapping the drive and then doing the restore. Easier to use URLs.
If the copy happens faster than the restore, that'll be because more bytes are being shifted per second. So although your network is tied up for a shorter time, a higher percentage of your bandwith is being used during that time. The only way to mitigate that is to perform or schedule the copy or restore during a quiet period.
John
May 21, 2013 at 8:01 am
John Mitchell-245523 (5/21/2013)
I've never used NET USE when restoring backups. The only way it would work is if run in the context of the service account, which would mean a two-stage process: mapping the drive and then doing the restore. Easier to use URLs.If the copy happens faster than the restore, that'll be because more bytes are being shifted per second. So although your network is tied up for a shorter time, a higher percentage of your bandwith is being used during that time. The only way to mitigate that is to perform or schedule the copy or restore during a quiet period.
John
Your definition of "easier" is somewhat abstract in my opinion but then that is a personal opinion.
The copy will -almost- always be faster than the restore because the data being moved is shifted block-for-block. The restoration of the database itself takes on a more logical approach because of the application of logs, rolling back transactions and so on that are necessary as part of the restore process to keep the database consistent.
Just as an example, consider moving a file within the same server room over one switch between two machines. It will be fast. Especially if those two machines share a common subnet not isolated from the main network traffic. Even the restore would be fast, maybe even as fast depending on how much work the restore has to do.
Now consider moving that same datafile across subnets with users, applications and other servers sharing the same bandwidth. If that datafile is a backup file it would arguably be faster to simply copy it across and restore it than to go through the process of restoring across the network. Then what happens if something goes wrong? Restarting a restore across the network could be administratively prohibitive in terms of time required to make it happen. You cannot compare a network copy at 100/1000MB/sec to a restore direct from disk especially if that disk happens to be a storage LUN with that datafile spread over multiple spindles.
The problem is we don't know enough to be able to answer confidently.
1. How big is the backup to be restored?
2. Are the two servers on the same subnet or on different subnets (maybe even continents!)?
3. How much time is allocated to the restore from start to finish?
4. What resources are available to store the backup?
5. Is network capacity sufficient to handle the significant increase in data transfer?
6. Will the restore be carried in a maintenance window that cannot easily be reallocated?
and so on......
Between the two of us we can thrash out our ideas until our ears bleed but without concrete information about the working infrastructure that this applies to, we are doing nothing but playing flame wars.
Perhaps it wouldbe easier to ask "how do make my database faster......"
Answers on a postcard please......
May 21, 2013 at 8:52 am
kevaburg (5/21/2013)
Your definition of "easier" is somewhat abstract in my opinion but then that is a personal opinion.
Example of mapped drive restore (assuming you even have access to use xp_cmdshell):
EXEC xp_cmdshell 'NET USE R: \\MyServer\MyShare'
RESTORE DATABASE MyDB FROM DISK = 'R:\MyDB.BAK'
WITH <whatever>
Example of URL restore:
RESTORE DATABASE MyDB FROM DISK = '\\MyServer\MyShare\MyDB.BAK'
WITH <whatever>
You're right - it's subjective, so readers will make up their own minds which is easier.
The copy will -almost- always be faster than the restore because the data being moved is shifted block-for-block. The restoration of the database itself takes on a more logical approach because of the application of logs, rolling back transactions and so on that are necessary as part of the restore process to keep the database consistent.
I'm not denying that copying is faster. My point is that the number of bytes squirted across the network is roughly the same, so you get a slow operation which ties affects the network for longer but uses less bandwidth, or a fast operation which is over quicker but uses a higher percentage of your bandwidth.
Then what happens if something goes wrong? Restarting a restore across the network could be administratively prohibitive in terms of time required to make it happen.
You're quite right - the risk of having to start again is the main drawback of my approach. I find that the risk is quite small and so does not outweigh the benefits.
1. How big is the backup to be restored?
2. Are the two servers on the same subnet or on different subnets (maybe even continents!)?
3. How much time is allocated to the restore from start to finish?
4. What resources are available to store the backup?
5. Is network capacity sufficient to handle the significant increase in data transfer?
6. Will the restore be carried in a maintenance window that cannot easily be reallocated?
Those are indeed valid questions, and there will be times when the copy and restore is the best solution, and other times when the restore over the network fits best. The reason I got involved in this discussion was only to point out that copying the backup file does not have a significant net benefit in terms of network traffic.
John
May 21, 2013 at 4:18 pm
kevaburg, John Mitchell-245523
What's your experience / opinion on performing backups in the ways you describe?
Thanks
.
May 22, 2013 at 1:28 am
We do most of our backups across the network. The advantages are as follows:
(1) Don't need to duplicate disk space on local server and on backup repository share.
(2) Backing up in a single operation means that we have an off-site (or at the very least, off-server) full backup of the database in the smallest amount of time after the backup begins.
(3) It's simpler.
The drawbacks are as follows:
(1) If the backup fails half way through, it's a waste of network traffic.
(2) Depending on the speed of the network, the backup operation itself may be slower.
John
May 22, 2013 at 2:42 am
Tim Walker. (5/21/2013)
kevaburg, John Mitchell-245523What's your experience / opinion on performing backups in the ways you describe?
Thanks
For us the advantages of copying the backup to the restore location are as follow:
1. We have a redundant copy of the backup in the remote location that we can use for multiple restorations should it be required.
2. The copy across the internetwork is fast because it is a block-for-block copy. Test servers are then restored from a local device.
3. We backup locally to an external SAN in the serverroom so that local restores/DR are at their fastest as long as the server room is still standing!
4. The SAN backup can be taken very quickly to another location and mounted if required (this has happened twice).
5. Local backups are copied offsite to tape on a dedicated network link for redundancy in a compressed format.
The disadvantages are:
1. We need extra space for the redundant backup copy.
2. Should we need to restore from tape then the process is much slower than from disk.
3. The dedicated hardware and network conenctions are not without cost!
May 22, 2013 at 4:34 am
Thanks both for the feedback, it's much appreciated. I have some space constraints to juggles with and it's great to hear what others are doing in practice.
Thanks again
Tim
.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply