locked
mssql2008r2: Concatenated string command fails. Need another pair of eyes. RRS feed

  • Question

  • Fellow Techies,

    Here is an excerpt from a stored proc:

     
      declare @backupFileName nvarchar(300)
      set @backupFileName = 'x:\myPath\mydbbackupfile.BAK'

    ALTER DATABASE [myDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

      EXEC ('use [master]; ' + '
        RESTORE DATABASE TPLD
         FROM DISK = ' + @backupFileName + '
           WITH REPLACE,MOVE ''T'' TO ''e:\sql\data\MDF\t_pld.mdf'',
           MOVE ''T_log'' TO ''E:\SQL\DATA\LDF\t_log_pld.ldf'''
          );


      ALTER DATABASE [myDatabase] SET MULTI_USER

    On execution of the sproc, this is what displays:

    Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '\'.
    Msg 319, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    (1 row(s) affected)


    • Edited by ditallop Thursday, January 29, 2015 9:54 PM
    Thursday, January 29, 2015 9:52 PM

Answers

  • Try to print the values

    RESTORE FILELISTONLY FROM DISK = 'x:\mypath\mydbbackupfile.bak'

    And give the valid data and log files names

    declare @backupFileName nvarchar(300),@SQL varchar(max)
      set @backupFileName = 'x:\myPath\mydbbackupfile.BAK'
    ALTER DATABASE [myDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    
    SET @SQL ='use [master]; ' + '
        RESTORE DATABASE TPLD 
         FROM DISK = ''' + @backupFileName + '''
           WITH REPLACE,MOVE ''T'' TO ''e:\sql\data\MDF\t_pld.mdf'',
           MOVE ''T_log'' TO ''E:\SQL\DATA\LDF\t_log_pld.ldf'''
    PRINT @SQL
    EXEC (@SQL);
          
    
    
      ALTER DATABASE [myDatabase] SET MULTI_USER
    --Prashanth


    • Edited by Prashanth Jayaram Thursday, January 29, 2015 10:07 PM
    • Marked as answer by ditallop Thursday, January 29, 2015 10:22 PM
    Thursday, January 29, 2015 10:01 PM