Day 2–Trace Flag 1807–Attach Network data file

Today we are going to see how can we attach data file exists in network share with the help of trace flag 1807. By default SQL Server 7,2000 and 2005 will not allow you to attach data file that exists in network share. This is the default behavior because attach database file from network share is overhead as performance is the great factor because if network share is slow or if disruptions happen in the network share will make the database to go offline. However in some circumstances we require this to be done for testing purpose. You will be getting anyone of the error below if you try to attach the data file from network share (without using trace flag 1807).

5105 “Device Activation Error”
or
5110 “File ‘file_name’ is on a network device not supported for database files.”

For testing purpose if you want to attach network share data file you need to turn on trace flag 1807 and then give a try. I’m going to turn on 1807 trace flag and attach a network share data file as shown below. You can view the article  “Enable \ Disable Trace Flag in SQL Server” for turning trace flag in different method.

DBCC TRACEON(1807)
GO
EXEC sp_attach_db nwsharedatabase, '\\sagar-pc\db\nwsharedatabase.mdf',
'\\sagar-pc\db\nwsharedatabase_log.ldf'

Once you done with your work, you can detach the db and turn off the trace flag 1807, you can use the command below

DBCC TRACEOFF(1807)

Thus trace flag 1807 is required to attach data files from network share on SQL 7, 2000 & 2005 however from SQL 2008 I’m able to attach data files from network share without turning on this trace flag.