Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to restore a database I get this error

Restore FIlELIST is terminated abnormally

I tried adding a space in logicalName but could not solve it.I am using the below mentioned stored procedure

use [master]
Go

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

 ALTER PROCEDURE [dbo].[RestoreDB] -- Alter the SP Always
@p_strDBNameTo SYSNAME,
@p_strFQNRestoreFileName VARCHAR(500)
 AS
 --
 BEGIN 
  SET NOCOUNT ON;   

DECLARE   @v_strDBFilename VARCHAR(100)
DECLARE   @v_strDBLogFilename VARCHAR(100)
DECLARE   @v_strDBDataFile VARCHAR(100)
DECLARE   @v_strDBLogFile VARCHAR(100)
DECLARE   @v_strExecSQL NVARCHAR(1000)
DECLARE   @v_strExecSQL1 NVARCHAR(1000)
DECLARE   @v_strMoveSQL NVARCHAR(4000)
DECLARE   @v_strREPLACE NVARCHAR(100)
DECLARE   @v_strTEMP NVARCHAR(1000)
DECLARE   @v_strListSQL NVARCHAR(4000)
DECLARE   @v_strServerVersion NVARCHAR(20)
  DECLARE       @v_intErr  INT
  DECLARE   @v_intRetval  INT
  DECLARE   @v_strerrormsg  VARCHAR(210)

SET @v_strREPLACE = '' 
--SET @p_strFQNRestoreFileName= 'D:\backupeftest_20130321111150.BAK'  
IF exists (select name from sys.databases where name = @p_strDBNameTo)
    SET @v_strREPLACE = ', REPLACE'

SET @v_strListSQL = ''
SET @v_strListSQL = @v_strListSQL + 'IF OBJECT_ID(''tempdb..##FILE_LIST'') IS NOT NULL '
SET @v_strListSQL = @v_strListSQL + 'BEGIN'
SET @v_strListSQL = @v_strListSQL + '   DROP TABLE ##FILE_LIST '
SET @v_strListSQL = @v_strListSQL + 'END '

SET @v_strListSQL = @v_strListSQL + 'CREATE TABLE ##FILE_LIST ('
SET @v_strListSQL = @v_strListSQL + '   LogicalName VARCHAR(64),'
SET @v_strListSQL = @v_strListSQL + '   PhysicalName VARCHAR(130),'
SET @v_strListSQL = @v_strListSQL + '   [Type] VARCHAR(1),'
SET @v_strListSQL = @v_strListSQL + '   FileGroupName VARCHAR(64),'
SET @v_strListSQL = @v_strListSQL + '   Size DECIMAL(20, 0),'
SET @v_strListSQL = @v_strListSQL + '   MaxSize DECIMAL(25,0),'
SET @v_strListSQL = @v_strListSQL + '   FileID bigint,'
SET @v_strListSQL = @v_strListSQL + '   CreateLSN DECIMAL(25,0),'
SET @v_strListSQL = @v_strListSQL + '   DropLSN DECIMAL(25,0),'
SET @v_strListSQL = @v_strListSQL + '   UniqueID UNIQUEIDENTIFIER,'
SET @v_strListSQL = @v_strListSQL + '   ReadOnlyLSN DECIMAL(25,0),'
SET @v_strListSQL = @v_strListSQL + '   ReadWriteLSN DECIMAL(25,0),'
SET @v_strListSQL = @v_strListSQL + '   BackupSizeInBytes DECIMAL(25,0),'
SET @v_strListSQL = @v_strListSQL + '   SourceBlockSize INT,'
SET @v_strListSQL = @v_strListSQL + '   filegroupid INT,'
SET @v_strListSQL = @v_strListSQL + '   loggroupguid UNIQUEIDENTIFIER,'
SET @v_strListSQL = @v_strListSQL + '   differentialbaseLSN DECIMAL(25,0),'
SET @v_strListSQL = @v_strListSQL + '   differentialbaseGUID UNIQUEIDENTIFIER,'
SET @v_strListSQL = @v_strListSQL + '   isreadonly BIT,'
SET @v_strListSQL = @v_strListSQL + '   ispresent BIT'

SELECT @v_strServerVersion = CAST(SERVERPROPERTY ('PRODUCTVERSION') AS NVARCHAR)

IF @v_strServerVersion LIKE '10.%' 
    BEGIN
        SET @v_strListSQL = @v_strListSQL + ', TDEThumbpr DECIMAL'
        --PRINT @v_strServerVersion
    END

SET @v_strListSQL = @v_strListSQL + ')'

  BEGIN TRY
EXEC (@v_strListSQL)

INSERT INTO ##FILE_LIST EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @p_strFQNRestoreFileName + '''')

DECLARE curFileLIst CURSOR FOR 
    SELECT 'MOVE N''' + LogicalName + ''' TO N''' + replace(PhysicalName, LogicalName,      @p_strDBNameTo) + ''''
      FROM ##FILE_LIST
SET @v_strMoveSQL = ''

OPEN curFileList 
FETCH NEXT FROM curFileList into @v_strTEMP
WHILE @@Fetch_Status = 0
BEGIN
    SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + ', '
    FETCH NEXT FROM curFileList into @v_strTEMP
END

CLOSE curFileList
DEALLOCATE curFileList

PRINT 'Killing active connections to the "' + @p_strDBNameTo + '" database'

-- Create the sql to kill the active database connections
SET @v_strExecSQL = ''
SELECT   @v_strExecSQL = @v_strExecSQL + 'kill ' + CONVERT(CHAR(10), spid) + ' '
FROM     master.dbo.sysprocesses
WHERE    DB_NAME(dbid) = @p_strDBNameTo AND DBID <> 0 AND spid <> @@spid

EXEC (@v_strExecSQL)

PRINT 'Restoring "' + @p_strDBNameTo + '" database from "' + @p_strFQNRestoreFileName + '" with '
PRINT '  data file "' + @v_strDBDataFile + '" located at "' + @v_strDBFilename + '"'
PRINT '  log file "' + @v_strDBLogFile + '" located at "' + @v_strDBLogFilename + '"'

SET @v_strExecSQL = 'RESTORE DATABASE [' + @p_strDBNameTo + ']'
SET @v_strExecSQL = @v_strExecSQL + ' FROM DISK = ''' + @p_strFQNRestoreFileName + ''''
SET @v_strExecSQL = @v_strExecSQL + ' WITH FILE = 1,'
SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL
SET @v_strExecSQL = @v_strExecSQL + ' NOREWIND, '
SET @v_strExecSQL = @v_strExecSQL + ' NOUNLOAD '
SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE

--PRINT '---------------------------'
--PRINT @v_strExecSQL
--PRINT '---------------------------'

EXEC sp_executesql @v_strExecSQL

  RETURN 1
  END TRY
  BEGIN CATCH
  SELECT @v_intErr = @@ERROR,@v_intRetval = @v_intErr
        SET @v_strerrormsg = 'Restore of ' + @p_strFQNRestoreFileName + ' failed with Native Error : ' + CAST(@v_intErr as varchar(10)) + ', Error Message: ' +ERROR_MESSAGE() 
        RAISERROR(@v_strerrormsg,16,1) 
        RETURN 0
  END CATCH
 END
 GO

I am getting this issue in SQL Server 2008 R2 Express as well as SQL Server 2012 RTM.

share|improve this question

migrated from stackoverflow.com Sep 26 '13 at 9:10

This question came from our site for professional and enthusiast programmers.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.