Created
September 23, 2010 21:49
-
-
Save stevenharman/594445 to your computer and use it in GitHub Desktop.
Extract metadata (like Logical name, file paths, etc.) from a SQL Server backup file into an Array of Hashes.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
lol ! that is something special :)