SQL Server Restoring backups from UNC Network paths and Recovery Time Objectives

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,

  1. Copy large backups onto local drives
  2. Start the restoration process
  3. 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.

1126_restore1

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.! 🙂

 

 

Advertisements
About

IT professional with wider interest in technology

Tagged with: , ,
Posted in backup, sql, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: