Restoring Database on a network using UNC path

Have you ever tried to keep your database files away from the actual server ...
Say , your SQL Server is on Server A and datafiles on Server B ....is it possible ..

Lets try :)

RESTORE DATABASE [TestCorruption_Load]
FROM DISK = N'\\abhay\Backup\TestCorruption_Load.bak'
WITH STATS = 10, RECOVERY , REPLACE ,
MOVE N'TestCorruption_Load' TO N'\\abhay\Backup\abc.mdf',
MOVE N'TestCorruption_Load_log' TO N'\\abhay\Backup\abc.ldf'

Results :

Msg 5110, Level 16, State 2, Line 1
The file "\\abhay\Backup\abc.mdf" is on a network path that is not supported for database files.
Msg 3156, Level 16, State 3, Line 1
File 'TestCorruption_Load' cannot be restored to '\\abhay\Backup\abc.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5110, Level 16, State 2, Line 1
The file "\\abhay\Backup\abc.ldf" is on a network path that is not supported for database files.
Msg 3156, Level 16, State 3, Line 1
File 'TestCorruption_Load_log' cannot be restored to '\\abhay\Backup\abc.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Yes , normally its not allowed and Microsoft does not support this ...

Now , enable trace flag 1807 (http://support.microsoft.com/kb/304261)
dbcc traceon (3604,1807 , -1)

and here we go :


RESTORE DATABASE [TestCorruption_Load] FROM DISK = N'\\abhay\Backup\TestCorruption_Load.bak' WITH STATS = 10, RECOVERY , REPLACE , MOVE N'TestCorruption_Load' TO N'\\abhay\Backup\abc.mdf', MOVE N'TestCorruption_Load_log' TO N'\\abhay\Backup\abc.ldf'

Processed 912 pages for database 'TestCorruption_Load', file 'TestCorruption_Load' on file 1.
Processed 2 pages for database 'TestCorruption_Load', file 'TestCorruption_Load_log' on file 1.
RESTORE DATABASE successfully processed 914 pages in 1.264 seconds (5.917 MB/sec).

one interesting fact that I have noticed is : the performance is much faster here as compared to non UNC restore .....try it on the test machine ...

But again , this is risky and not supported yet ....
Hoping this will be suppported some day....