Restore Database from local disk

Question

http://msdn.microsoft.com/en-us/library/ms186858.aspx
RESTORE DATABASE AdventureWorks2012
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
   WITH FILE = 6
      NORECOVERY;
When you specify FROM DISK, is the drive letter relative to the server you are connecting to?  
In other words, if your on a workstation with SSMS and connected to a server.  If youre running a restore,
then the paths specified are relative to the server or your own local system?

Answer 1

It's the path where you've placed the backup file(*.bak) file.

It should be the path relative to the server.

Here Z drive is local to your server

RESTORE DATABASE AdventureWorks2012 FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak' WITH FILE = 6 NORECOVERY;

Restoring the database from a shared path(this can be your local workstation)

RESTORE DATABASE AdventureWorks2012 FROM DISK = '\\workstation\C$\AdventureWorks2012.bak' WITH FILE = 6 NORECOVERY;

--Prashanth

Answer 2

The path is relative to machine on which SQL Server is running. SQL Server has no idea or interest in what the drive letters on your local box may refer to.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

T-SQL runs on the server.  SSMS will display only the local drives on the server where the T-SQL runs.  You can use T-SQL to run a restore that uses a UNC path, but the path is used from the server.  To use a UNC with a admin share (e.g., \\machine\C$), the SQL service account will need to be a member of the local admin group on that machine - not good.  Better to create a share with proper access or to copy the file to default backup folder on the server - because the SQL Server service account might not be a member of the local admin group and not have access to all locations. 

It can get interesting if there are more than two machines involved and windows authentication.  You use machine A to issue a T-SQL query to machine B and the T-SQL query specifies a path to machine C.  A single hop from A to B and then back from B to a path on A should be fine.  It can be tricky to get it to allow A to B and then B to C.  The same query that would fail when run on A will work if run on B or C.  The server B could be trusted for delegation if required, but that's drastic.  I've run into this with bulk operations. 

---

Randy in Marin