Sql-server – How to restore Network backup to Network Path in SQL Server 2005

sql serversql-server-2005sql-server-2008t-sql

I am trying to restore network backup file to network path.. is there any way to do it?

I am using SQL Server 2005 and code…. 

RESTORE DATABASE DB_NAme
FROM DISK = N'\\NetworkPath\backup_20140501030004.BAK'
WITH  FILE = 1
, MOVE 'DB_Data' TO '\\NetworkPath\DATA\DB.mdf'
, MOVE 'DB_Data2' TO '\\NetworkPath\DATA\DB_1.mdf'
, MOVE 'DB_Data3' TO '\\NetworkPath\DATA\DB_2.mdf'
, MOVE 'DB_Log' TO '\\NetworkPath\Log\DB_log.ldf'
, NOUNLOAD,REPLACE, STATS = 10;

Error:

Msg 5110, Level 16, State 2, Line 2
The file "\NetworkPath\DATA\DB.mdf" is on a network path that is not supported for database files.
Msg 3156, Level 16, State 3, Line 2
File 'DB_Data' cannot be restored to '\NetworkPath\DATA\DB.mdf'. Use WITH MOVE to identify a valid location for the file.

Thanks in advance

Best Answer

Microsoft has documented their "Description of support for network database files in SQL Server" at: http://support.microsoft.com/kb/304261

Trace flag 1807 bypasses the check and allows you to configure SQL Server with network-based database files. However, this is not without risk and you can fairly easily wind up with a corrupt database if the device does not fully support what SQL Server needs to achieve consistency.

If you want to use this approach in a manner that Microsoft will fully support, they have a set of standards describing what they support. Following is a quote from the link:

Windows Hardware Quality Lab (WHQL) qualified devices Microsoft Windows servers and networked servers or NAS storage servers that are Windows Hardware Quality Lab (WHQL) certified automatically meet the data write ordering and write-through guarantees required to support a SQL Server storage device. Microsoft supports both application and storage-related issues in these configurations.

Note To be supported by SQL Server, the NAS storage solution should also meet all the requirements that are listed on the following Microsoft website: http://download.microsoft.com/download/6/E/8/6E882A06-B71B-4642-9EB4-D1EA0D6223C8/SQL%20Server%20IO%20Reliability%20Program%20Requirements%20Document.docx

Related Question