Extract metadata (like Logical name, file paths, etc.) from a SQL Server backup file into an Array of Hashes.

sqlserver_backup_info.rb

# Extract metadata (like Logical name, file paths, etc.) from a SQL Server backup file into an Array of Hashes.
# This assumes you're running on Windows and have SQL Server installed locally.
# example usage:
# 
# require 'sqlserver_backup_info'
#
# bak = File.expand_path('../MyBackup.bak', __FILE__)
# info = extract_info bak
# info.each { |meta| puts meta[:logical_name] }
#

def extract_info(backup)
  bak = backup.gsub('/','\\') # SqlCmd requires Windows slashes.
  files = eval(`sqlcmd -h-1 -y 0 -Q "#{Backup_Info_Query}" -v bakfile="#{bak}"`)
end

Backup_Info_Query <<EOF
SET NOCOUNT ON
DECLARE @FileList TABLE
(
  LogicalName nvarchar(128) NOT NULL,
  PhysicalName nvarchar(260) NOT NULL,
  Type char(1) NOT NULL,
  FileGroupName nvarchar(120) NULL,
  Size numeric(20, 0) NOT NULL,
  MaxSize numeric(20, 0) NOT NULL,
  FileID bigint NULL,
  CreateLSN numeric(25,0) NULL,
  DropLSN numeric(25,0) NULL,
  UniqueID uniqueidentifier NULL,
  ReadOnlyLSN numeric(25,0) NULL ,
  ReadWriteLSN numeric(25,0) NULL,
  BackupSizeInBytes bigint NULL,
  SourceBlockSize int NULL,
  FileGroupID int NULL,
  LogGroupGUID uniqueidentifier NULL,
  DifferentialBaseLSN numeric(25,0)NULL,
  DifferentialBaseGUID uniqueidentifier NULL,
  IsReadOnly bit NULL,
  IsPresent bit NULL,
  TDEThumbprint varbinary(32) NULL
)

insert into @FileList
exec('RESTORE FILELISTONLY FROM DISK=''$(bakfile)''')

declare @ruby varchar (max)
select @ruby='['
select @ruby=@ruby + '{'+
 ':file_id=>' + cast(FileID as varchar(10)) + ',' +
 ':logical_name=>"' + LogicalName + '",' +
 ':physical_name=>"' + replace(PhysicalName, '\', '\\') + '",' +
 ':type=>"' + Type + '",' +
 ':file_group_name=>"' + isnull(FileGroupName, '') + '",' +
 ':file_group_id=>' + cast(FileGroupID as varchar(10)) +
 '},'
from @FileList

select @ruby=@ruby+']'

select @ruby

EOF

it's both disgusting that this is possible, and that I actually did it. I feel dirty.

lol ! that is something special :)