Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Unanswered: RESTORE FILELIST is terminating abnormally

    'm a noob, using MS SQL 2005. Trying to use a stored procedure I got from SQL 2000 DB integrity to work with 2005. Receiving:


    Message
    Executed as user: MMCRSDOM\teletracksql. Insert Error: Column name or number of supplied values does not match table definition. [SQLSTATE 21S01] (Error 213) RESTORE FILELIST is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

    Please help.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Post the sproc ... not enough information to determine a solution.

    RESTORE FILELIST is used to list the database and log files contained in a backup set.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jun 2007
    Posts
    2
    USE [master]
    GO
    /****** Object: StoredProcedure [dbo].[restore_database] Script Date: 06/21/2007 13:17:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[restore_database]

    @source_serv NVARCHAR(260),
    @source_db NVARCHAR(260),
    @destination_db NVARCHAR(260)

    AS

    DECLARE @backup_name NVARCHAR(260)

    DECLARE @logical_db_name NVARCHAR(260)
    DECLARE @logical_tl_name NVARCHAR(260)

    DECLARE @physical_db_name NVARCHAR(260)
    DECLARE @physical_tl_name NVARCHAR(260)

    DECLARE @cmd NVARCHAR(1000)

    SET @cmd = 'SELECT TOP 1 physical_device_name
    FROM msdb.dbo.backupmediafamily AS F, msdb.dbo.backupset AS S
    WHERE F.media_set_id = S.media_set_id AND S.database_name = ''' + @source_db + ''' AND S.type='+ '''D''' + '
    ORDER BY S.backup_start_date DESC'

    CREATE TABLE #remote(backup_name nvarchar(500))

    INSERT #remote (backup_name) EXEC(@cmd)

    SET @backup_name = (SELECT backup_name FROM #remote )

    DELETE #remote

    SET @backup_name= CHAR(39) + '\\' + @source_serv + '\' + replace(@backup_name, ':', '$') + CHAR(39) + ' '

    CREATE TABLE #dbfiles(
    LogicalName nvarchar(128)
    ,PhysicalName nvarchar(260)
    ,Type char(1)
    ,FileGroupName nvarchar(128)
    ,Size numeric(20,0)
    ,MaxSize numeric(20,0))

    INSERT #dbfiles EXEC('RESTORE FILELISTONLY FROM DISK = ' + @backup_name)

    SELECT 'Backup set...',@backup_Name

    SET @logical_db_name = ' ' + ltrim(rtrim(CHAR(39) + (SELECT logicalname FROM #dbfiles WHERE type='D')))+ CHAR(39) + ' '
    SET @logical_tl_name = ' ' + ltrim(rtrim(CHAR(39) + (SELECT logicalname FROM #dbfiles WHERE type='L')))+ CHAR(39) + ' '

    DROP TABLE #dbfiles

    SET @cmd = 'SELECT [filename] FROM [' + @destination_db + '].dbo.sysfiles WHERE fileid = 1'
    INSERT #remote EXEC(@cmd)
    SET @physical_db_name = ' ' + ltrim(rtrim(CHAR(39) + (SELECT * FROM #remote)))+ CHAR(39) + ' '
    DELETE #remote

    SET @cmd = 'SELECT [filename] FROM [' + @destination_db + '].dbo.sysfiles WHERE fileid = 2'
    INSERT #remote EXEC(@cmd)
    SET @physical_tl_name = ' ' + ltrim(rtrim(CHAR(39) + (SELECT * FROM #remote)))+ CHAR(39) + ' '

    DROP TABLE #remote

    SELECT @physical_db_name, @physical_tl_name

    DECLARE @sql nvarchar(1000)

    SET @sql = 'RESTORE DATABASE ' + @destination_db + ' FROM DISK = ' + @backup_name + ' WITH RECOVERY , REPLACE , MOVE' +
    @logical_db_name + 'TO' + @physical_db_name + ', MOVE' + @logical_tl_name + 'TO' + @physical_tl_name

    EXEC (@sql)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •