DB Logical File Names - SQL Server Forums

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

Transact-SQL (2000)

DB Logical File Names

Author

Topic

cblatchford
Starting Member

40 Posts

Posted - 2007-06-19 : 05:53:44

Hi all,

I'm trying to write a query which selects the logical file names of databases. I know I can use:

RESTORE FILELISTONLY FROM DISK = 'f:\someDatabase.bak'

..to get the logical file names, but how would I put the result (logicalName) into a variable that I can use later? Somethin like..

DECLARE @DBLogical varchar(100)

SET @DBLogical = select logicalname from restore filelistonly..

Thanks..

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-19 : 05:55:01

see
http://www.nigelrivett.net/SQLAdmin/s_RestoreDatabase.html

It gets the logical file names and sets the path for a restore.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cblatchford
Starting Member

40 Posts

Posted - 2007-06-19 : 07:07:11

That's great thanks, but is there a quick and simple way to put the logicalname into a variable?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-19 : 07:49:19

Yes - and it's in that stoerd proc. It's not a single logical name, you have to loop through the set.

select @cmd = 'restore filelistonly from disk = ''' + @SourcePath + @filename + ''''
insert #files exec (@cmd)
select @s = ''

while @s < (select max(lname) from #files)
begin
select top 1 @s = lname, @t = type from #files where lname > @s order by lname
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cblatchford
Starting Member

40 Posts

Posted - 2007-06-19 : 08:01:12

Thanks again; I ended up doing the following, all I need to do now is add in the restore command and it should all work fine..

DECLARE @DB varchar(100),
@Backup_location varchar(100),
@cmd varchar(100)

DECLARE ICursor CURSOR
FOR
SELECT name from sysdatabases where name not in ('master','msdb','model','tempdb')
OPEN ICursor
CREATE TABLE #dbfiles(
LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0))

Fetch Next From ICursor Into @DB
WHILE(@@fetch_status = 0)
BEGIN
SET @Backup_Location = '\\LON-SQAPPS01\backup\'+@DB+'.BAK'
/*Backup Database @DB to disk = @Backup_Location with init*/

/*TRUNCATE TABLE #dbfiles*/
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @Backup_Location + '''')

Fetch Next From ICursor Into @DB
END

select * from #dbfiles

/*drop table #dbfiles*/

deallocate ICursor

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-19 : 08:10:22

The code you need is all in that SP I linked.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 08:26:12

In case it helps: there is a worked example of RESTORE Syntax here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources