Might sound very basic but I still come across colleagues who are confused on this topic. SQL Server management studio when restoring backups does no let you browse to network drives.
That is why if quick workarounds are not known people end up for large databases in GBs or TBs,
- Copy large backups onto local drives
- Start the restoration process
- Delete the large backup file
This is time consuming and seems like repeating of the same process as the contents in form of backup file and then data and log files is copied twice then the space is released for the place where the backup was originally copied perhaps remaining part of the drive till it is eventually used which can be a long time.
This can seriously impact your Recovery Time Objectives due to potential errors and restarts of transfer. Furthermore the sad thing is that the workaround have existed since SQL 2000 days.
The solution that I mostly use is to restore such backups over the network wherever the network is reasonably reliable is to just simply put in the full path of the backup file including extension in UNC format in the file opening dialogue and once file is selected proceeding as normal.
You can do the same with the file parameter in Restore command and achieve the same result and saving a lot of valuable time in case of large backup files.
For a more complicated solution see this blog post (Restore Database from a UNC path – Why SSMS doesn’t show network drives/paths in the restore wizard) which shows you how you can force the Management studio to browse unc paths. Based on its complexity is it worth it is your decision.
Until next time take care.! 🙂