question

jctronicsshiva avatar image
jctronicsshiva asked

,Restore filelist is terminating abnormally

Hello, i am trying to restore a backup using the below stored procedure in 2008r2. But i am getting the following error Restore filelist is terminating abnormally.. can anyone help me on this ? 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
sql-server-2008t-sqlsql-server-2012restore
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
There is an extra column returned from RESTORE FILELIST that you don't have in your temp table definition : TDEThumbPrint. You are suppressing the real error message in your catch block!
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

you will have to check the sql server version and create a temporary table of the appropriate shape to match the sql version
1 Like 1 ·
I added that , now it works in some of the PCS, in some of them i get the follwoing error code: eInternalFailure message: System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
0 Likes 0 ·
I need this working in 2008 as well as 2012 .. any suggetions ??
0 Likes 0 ·
rcamarda390 avatar image
rcamarda390 answered
I got error using SQL Server 2017. Turns out SnapshotURL nvarchar(360) for ##FILE_LIST is missing.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.