Apr 6, 2013

Attach SQL Database from Network Drive

One crazy question came up in mind, can I attach a database from network drive? There’s a lot of recommendation, that we can’t attach SQL Database. Why would we want it? Anyway no one would do that, unless they want a little bit hiccup in their system and mess up the whole thing? That’s true, but I want a solid answer. Can it be done?

The answer is it can be done. But again, on really strict rules, this is not recommended. Why would I want it? Because I have development VM (VirtualBox) and I need those big databases (say about ~20GB) to be exist on my system without copying it inside my Virtual Hard Disk which will take some time to copy. Again, it’s a development VM, so I could manage it and no one would care if my system crashed or database not exist.

So, here it is.

Attach Database from Network
  1. -- this command to turn on network share attach DB
  2. DBCC TraceOn(1807)
  3. -- attach it
  4. EXEC sp_attach_db @dbname = N'WSS_Content_85',
  5.    @filename1 = N'\\Vboxsvr\r\DOCS\SQL Database\WSS_Content_85.mdf',
  6.    @filename2 = N'\\Vboxsvr\r\DOCS\SQL Database\WSS_Content_85_log.ldf'
  7. -- this command to turn off network share attach DB
  8. DBCC TraceOff(1807)

Please do note that this command would likely not recommended, especially on production server. Thanks to Sumardi Soemartopo for blogging about this COOL tips, and this is the link to that page http://guozspace.wordpress.com/2009/02/26/microsoft-sql-server-how-to-attach-database-from-network-drive/.