Can we use Network Drive for SQL Server Database creation?

Leave a comment (0) Go to comments

Can we create SQL Server Database using Network Drives ?

Can we use Network Drive for SQL Server Database creation?

Can I create SQL Server databases on network drives?

Can we Backup and restore using mapped network drive?

Can We attach database from files which are hosted Network Drive?

Yes, You can create a SQL Server database on Network Share in SQL Server 2008 and 2005.

By default, you cannot create a new database on a network share drive, you need to enable this setting at SQL Server level so that SQL Server can place database files over the UNC path. But please keep remember this is not a suggested / recommended way, as your performance will be degraded and you will be more prone to database corruption due to network glitches

To use this functionality, we need enable a SQL Server Trace Flag 1807.

A small example to use this functionality.

Step 1. Enable the Trace Flag 1807:

DBCC TRACEON(1807, -1)


Step 2. Ensure that SQL Server Service start-up account has FULL access on file share where you wished to place database files

Step 3. Create the database on the network drive

CREATE DATABASE [DB_NETWORK_File_TEST] ON  PRIMARY

( NAME = N'DB_NETWORK_File_TEST', FILENAME = N'\\Server1\DBATAG\ DB_NETWORK_File_TEST.mdf' , SIZE = 1024MB )

LOG ON

( NAME = N'DB_NETWORK_File_TEST_log', FILENAME = N'\\Server1\DBATAG\ DB_NETWORK_File_TEST_log.ldf' , SIZE = 512MB)

GO 

That all, you database is now on network drive.

Note :

  • SQL Server 2008R2, you can directly create a database over network drives without enabling trace flag 1807
  • This is not recommended by Microsoft to place your DB files over the network
  • Performance will degraded because of network delays
  • Database with network files are more prone to database corruption due to network glitches

Related Articles from Microsoft:

Description of support for network database files in SQL Server

EOF - Can we use Network Drive for SQL Server Database creation?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.