Filelistonly
Aug 13, 2007
1-anyone know why there are 4 files in this bak file?
2-how do you create it?
3-how do you restore it?
thx
restore filelistonly
from disk = 'r:ackupIPGDB_BackupDevice.bak'
PRIMARYD:Program FilesMicrosoft SQL ServerMSSQLDataklxprodplanklxprodplan.mdfDPRIMARY5242880035184372080640
KLX_BASE_DATD:Program FilesMicrosoft SQL ServerMSSQLDataklxprodplanklxprodplan_1.mdfDKLX_BASE_DAT172772556835184372080640
KLX_DERIVED_DATD:Program FilesMicrosoft SQL ServerMSSQLDataklxprodplanklxprodplan_2.mdfDKLX_DERIVED_DAT7112294400035184372080640
LOGL:Program FilesMicrosoft SQL ServerMSSQLLogsklxprodplanklxprodplan_log.ldfLNULL164587110435184372080640
=============================
http://www.sqlserverstudy.com
View 9 Replies
Feb 25, 2000
I would like to use "RESTORE FILELISTONLY' in a stored procedure, but I do not know how to retrieve the result set that it makes up. I need to know the names of files contained in the backup set, so I can do a specific restore.
Thanks,
Judith
View 4 Replies
View Related
Jul 23, 2005
Hello all. Does anyone know if a successful completion of a 'restorefilelistonly' command would indicate that a backup file is valid? I'venoticed some of our backup jobs failing during the verify phase of themaintenenace plan because of network issues, and I'd like a quick way tocheck if the backup is valid because some of the backup files take hours toverify. I searched MS Support and they don't seem to have any info on this.TW
View 1 Replies
View Related
Jan 28, 2008
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 FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMALIK.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.
View 5 Replies
View Related
Jul 21, 2003
When moving around 800 databases from 40 MSDE servers to on STD SQL2k server,
I need the logicalfile name info in order to run the script to restore from backup device. The only place I could get from the target server is the backup device(which are copied from 40 MSDE to STD). It works fine and retuns value from "restore filelistonly from backup_Testing".
But fails when I tried to put the result into a table variable or a temp #Table.
declare @tableFileList
table (LogicalName nvarchar(128), PhysicalName nvarchar(260), Type char(1), FileGroupName nvarchar(128), Size numeric(20,0), MaxSize numeric(20,0))
insert into @tableFileList
restore filelistonly from backup_Testing
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'restore'.
thanks
David
View 8 Replies
View Related
Jan 20, 2005
When I try to do a restore filelistonly I get the following message
[Server: Msg 3624, Level 20, State 1, Line 1
Location: upgraddb.cpp:214
Expression: tableIndex < ARRAY_LEN (upgradeMap)
SPID: 8
Process ID: 216]
I get the same when I try a restore database
The instance is on a xp Pro local desktop which is only new. No database has been restored yet on this instance.
But everything seems to work fine on this instance e.g Northwind, xp_cmdshell etc.
Any help??
Thanks
View 2 Replies
View Related
Jun 30, 2006
Hello,
Would some one please help me with the syntex on how to run "restore filelistonly" or restore verifyonly" on a SQL backup which has multiple filesets?? My backups locations are as follow:
RESTORE VERIFYONLY
From disk = 'E:syndicated_databank__bkup_01.bak',
'E:syndicated_databank__bkup_02.bak',
€˜E:syndicated_databank__bkup_03.bak€™,
€˜E:syndicated_databank__bkup_04.bak€™, €˜E:syndicated_databank__bkup_05.bak€™
I tried to do a restore with the above, I got error The label 'E' has already been declared. Label names must be unique within a query batch or stored procedure.
Please advise!!
View 3 Replies
View Related
