How to create a database on a network share?
If for any reason you need to place the database on a network share and by default this option is off you can follow the way described below: A database on a network share is created by running T-sql script, graphic mode will not work in this case. So, first we enable 1807 trace flag:
DBCC TRACEON(1807, -1) go
Then run the script for database creation:
-- CREATE DATABASE [db] ON PRIMARY ( NAME = N'db', FILENAME = N'\\DB\data\db_data.mdf' , SIZE = 5120KB , FILEGROWTH = 10024KB ) LOG ON ( NAME = N'db_log', FILENAME = N'\\DB\data\db_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) GO
The database has been successfully created. In such a way we create a database on a network share. The important moments you need to remember are – when you create a database consider the rights MS SQL Server account has on this network share – as long as speed access via LAN and reliability of such connection type are lower compared to standard location of databases, it is recommended to use such method for creation oftest databases or not vital systems.
This stuff was tested in MS SQL Server 2008R2 and 2012.