Agent Jobs

Get File Group Size From Backup File

Here’s a quick script to get the Data File Size using just the backup file. Basically; this is good to do before you run a Database restore so you can see where you need to place the files on your drives. Even though the restore will tell you if you are low on space ( as an error ). Additionally; if you’re like me you’ll probably want to write out all the logic for the restore. The results of this script will help you see all the data file lists so your code is consistent.

use master;
set nocount on

declare @filelist_from_backup_file table
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
[Type] char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnl bit,
IsPresent bit,
TDEThumbprint varbinary(32) -- remove this column if using SQL 2005 )
insert into @filelist_from_backup_file
exec('restore filelistonly from disk = ''\\MyBackupShare\MyBackupFile.bak''')

, logicalname
, 'size in gb' = ( convert(decimal(10,2),size / 1024 / 1024 / 1024))
, 'bu size in gb' = ( convert(decimal(10,2),backupsizeinbytes / 1024 / 1024 / 1024 )) , 'physical path' = physicalname
order by
size desc

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s