CODEWITHSUNDEEP
sql-server
Eltonka
Eltonka

Reputation: 11

Problems with Path Variable for Restore

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.

My script

 --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'

Results

'Y:\ProdDB_backup_201206220600.bak'

Msg 3201, Level 16, State 2, Line 15
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\'Y:\ProdDB_backup_201206220600.bak''.
Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
Msg 3013, Level 16, State 1, Line 15
RESTORE DATABASE is terminating abnormally.

Thanks

Upvotes: 0

Views: 1423

Answers (1)

Eltonka
Eltonka

Reputation: 11

I was able to solve the problem by passing the path into a temp table and then recalling when I needed it.

Upvotes: 1

Related Questions