Create a SQL Server Database on network drive

Sometime back I came across a situation when a server ranout of local storage and among other things we did not have HBA card readilyavailable so SAN storage could not be attached.

Only solution was to map a network drive and move somedatabase files to this network drive. So here is a small article on how to create a database on network drive

For SQL Server 2008and SQL Server 2005

By default you can not have database files (MDF, NDF, LDF)on a network drive. This is because any network glitch can cause databasecorruption along with I/O issues inherently caused by network drives.

But it is “allowed” by tweaking a setting in SQL Servertrace flags.

1. Enable trace flag 1807 (Description from BOL: Allows you to configure SQL Server with network-based database files.)

DBCC TRACEON(1807, -1)

2. Now make sure account under which SQL Server service is running has full access to the network location
   
   
3. Create database
   
   CREATE DATABASE [NetDriveDB] ON PRIMARY

( NAME = N'NetDriveDB',

FILENAME = N'\\computername\DBDrive\NetDriveDB_Data.mdf' ,

SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH= 1024KB )

LOG ON

( NAME = N'NetDriveDB_log', FILENAME = N'\\computername\DBDrive\NetDriveDB_Log.ldf' ,

SIZE = 2048KB , MAXSIZE = 4096GB , FILEGROWTH = 10%)

GO

For SQL Server 2008R2

In SQL Server 2008 R2 you do not need to turn on 1807 flag. However we need to make sure it is UNC path (\\ComputerName\SharedFolder\Resource\)

1. Now make sure account under which SQL Server service is running has full access to the network location
   
   
2. Create database
   
   CREATE DATABASE [NetDriveDB] ON PRIMARY

( NAME = N'NetDriveDB',

FILENAME = N'\\computername\DBDrive\NetDriveDB_Data.mdf' ,

SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH= 1024KB )

LOG ON

( NAME = N'NetDriveDB_log', FILENAME = N'\\computername\DBDrive\NetDriveDB_Log.ldf' ,

SIZE = 2048KB , MAXSIZE = 4096GB , FILEGROWTH = 10%)

GO