Use a SQL Server 2008 database on a NAS share

Question

I'm working on a project in Visual Studio, and I want to create a local database file (.mdf) within the project directory so that it can be checked into SubVersion and have configuration management. Unfortunately, trying to create/attach a database on a network share leads to an error.

All of the resources I have found to enable NAS functionality are SQL Server 2000 based and do not work for 2005/2008.

I don't care about the performance issues surrounding storing a database non-locally and I can guarantee 100% up-time and connectivity to the file server during the periods that I have the database attached. I do care about having my database files stored with the project.

To clarify, my working set from SubVersion is on the NAS. Corporate mobility means I might be at a different workstation later and I'll need access to my working set.

Also, I don't want any answers telling me that I shouldn't do this... I know its strongly recommended against. However, in the case of some sort of database corruption I can always just revert back to the repository version.

Answer 1

I'm pretty sure that you cannot do this with a local mdf. You need to run an instance of SQL Server (even if its express). When you install the instance, you tell the installer package where to store its data and log files. At this point, tell SQL to keep its data and logs on the NAS.

Answer 2

Everyone (including Microsoft) can, should, and will tell you that this is not necessarily a good idea. Be that as it may, MS did provide a way to do this in SQL 2005 (and maybe earlier and maybe later): trace flag 1807. See the MS KB article 304261 for details. (I don't know if it's still valid for 2008.)

Answer 3

Jeff Atwood hat written an article about this topic

Check out his blog entry "Get Your Database Under Version Control" with links to the topic and this blog entry. Also look at the comments

Here are some tools for versioning Databases