RESTORE FILELIST is terminating abnormally.

• Shahzadi

  Default port

  Points: 1479

  More actions

  January 28, 2008 at 10:52 am

  #186012

  Hi Guys.

  i have write a store procedure which take few input and then backup the database and at the same time it's restore the database with new name, but i m hving a error code.

  what this program do in restore section, it's read the backup file and all give me list of all the file with the location and then i can rename them.

  actually the purpose of doing this is to create a new database on behalf of old database. plz have alook code

  PLZ, PLZ help me, it's really geting headach

  USE [master]

  GO

  /****** Object: StoredProcedure [dbo].[CreateNewDB] Script Date: 01/28/2008 17:13:09 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER proc [dbo].[CreateNewDB]

  @ActualDb varchar(128),

  @dbnamesysname ,

  @recipientsvarchar(128)

  AS

  SET NOCOUNT ON

  Declare @cmd sysname ,

  @filenamevarchar(128) ,

  @Backuppath varchar(1000),

  @LogicalName varchar(2000),

  @ActualPath varchar(2000),

  @Aloop int,

  @FileID int,

  @sqlnvarchar(4000)

  SET @Backuppath = 'C:\' + @dbname

  -- TAKE BACKUP

  BACKUP DATABASE @ActualDb TO DISK = @Backuppath WITH NOFORMAT, INIT, NAME = 'DBBackup-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

  -- Get files in backup

  select @cmd = 'restore filelistonly from disk = ''' + @Backuppath + ''''

  CREATE table #RestoreFileListOnly

  (

  LogicalName sysname,

  PhysicalName sysname,

  type char(1),

  FileGroupName sysname,

  bigint,

  [MaxSize] bigint,

  FileID int

  )

  INSERT into #RestoreFileListOnly

  exec(@cmd)

  -- buld the restore command

  set @Aloop=1

  set @FileID=0

  set @sql= ''

  set @sql = @sql + 'RESTORE DATABASE ' + @dbname + CHAR(10)

  set @sql = @sql + ' FROM DISK = ''' + @Backuppath + '''' + CHAR(10)

  set @sql= @sql + ' WITH FILE = 1' + CHAR(10)

  WHILE (@aloop <= @@ROWCOUNT)

  BEGIN

  SELECT @LogicalName = LogicalName , @FileID = FileID, @ActualPath = Left(PhysicalName, len(PhysicalName)-charindex('\',reverse(PhysicalName))+1) FROM #RestoreFileListOnly WHERE FILEID > @FileID

  SET @sql= @sql + ',' + CHAR(10)

  SET @sql= @sql + CHAR(9) + 'MOVE''' + @LogicalName + '''TO''' + @ActualPath + '''' + @dbname + ''''

  --@sql= @sql + 'MOVE '''+ + '' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\MALIK.mdf'

  SET @Aloop=@Aloop+1

  END

  SET @sql = @sql + ', NOUNLOAD, STATS = 10'

  -- Restore the database

  print @sql

  EXEC (@sql)

  Drop table #RestoreFileListOnly

  -- send email to the define person.

  EXEC master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername

  ERROR:

  Msg 213, Level 16, State 7, Line 1

  Insert Error: Column name or number of supplied values does not match table definition.

  Msg 3013, Level 16, State 1, Line 1

  RESTORE FILELIST is terminating abnormally.

• Paul Randal

  One Orange Chip

  Points: 29544

  More actions

  January 28, 2008 at 8:10 pm

  #772714

  The problem is that your temp table doesn't contain all 20 columns that the output from RESTORE FILELISTONLY has (actually 21 in SS2008). Checkout the BOL for RESTORE FILELISTONLY for the details - your temp table has to have the exact same schema as the command output. Once you've done that it'll work fine.

  Paul Randal
  CEO, SQLskills.com: Check out SQLskills online training!
  Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
  SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
  Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005