title: Problems with Path Variable for Restore
body: <p>I am a bit confused with my issue. I have a backup file on a remote server that I need to restore locally. I can restore the file without problem but some some reason when I put the variable for the path it appends to my path the default backup location. If I were to put the path directly it works so I don't believe the shared is the problem. I included the error and a the print results of my path variable. I have copied the printed path and replaced it with my  variable and it works just fine. Since the path is appended it fails of course to find the file.</p>
  
  <p>My script</p>
  
  <pre><code> --Declare the Network Drive
   Exec XP_CMDSHELL 'net use Y: \\ProdServer\Backups'
  
  --Declare the Filepath
  declare @Backupfilename as nvarchar(500)
  declare @filename as nvarchar(40)
  Set @filename = ( Select Top(1) CONVERT(VARCHAR(36),name)+'.bak' from ProdServer.msdb.dbo.backupset WITH (nolock)
          where database_name='ProdDB'
              order by backup_set_id desc)
  
  Set @Backupfilename = ''''+'Y:\' + @filename + ''''
  Print @backupfilename
  
  --Start the Actual Restore
  RESTORE DATABASE [TESTRestore] FROM  
  DISK = @BackupFileName WITH  FILE = 1,  
  MOVE N'ProdDB' TO N'D:\Data\ProdDB_Test.mdf',  
  MOVE N'ProdDB_Log' TO N'D:\ProdDB_Test.ldf', 
  NOUNLOAD,  REPLACE, STATS = 10
  GO
  
  
  --Clear the Defined Network Path
  EXEC XP_CMDSHELL 'net use Y: /delete'
  </code></pre>
  
  <p>Results</p>
  
  <pre><code>'Y:\ProdDB_backup_201206220600.bak'
  </code></pre>
  
  <blockquote>
    <p>Msg 3201, Level 16, State 2, Line 15<br>
    Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\'Y:\ProdDB_backup_201206220600.bak''.<br>
    Operating system error 123(The filename, directory name, or volume
    label syntax is incorrect.).<br>
    Msg 3013, Level 16, State 1, Line 15<br>
    RESTORE DATABASE is terminating abnormally.</p>
  </blockquote>
  
  <p>Thanks</p>