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.
SQLTwins by Nakul Vachhrajani 
1. Don’t back up databases to the same server. Horrible idea in production, even worse in development.
2. The Sevice Account SQL Server runs under will have to be a domain account. That domain account will need to be granted access to the share AND the directory in the file system. IIRC, The account will need FULL or MODIFY permission to the directory.
LikeLike
@Marc: Generally, what I have seen is that when database backups are taken in development environments, they are either because the server is being rebuilt or because the database needs to be shared with some other team for their (generally invasive) development and testing. This is one of the reasons why we generally keep the databases in our development environment in SIMPLE recovery.
If something unforeseen does happen, the database can be rebuilt using the scripts and test data generation plans stored safely away under source control.
For my academic interest, why do you say that in such cases, taking database backups one the same server is bad for the development environment?
(I completely support your views that its a horrible idea for production instances. Also, the requirements around the SQL Server account are available in the Books-On-Line entry I referenced in the post).
LikeLike
The reason why backups should never be stored on the same server, regardless of prod vs dev, is the same: if you lose the server, you lose the backups. In the case of dev, you lose the work of your developers: a cost. While object definitions are generally stored under source control, data generally is not. Nor are dependencies (base table definitions and view definitions may be under source control, but the fact the tables need to be created before the views is not.
It is much easier to have a solid backup/restore strategy in place. You can also easily seperate object definitions and data storage with the use of filegroups, then using filegroup backups (and if necessary, piecemeal restores) to mitigate risk.
LikeLike
I see your point now, Marc. Thank-you!
LikeLike