Wednesday, March 7, 2012

Retrieve the logical file name from a backup device

Hi,
I'm working on some "daemon" able to restore whatever come into a given
folder to a "stand-by" SQL Server (sort of log shipping, but also to restore
full backup and so on).
I'm now faing a problem : I can't put the logical name of the database from
the backup file to a variable.
Here a sample of what I 'm trying to do :
--
DECLARE @.Data_file nvarchar (256)
create table #result
(
LogicalName varchar(512),
PhysicalName varchar(512),
Type varchar(1),
FileGroupName nvarchar(50),
Size int,
Maxsize int
)
insert into #result EXEC (restore filelistonly from disk='X:\file.bak')
--
(please note that table declaration, variable delclaration, etc may be
weird, but it's purely test code to try validate my concept.
Each time I got the following error :
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'restore'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
As RESTORE filelistonly is, from BOL, returning a dataset, I was expecting
to be able to load into a table, or to be able to extract data from it
easily.
I do not know where to look at, any help will be welcome,
Thanks,
ChrisSee "RESTORE FILELISTONLY " in BOL.
AMB
"Chris V." wrote:

> Hi,
> I'm working on some "daemon" able to restore whatever come into a given
> folder to a "stand-by" SQL Server (sort of log shipping, but also to resto
re
> full backup and so on).
> I'm now faing a problem : I can't put the logical name of the database fro
m
> the backup file to a variable.
> Here a sample of what I 'm trying to do :
> --
> DECLARE @.Data_file nvarchar (256)
> create table #result
> (
> LogicalName varchar(512),
> PhysicalName varchar(512),
> Type varchar(1),
> FileGroupName nvarchar(50),
> Size int,
> Maxsize int
> )
> insert into #result EXEC (restore filelistonly from disk='X:\file.bak')
> --
> (please note that table declaration, variable delclaration, etc may be
> weird, but it's purely test code to try validate my concept.
> Each time I got the following error :
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'restore'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
>
> As RESTORE filelistonly is, from BOL, returning a dataset, I was expecting
> to be able to load into a table, or to be able to extract data from it
> easily.
> I do not know where to look at, any help will be welcome,
> Thanks,
> Chris
>
>|||Hi Alejandro,
That's what I;ve done, bout couldn't find the way to have the following
result :
SELECT @.DATE_FILE =SELECT LogicalName from EXEC(RESTORE FILELISTONLY from
DISK='File') where TYPE='D'
I'm desperate :)
Thanks,
Chris
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> a crit dans le
message de news:99C7DE87-8022-4F37-A797-F99296625772@.microsoft.com...
> See "RESTORE FILELISTONLY " in BOL.
>
> AMB
> "Chris V." wrote:
>
restore
from
expecting|||Try,
use northwind
go
create table #t (
LogicalName sysname,
PhysicalName sysname,
Type char(1),
FileGroupName sysname null,
[Size] bigint,
[MaxSize] bigint
)
insert into #t
execute sp_executesql N'use master restore filelistonly from mydump'
select
*
from
#t
drop table #t
go
AMB
"Chris" wrote:

> Hi Alejandro,
> That's what I;ve done, bout couldn't find the way to have the following
> result :
> SELECT @.DATE_FILE =SELECT LogicalName from EXEC(RESTORE FILELISTONLY from
> DISK='File') where TYPE='D'
> I'm desperate :)
> Thanks,
> Chris
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> a écrit dans l
e
> message de news:99C7DE87-8022-4F37-A797-F99296625772@.microsoft.com...
> restore
> from
> expecting
>
>|||Works perfectly !
Thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E686C7EB-957F-4A14-BEA2-6C86F80B1D3F@.microsoft.com...
> Try,
> use northwind
> go
> create table #t (
> LogicalName sysname,
> PhysicalName sysname,
> Type char(1),
> FileGroupName sysname null,
> [Size] bigint,
> [MaxSize] bigint
> )
> insert into #t
> execute sp_executesql N'use master restore filelistonly from mydump'
> select
> *
> from
> #t
> drop table #t
> go
>
> AMB
> "Chris" wrote:
>
from
le
given
database
disk='X:\file.bak')
be
it

No comments:

Post a Comment