Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

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.



    



Learn more about SQL Server tools