mssqltips logo

SQL Server RESTORE FILELISTONLY



By:

Overview

The RESTORE FILELISTONLY option allows you to see a list of the files that were backed up.  So for example if you have a full backup you will see all of the data files (mdf) and the log file (ldf).

Explanation

This information can only be returned using T-SQL there is not a way to get this information from SQL Server Management Studio. Although if you do a restore and select options, you will see some of this information in SSMS.

The RESTORE FILELISTONLY option can be simply issued as follows for a backup that exists on disk.  If there are multiple backups in one file and you do not specify "WITH FILE = X" you will only get information for the first backup in the file.  To get the FILE number use RESTORE HEADERONLY and use the "Position" column.


Get filelistlonly information from a backup file

T-SQL

RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 1
GO

The result set would like the following.  The things that are helpful here include the LogicalName and PhysicalName.

ColumnName Value - Row 1 Value - Row2
LogicalName AdventureWorks_Data AdventureWorks_Log
PhysicalName C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf
Type D L
FileGroupName PRIMARY NULL
Size 202113024 153092096
MaxSize 35184372080640 2199023255552
FileId 1 2
CreateLSN 0 0
DropLSN 0 0
UniqueId 50A534B0-156C-42B7-82FE-A57D21A53EEA 4F544777-6DBB-4BBC-818A-72C0B878610C
ReadOnlyLSN 0 0
ReadWriteLSN 0 0
BackupSizeInBytes 177012736 0
SourceBlockSize 512 512
FileGroupId 1 0
LogGroupGUID NULL NULL
DifferentialBaseLSN 0 0
DifferentialBaseGUID 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000
IsReadOnly 0 0
IsPresent 1 1

Last Update: 3/17/2009




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools