Oct
9
2013

How to restore SQL database on network share (UNC path) – SQL Server 2005

As it is known fact that by default SQL Server 2008 and below does not support database files on network shared path \\<sharepath>\<databaseFileName>.<mdf /ldf>, this is done because of certain performance and stability factor (read more about this in links present in reference section of this article). But in case if there is requirement to do a temporary restore on a network share then the below tip will come handy.

 

Note: SQL Server 2008R2 by default support user database files on network share in standalone installations (do not work for system databases and in cluster scenarios) hence no need to enable any trace flag

 

This case is tested and validated for SQL Server 2005 scenario, in order to restore database on a network shared path, 1807 traceflag needs to be enabled first as,

DBCC TRACEON(1807, –1)

 

Then check the status of trace flag by executing following command,

DBCC TRACESTATUS(-1)

 

Then test that database can be created or not by creating a sample database (or actual only) as per requirement,

 

CREATE
DATABASE [my_test_database_unc] ON
PRIMARY

(NAME = N‘my_test_database_unc’,FILENAME = N’\\SharedPath\TempFolder\my_test_database_unc.mdf’
,SIZE = 3072KB ,MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )

LOG
ON

(NAME = N‘my_test_database_unc_log’,FILENAME = N’\\SharedPath\TempFolder\my_test_database_unc_log.ldf’
,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)

GO

 

If database is created successfully then test is complete and now restore can be started as usual by providing the UNC path in MOVE TO section of restore script or in OPTIONS section in SSMS GUI.

 

If activity is over, then traceflag can be disabled using following command,

DBCC TRACEOFF(1807,-1)

 

Note: The traceflag which are enabled using t-sql query gets automatically disabled on SQL Service restart hence if in case there is permanent requirement for this traceflag, then add –t 1807 in the startup parameters of SQL Server Service using SQL Server Configuration Manager.

 

Below reference contain more details on this topic along with KB article describing the workaround,

http://support.microsoft.com/kb/836873

http://blogs.msdn.com/b/varund/archive/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive.aspx

http://ms-abhay.blogspot.ca/2008/02/restoring-database-on-network-using-unc.html

http://blogs.technet.com/b/josebda/archive/2011/02/24/sql-over-smb2-one-of-the-top-10-hidden-gems-in-sql-server-2008-r2.aspx

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e8f5c91a-6a3d-48de-923d-16e73d48f80d/only-when-creating-a-new-databases-access-is-denied?forum=sqldatabaseengine

 

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

1 Comment + Add Comment

  • Very useful the comment about the parameter –t 1807 on SQL Service start. Thank you!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 158 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

March 2019
M T W T F S S
« Nov    
 123
45678910
11121314151617
18192021222324
25262728293031

View Post by Categories

%d bloggers like this: