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''')

select
fileid
, 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
from
@filelist_from_backup_file
order by
size desc
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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