RESTORE FILELIST is terminating abnormally

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

RESTORE FILELIST is terminating abnormally

Author

Topic

malikjaved
Starting Member

21 Posts

Posted - 2008-01-28 : 12:49:58

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),

@dbname sysname ,

@recipients varchar(128)

AS

SET NOCOUNT ON

Declare @cmd sysname ,

@filename varchar(128) ,

@Backuppath varchar(1000),

@LogicalName varchar(2000),

@ActualPath varchar(2000),

@Aloop int,

@FileID int,

@sql nvarchar(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,

[size] 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.

Haywood
Posting Yak Master

221 Posts

Posted - 2008-01-28 : 13:29:33

Your table that holds the RESTORE FILELISTONLY results is incomplete....


CREATE TABLE #RestoreFileListOnly
	(
	 LogicalName nvarchar(128)
	,Old_PhysicalName nvarchar(128)
	,[Type] char(1)
	,FileGroupName nvarchar(128)
	,[Size] numeric(20,0)
	,[MaxSize] numeric(20,0)
	,FileID bigint
	,CreateLSN numeric(25,0)
	,DropLSN numeric(25,0) NULL
	,UniqueID uniqueidentifier
	,ReadOnlyLSN numeric(25,0)
	,ReadWriteLSN numeric(25,0)
	,BackupSizeInByte bigint
	,SourceBlockSize int
	,FilegroupID int
	,LogGroupGUID uniqueidentifier NULL
	,DifferentialBaseLSN numeric(25,0)
	,DifferentialbaseGUID uniqueidentifier
	,IsReadOnly bit
	,IsPresent bit
	)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources