I am sure this post would ring a bell with most development teams. Development servers are generally constrained for space. Whenever a development team requests more hardware, be it in terms of a new machine or even additional storage space, I am sure that most IT departments would have told them “Why do you need all these servers? You already have n number of servers, which are more than the number of people on the team!”
Ultimately, this results in lack of disk space to store both a database and it’s backup on the same drive. In fact, we had this issue some time ago, wherein we had to restore a reasonably large database 50GB+. Both the backup and the database simply could not reside on the same server due to disk space issues.
So, we decided to place the backup file on a network share, map it to a drive on the server and restore the database from there. However, that just wouldn’t work. Attempting to restore through the SSMS results in the following error:
But, as they say – “Where there is a will, there is a way.” The core issue here is that mapped network drives are not supported by SQL Server. UNC paths however, are a different story.
SQL Server fully supports backups & restores over UNC paths. There are 2 methods that you can use:
- Directly type the UNC path where the backup needs to be taken/restored from
- Create a backup device pointing to the UNC path and then backup to/restore from this device
After looking at how easy and convenient it is to use UNC paths with SQL Server, our entire team has started using them. Here are a some scripts that you may be interested in:
- Backup Databases across the network: http://beyondrelational.com/modules/30/scripts/485/scripts/15042/backup-database-across-the-network.aspx
- Restore Databases across the network: http://beyondrelational.com/modules/30/scripts/485/scripts/15043/restore-databases-from-a-backup-file-across-a-network.aspx
There are a couple of security considerations that you need to take care about. They are available in the following MS KB article: http://support.microsoft.com/kb/207187
These scripts are available in the Scripts module on BeyondRelational.com (http://beyondrelational.com/modules/30/default.aspx?s=stream&tab=scripts). If you haven’t been to the Scripts module page, do pay it a visit – you will find a lot of useful scripts that you can customize for your use.
Until we meet next time,
Be courteous. Drive responsibly.