SQL Server

[nkahootz]

Hello, all.  I'm hoping that someone can help me with this.

I'm using VMWare Fusion on my iMAC to run Windows 7 with MS SQL Server.  I've been able to get all of this working fine, but I really want to store my databases (the SQL MDFs) on the iMAC and have SQL Server access them from there.  This is just a develop machine and I want to do this so that I can easily re-image my virtual machine without risk of data loss (the iMAC itself is backed up and I'm trying to avoid backing up the VM).

In SQL Server, when I try to attach to the database, I only see the C:\ drive.  I do not see the VMWare share, though I do see it elsewhere.  I tried accessing the share directly by manually entering it when attaching the db and get a "location cannot be found" error.

Has anyone done this?  Any pointers here?

I appreciate all of the help!

Shawn

[nkahootz]

I found the answer myself after way too many hours...

By default, SQL Server does not allow use of a network disk for storage of database files.  This normally makes sense, but may sometimes be needed (like in my scenario here).

First, you need to enable trace flag 1807, which will then bypass the network disk check:

DBCC TRACEON(1807, -1)

Next, when attaching to (or creating new) database, simply refer to the network location (not the mapped drive) and it should work:

\\vmware-host\Shared Folders\Documents\MSSQL\DATA\mySQLDatabase.mdf

That's it! 

Thanks all!

Shawn

[nkahootz]

Just found the following article, which states that network database locations are now allowed in SQL Server 2008R2.

It also confirms what I had found as well.

http://blogs.msdn.com/b/varund/archive/2010/09/02/create-a-sql-server-database-on-a-network-shared-d...

Thanks again!

Shawn