Wyodrębnianie pola z PRZYWRACANIA HEADERONLY

12

Próbuję użyć opcji „ PRZYWRÓĆ HEADERONLY ”, aby uzyskać datę utworzenia kopii zapasowej, którą zamierzam przywrócić.

Komenda:

RESTORE HEADERONLY FROM DISK = '<path to .bak file>'

działa dobrze w Query Analyzer i daje zestaw wyników z około 50 kolumnami.

Problemem jest dostęp do tego z kodu.

Mogę wprowadzić to do tabeli tymczasowej, deklarując każdą z 50 kolumn: ish, wstawiając do niej execi pobierając z niej żądaną wartość.

Problem polega na tym, że naprawdę chcę uniknąć deklarowania całego zestawu wyników jako tabeli tymczasowej, ponieważ wydaje się, że jest to bardzo kruche rozwiązanie, jeśli kiedykolwiek dodadzą do niego kolumny w przyszłych wersjach.

Czy jest jakiś sposób na wyciągnięcie jednej kolumny z tego zestawu wyników bez deklarowania wszystkich kolumn?

sql-server  sql-server-2008-r2  restore 
— Alun
źródło

Odpowiedzi:

12

To działa dla mnie.

SELECT BackupStartDate 
FROM OPENROWSET('SQLNCLI',
                'Server=MARTINPC\MSSQL2008;Trusted_Connection=yes;',
'SET NOCOUNT ON;SET FMTONLY OFF;EXEC(''
RESTORE HEADERONLY 
FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')'
)

Opcja rozproszonych zapytań ad hoc musi być włączona. Lub jeśli nie chcesz tego zrobić, możesz skonfigurować serwer połączony w pętli zwrotnej i użyć go zamiast tego.

EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                        @provider = 'SQLOLEDB', @datasrc = @@servername

SELECT BackupStartDate 
FROM OPENQUERY(LOCALSERVER, 
               'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY 
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')')
— Martin Smith
źródło
Sprytne i dziękuję za dzielenie się, ale tylko za nagranie, myślę, że jest to tak samo kruche / złożone, jak na końcu duża lista kolumn. Szkoda, że ​​nie ma eleganckiego rozwiązania.
— Tim Abell
@TimAbell - Tak, nie sądzę, że użyłbym tego w praktyce, chyba że po pierwsze, by uzyskać definicję tabeli.
— Martin Smith
1
Nie udało mi się sprawić, by oba zapytania działały. Czy ktoś inny otrzymuje komunikat o błędzie „Nie można ustalić metadanych, ponieważ instrukcja ODZYSKANIE SŁUCHAWEK ... nie obsługuje wykrywania metadanych”? Uważam, że przyczyną jest sp_describe_first_result_setsystem sp. Ja również podniósł tę kwestię jako oddzielnego biletu tutaj
— Stackoverflowuser
@Stackoverflowuser - wygląda na to, że pierwotnie przetestowałem to na instancji z 2008 roku, MARTINPC\MSSQL2008więc może coś się zmieniło w późniejszych wersjach, co oznacza, że ​​to już nie działa.
— Martin Smith
1
@Stackoverflowuser, powyższy przykład działa, gdy serwer @@ wersja <2012. Począwszy od 2012 r., Zamiast wykonywania tego zapytania, można zobaczyć w Profiler ten: exec [sys] .sp_describe_first_result_set N'SET FMTONLY OFF; EXEC ('' PRZYWRACANIE SŁUCHAWEK Z DYSKU = '' 'C: \ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQL2008 \ MSSQL \ Backup \ DB1.bak' '' ')), NULL, 1
— sepupic
7

Jest to niezależna od wersji sp, którą napisałem, aby uzyskać datę kopii zapasowej z pliku.

Jest testowany pod kątem SQL 2008R2, 2012 i 2014.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spGetBackupDateFromFile')
    EXEC ('CREATE PROC dbo.spGetBackupDateFromFile AS SELECT ''stub version, to be replaced''')
GO
/*----------------------------------------------------------------------
                    spGetBackupDateFromFile
------------------------------------------------------------------------
Versie      : 1.0
Autheur     : Theo Ekelmans 
Datum       : 2016-03-31
Change      : Initial release 
------------------------------------------------------------------------*/
alter procedure dbo.spGetBackupDateFromFile(@BackupFile as varchar(1000), @DT as datetime output) as 

declare @BackupDT datetime
declare @sql varchar(8000)
declare @ProductVersion NVARCHAR(128)
declare @ProductVersionNumber TINYINT

SET @ProductVersion = CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductVersion'))
SET @ProductVersionNumber = SUBSTRING(@ProductVersion, 1, (CHARINDEX('.', @ProductVersion) - 1))

if object_id('dbo.tblBackupHeader') is not null drop table dbo.tblBackupHeader

set @sql = ''

-- THIS IS GENERIC FOR SQL SERVER 2008R2, 2012 and 2014
if @ProductVersionNumber in(10, 11, 12)
set @sql = @sql +'
create table dbo.tblBackupHeader
( 
    BackupName varchar(256),
    BackupDescription varchar(256),
    BackupType varchar(256),        
    ExpirationDate varchar(256),
    Compressed varchar(256),
    Position varchar(256),
    DeviceType varchar(256),        
    UserName varchar(256),
    ServerName varchar(256),
    DatabaseName varchar(256),
    DatabaseVersion varchar(256),        
    DatabaseCreationDate varchar(256),
    BackupSize varchar(256),
    FirstLSN varchar(256),
    LastLSN varchar(256),        
    CheckpointLSN varchar(256),
    DatabaseBackupLSN varchar(256),
    BackupStartDate varchar(256),
    BackupFinishDate varchar(256),        
    SortOrder varchar(256),
    CodePage varchar(256),
    UnicodeLocaleId varchar(256),
    UnicodeComparisonStyle varchar(256),        
    CompatibilityLevel varchar(256),
    SoftwareVendorId varchar(256),
    SoftwareVersionMajor varchar(256),        
    SoftwareVersionMinor varchar(256),
    SoftwareVersionBuild varchar(256),
    MachineName varchar(256),
    Flags varchar(256),        
    BindingID varchar(256),
    RecoveryForkID varchar(256),
    Collation varchar(256),
    FamilyGUID varchar(256),        
    HasBulkLoggedData varchar(256),
    IsSnapshot varchar(256),
    IsReadOnly varchar(256),
    IsSingleUser varchar(256),        
    HasBackupChecksums varchar(256),
    IsDamaged varchar(256),
    BeginsLogChain varchar(256),
    HasIncompleteMetaData varchar(256),        
    IsForceOffline varchar(256),
    IsCopyOnly varchar(256),
    FirstRecoveryForkID varchar(256),
    ForkPointLSN varchar(256),        
    RecoveryModel varchar(256),
    DifferentialBaseLSN varchar(256),
    DifferentialBaseGUID varchar(256),        
    BackupTypeDescription varchar(256),
    BackupSetGUID varchar(256),
    CompressedBackupSize varchar(256),'

-- THIS IS SPECIFIC TO SQL SERVER 2012
if @ProductVersionNumber in(11)
set @sql = @sql +'
    Containment varchar(256),'


-- THIS IS SPECIFIC TO SQL SERVER 2014
if @ProductVersionNumber in(12)
set @sql = @sql +'
    Containment tinyint, 
    KeyAlgorithm nvarchar(32), 
    EncryptorThumbprint varbinary(20), 
    EncryptorType nvarchar(32),'


--All versions (This field added to retain order by)
set @sql = @sql +'
    Seq int NOT NULL identity(1,1)
); 
'
exec (@sql)


set @sql = 'restore headeronly from disk = '''+ @BackupFile +'''' 

insert into dbo.tblBackupHeader 
exec(@sql)

select @DT = BackupStartDate from dbo.tblBackupHeader 

if object_id('dbo.tblBackupHeader') is not null drop table dbo.tblBackupHeader
— użytkownik3742089
źródło
1
Zgodnie z stackoverflow.com/a/31318785/489865 i support.microsoft.com/en-us/kb/3058865 , KeyAl algorytm / EncryptorThumbprint / EncryptorType dodany dla „SQL SERVER 2014” faktycznie pojawił się tylko w SP1. Uważam, że jest to wersja kompilacji 12.0.4100.1, więc kod powinien sprawdzać wszystkie pola, SERVERPROPERTY('ProductVersion')aby poprawnie to zaspokoić.
— JonBrave,
7

Ponieważ zapytałeś tylko o dostęp do danych z „kodu” bez podania żadnych szczegółów, jakiego rodzaju kodu, niniejszym przedstawiam rozwiązanie PowerShell :

Invoke-SQLcmd -Query "RESTORE HEADERONLY FROM DISK = 'R:\SQLFiles\MSSQL.MSSQLSERVER.Backup\Backup.bak'" | Select-Object MachineName,DatabaseName,HasBackupChecksums,BackupStartDate,BackupFinishDate
— Christoph Wegener
źródło
1
Jest to nawet lepsze, ponieważ możemy zrobić coś takiego jak `ls | % {$ _. pełna nazwa} | % {invoke-sqlcmd -Query "PRZYWRÓCIĆ HEADERONLY Z DYSKU = '$ _'"} | format-table `
— Luiz Felipe
6

W staromodny sposób:

declare @backupFile varchar(max) = 'C:\backupfile.bak';
declare @dbName varchar(256);

-- THIS IS SPECIFIC TO SQL SERVER 2012
--
declare @headers table 
( 
    BackupName varchar(256),
    BackupDescription varchar(256),
    BackupType varchar(256),        
    ExpirationDate varchar(256),
    Compressed varchar(256),
    Position varchar(256),
    DeviceType varchar(256),        
    UserName varchar(256),
    ServerName varchar(256),
    DatabaseName varchar(256),
    DatabaseVersion varchar(256),        
    DatabaseCreationDate varchar(256),
    BackupSize varchar(256),
    FirstLSN varchar(256),
    LastLSN varchar(256),        
    CheckpointLSN varchar(256),
    DatabaseBackupLSN varchar(256),
    BackupStartDate varchar(256),
    BackupFinishDate varchar(256),        
    SortOrder varchar(256),
    CodePage varchar(256),
    UnicodeLocaleId varchar(256),
    UnicodeComparisonStyle varchar(256),        
    CompatibilityLevel varchar(256),
    SoftwareVendorId varchar(256),
    SoftwareVersionMajor varchar(256),        
    SoftwareVersionMinor varchar(256),
    SoftwareVersionBuild varchar(256),
    MachineName varchar(256),
    Flags varchar(256),        
    BindingID varchar(256),
    RecoveryForkID varchar(256),
    Collation varchar(256),
    FamilyGUID varchar(256),        
    HasBulkLoggedData varchar(256),
    IsSnapshot varchar(256),
    IsReadOnly varchar(256),
    IsSingleUser varchar(256),        
    HasBackupChecksums varchar(256),
    IsDamaged varchar(256),
    BeginsLogChain varchar(256),
    HasIncompleteMetaData varchar(256),        
    IsForceOffline varchar(256),
    IsCopyOnly varchar(256),
    FirstRecoveryForkID varchar(256),
    ForkPointLSN varchar(256),        
    RecoveryModel varchar(256),
    DifferentialBaseLSN varchar(256),
    DifferentialBaseGUID varchar(256),        
    BackupTypeDescription varchar(256),
    BackupSetGUID varchar(256),
    CompressedBackupSize varchar(256),        
    Containment varchar(256),
    --
    -- This field added to retain order by
    --
    Seq int NOT NULL identity(1,1)
); 

insert into @headers exec('restore headeronly from disk = '''+ @backupFile +'''');
select @dbName = DatabaseName from @headers;
select @dbName;
— ryscl
źródło
1
Aby to działało w SQL2014, musisz mieć te dodatkowe pola na końcu tabeli:, Maleńka przechowująca, KeyAl algorytm nvarchar (32), EncryptorThumbprint varbinary (20), EncryptorType nvarchar (32)
— Mike
Jest też ta odpowiedź , która nie używa varchar do wszystkiego, i zawiera dodatkowe kolumny dla SQL Server 2014.
— Baodad
Należy zauważyć, że te dodatkowe kolumny zostały faktycznie dodane w dodatku SP1 dla programu SQL 2014 . Wierzę, że jest to wersja kompilacji 12.0.4100.1.
— JonBrave,
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.