Sunday, January 31, 2010

Database on a Network Drive



How to create a database on a network drive?

Though not a recommended practice, if you need to resort to this option, due to space constraints then follow the following steps.

Step 1:
DBCC traceon ( 1807 )

Step 2:

CREATE DATABASE [test] ON  PRIMARY

( NAME = N'test', FILENAME = N'\\Machinename\\network_shared_machinename\\path\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'test_log', FILENAME = N'\\Machinename\\network_shared_machinename\path\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)


GO



The SQL service account/user account should have full rights to access the shared machine and the shared folder.Also note that you can only use the script to create the database, as management studio doesn't show shared drives even after enabling trace flag ( 1807 ).

Having a database on a network/shared drive makes the db more vulnerable to corruption issues and performance can also dip. So, certainly not a recommended practice, even for stage/development environments but perhaps can be used temporary arrangement.

Refer here for more details

No comments: