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 2000 Forums
 SQL Server Administration (2000)
 RESTORE FILELISTONLY

Author  Topic 

noamg
Posting Yak Master

215 Posts

Posted - 2004-07-01 : 04:24:34
hi,

The 'RESTORE FILELISTONLY' output is a resultset.
how to insert it into my table ?


Noam Graizer

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-01 : 04:48:58
Hi Naom,

this is a bit more than you are asking for, but inside here, I do am insert into table from a restore filelistonly.


if exists (select 'SP exists' from sysobjects where name = 'usp_autorestore_backups' and xtype = 'p')
drop proc usp_autorestore_backups
go
create procedure usp_autorestore_backups
@movepathdata varchar(255),
@movepathlog varchar(255),
@backuppath varchar(255)
-- Create By: Regan Galbraith
-- Create On: 2004-06-24
-- Purpose:
-- This stored procedure was written to facilitate the automatic restore of databases.
-- It relies upon, and helps enforce, certain standards, for example the naming standard for
-- data files that it creates, and the log files. It relies upon a namiing standard for backups.
--
-- Currently, the environment that I am in has a naming standard that enforces no _'s in the
-- database names. This allows me to do the charindex('_') searching I do to split out the DB name
-- from a backup. If you environment allows _'s in the DB name, consider changing this to a
-- patindex('_x_') or something like that, and then ensuring that it is used consistently in the backup
-- creation. You could even implement that as a input variable.
--
-- Example:
-- exec usp_autorestore_backups 'd:\data\','d:\log\','d:\backups\'
--
-- Possible future additions:
-- 1> stripping out date of backup file creation ... maybe by removing /b from dir, and then
-- accepting an input variable for date, to restore only certain backups
-- 2> accepting a parameter instead of apply the default .bak. Use the .bak as default
-- 3> implementing default value's for dir's, so that it can run without parm's ... good or bad?
--
-- Change Control: version 1 - creation and adding of comment
--
as
begin
set nocount on
set quoted_identifier off

create table #backuplist(backupname varchar(128))

create table #filelistinfo
(LogicalName sysname null,
PhysicalName sysname null,
Type varchar(20) null,
FileGroupName sysname null,
FileSize bigint null ,
FileMaxSize Bigint null)

create table #direxists
(FileExists int,
FileIsDir int,
ParentDirExists int)

declare @database sysname
--declare @dbstatement varchar(255)
declare @restoreSQL varchar(510)
declare @filelistSQL varchar(255)
declare @datafile varchar(255)
declare @logfile varchar(255)
--declare @backupsubstring varchar(255)
--declare @movepathdata varchar(255)
--declare @movepathlog varchar(255)
--declare @movepathdocument varchar(255)
--declare @dirliststring varchar(255)
declare @backupname varchar(255)
--declare @backuppath varchar(255)
declare @dirSQL varchar(255)
--declare @filexists int
declare @succeed int
declare @ErrorDir varchar(6)

--set @movepathdata = 'd:\data\'
insert into #direxists exec master..xp_fileexist @movepathdata
if not exists(select 'Dir Exists' from #direxists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
begin
set @ErrorDir = 'Data'
goto DirError
end
else
begin
delete from #direxists
-- set @movepathlog = 'd:\log\'
insert into #direxists exec master..xp_fileexist @movepathlog
if not exists(select 'Dir Exists' from #direxists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
begin
set @ErrorDir = 'Log'
goto DirError
end
else
begin
delete from #direxists
-- set @backuppath = 'd:\backups\'
insert into #direxists exec master..xp_fileexist @backuppath
if not exists(select 'Dir Exists' from #direxists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
begin
set @ErrorDir = 'Backup'
goto DirError
end
end
end

set @dirSQL = 'dir '+@backuppath+'*.bak /b'
--print @dirSQL

delete from #backuplist
insert into #backuplist
exec master..xp_cmdshell @dirSQL
--select * from #backuplist

declare backuplist cursor for select backupname from #backuplist

open backuplist
fetch next from backuplist into @backupname

while @@fetch_status <> -1
begin
-- this assumes a naming standard where your back names are databasename_ and then more detail. If you have
-- a naming standard for backups of simply databasename.bak, then change this to charindex ('.' , for example.

set @database = left(@backupname,(charindex('_',@backupname)-1))
set @filelistSQL = 'restore filelistonly '--+@database
set @filelistSQL = @filelistSQL + ' from disk = '''+@backuppath+@backupname+''''
-- print @filelistSQL
insert into #filelistinfo exec (@filelistSQL)
select @datafile=LogicalName from #filelistinfo where type = 'D'
-- print @datafile
select @logfile=LogicalName from #filelistinfo where type = 'L'
-- print @logfile
set @restoreSQL = 'restore database '+@database
set @restoreSQL = @restoreSQL + ' from disk = '''+@backuppath+@backupname
set @restoreSQL = @restoreSQL + ''' with replace,stats,move '''+@datafile
set @restoreSQL = @restoreSQL + ''' to '''+@movepathdata + @database+'data.mdf'', move '''
set @restoreSQL = @restoreSQL + @logfile+''' to '''+@movepathlog+@database+'log.ldf'''
delete from #filelistinfo
print @restoreSQL
exec (@restoreSQL)
fetch next from backuplist into @backupname
end

close backuplist
deallocate backuplist
goto finish

DirError:
Print 'An error with the '@errordir+' Directory given - please check that it exists. Thanks'
drop table #filelistinfo
drop table #backuplist
drop table #direxists
RETURN -1

Finish:
drop table #filelistinfo
drop table #backuplist
drop table #direxists
RETURN 0

end

--exec usp_autorestore_backups 'd:\data\','d:\log\','d:\backups\'


*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-01 : 06:16:15
[code]
DECLARE @strSQL varchar(8000),
@MyRestoreFilePath varchar(8000)

SELECT @MyRestoreFilePath = 'D:\MSSQL\BACKUP\MyFileName.BAK'

CREATE TABLE #TEMP_TABLE
(
LogicalName nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PhysicalName nvarchar(260) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Type char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
FileGroupName nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Size] numeric(20,0) NULL,
[MaxSize] numeric(20,0) NULL
)

SELECT @strSQL = 'RESTORE FILELISTONLY FROM DISK = ''' + @MyRestoreFilePath + ''''

INSERT INTO #TEMP_TABLE
exec(@strSQL)

SELECT * FROM #TEMP_TABLE
[/code]
Kristen
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-01 : 06:28:30
I was too lazy to strip that out of my SP :-)

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-01 : 06:57:08
I was lucky that that's how it looked in mine - bar a Find&Replace to make the names more friendly and bunging a DECLARE on the front ...

Kristen
Go to Top of Page

supersql
Yak Posting Veteran

99 Posts

Posted - 2009-03-12 : 18:53:54
How can i do autorestore in sql 2005 if i have 15 file groups and 12 ndf files in each group.

thanks
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-12 : 21:44:23
quote:
Originally posted by supersql

How can i do autorestore in sql 2005 if i have 15 file groups and 12 ndf files in each group.

thanks




Did you use filegroup backup or full backup?
Go to Top of Page
   

- Advertisement -