Vytvoření zálohy snímku Transact-SQL

Článek
03/05/2025

platí pro: SQL Server 2022 (16.x)

Tento článek vysvětluje, proč a jak používat zálohování snímků Transact-SQL. zálohy snímků Transact-SQL (T-SQL) byly zavedeny v SQL Serveru 2022 (16.x).

Databáze se každý den zvětšují a zvětšují. Tradičně se zálohování SQL Serveru provádí pomocí streamovaných záloh. Streamované zálohování závisí na velikosti databáze. Operace zálohování spotřebovávají prostředky (procesor, paměť, vstupně-výstupní operace, síť), které ovlivňují propustnost souběžné úlohy OLTP během zálohování. Jedním ze způsobů, jak vytvořit konstantu výkonu zálohování místo toho, aby závisela na velikosti dat, je provedením zálohování snímků pomocí mechanismů poskytovaných základním hardwarem nebo službou úložiště.

Vzhledem k tomu, že samotné zálohování probíhá na úrovni hardwaru, není tato funkce čistě řešením SQL Serveru. SQL Server musí nejprve připravit data a soubory protokolu pro snímek, aby se zajistilo, že soubory budou ve stavu, který lze později obnovit. Po dokončení tohoto kroku se operace zápisu na SQL Serveru pozastaví (požadavky na čtení jsou stále povolené) a řízení se předá zálohovací aplikaci, aby dokončila snímek. Po úspěšném dokončení snímku musí aplikace vrátit řízení zpět na SQL Server, kde se pak obnoví operace zápisu.

Vzhledem k tomu, že během operace snímku musíme zastavit operace zápisu, je nezbytné, aby se snímek rychle provedl, aby zatížení serveru nebylo na delší dobu přerušeno. V minulosti se uživatelé spoléhali na řešení od jiných společností než Microsoft, která byla postavena na službě SQL Writer, aby dokončili zálohování snímků. Služba SQL Writer závisí na stínové službě systému Windows (Stínová služba svazku) spolu s rozhraním SQL Server VDI (virtual device interface) k provedení orchestrace mezi SQL Serverem a snímkem na úrovni disku.

Zálohovací klienti založené na službě SQL Writer jsou obvykle složití a pracují jenom ve Windows. U zálohování snímků pomocí T-SQL je možné orchestraci na straně SQL Serveru zpracovat pomocí řady příkazů T-SQL. Tato funkce umožňuje uživatelům vytvářet vlastní malé zálohovací aplikace, které můžou běžet ve Windows nebo Linuxu, nebo dokonce skriptovaná řešení, pokud základní úložiště podporuje skriptovací rozhraní pro zahájení snímku.

Tady je ukázkový skript PowerShellu, který nabízí demonstraci uceleného řešení zálohování a obnovení databáze ve virtuálním počítači Azure SQL IaaS. Ukázka používá funkce zálohování snímků T-SQL představené v SQL Serveru 2022 (16.x).

Pracovní postup

Syntaxe zálohování snímků T-SQL odděluje mechanismus snímků závislý na dodavateli od operací pozastavení a zálohování. Pomocí této syntaxe můžete:

Ukotvit databázi pomocí příkazu ALTER, který poskytuje příležitost k provedení snímku základního úložiště. Potom můžete databázi rozmrazit a nahrát snímek pomocí příkazu BACKUP.
Pomocí nových příkazů BACKUP GROUP a BACKUP SERVER provádět snímky více databází současně. Díky této možnosti se snímky dají provádět v členitosti snímků podkladového úložiště, což eliminuje nutnost provádět snímek stejného disku několikrát.
Proveďte zálohování FULL a COPY_ONLY FULL. Tyto zálohy se zaznamenávají také v msdb.
Proveďte obnovení k určitému bodu v čase pomocí záloh protokolů pořízených běžným streamovacím přístupem po zálohování snímku FULL. Podporovány jsou také rozdílové zálohy streamováním, pokud je to žádoucí.

Poznámka

Rozdílové rastrové obrázky se vymažou během první fáze při pozastavení databáze pomocí příkazu ALTER. Pokud se uživatel rozhodne databázi rozmrazit, aniž by provedl zálohu, protože snímek selhal nebo z jiného důvodu, rozdílový rastrový obrázek je neplatný. Jakékoli následné rozdílové zálohy jsou náročnější na vstupně-výstupní operace, protože musí prohledat celou databázi, aby bylo možno provést rozdílové zálohování. Rozdílový rastrový obrázek bude znovu platný po úspěšném zálohování snímku.

Následující diagram znázorňuje pracovní postup vysoké úrovně záloh snímků T-SQL:

Prostřední krok snímku vyžaduje, abyste snímek zahájili v podkladovém úložišti. Následující diagram znázorňuje příklad toho, jak může záložní skript pracovat s SQL Serverem k dokončení procesu zálohování snímků:

Podobně může skript obnovení fungovat takto:

Omezení

Maximální počet databází, které můžete pomocí této funkce zálohovat, je 64. Pokud na serveru existuje více než 64 databází, zobrazí se následující chyba:

Error message:
Msg 925, Level 19, State 1, Line 4
Maximum number of databases used for each query has been exceeded. The maximum allowed is 64.

Příklady

Následující části ukazují různé příkazy T-SQL, které slouží k zálohování snímků na disk. Při zápisu zálohy snímku na disk se do souboru zapíšou jenom metadata připojená k zálohování snímků. Výstup neobsahuje žádný obsah databáze s výjimkou hlavičky a obsahu souboru. Shellový soubor vytvořený jako součást provádění zálohování snímků by se měl použít se skutečným URI snímku k provedení úplného zálohování. RESTORE databáze z tohoto souboru vyžaduje, aby uživatel zkopíroval soubory databáze z URI snímku do přípojného bodu před zadáním příkazu RESTORE. Uživatelé mohou spouštět všechny tradiční příkazy T-SQL, jako jsou RESTORE HEADERONLY a RESTORE FILELISTONLY, na tomto souboru metadat zálohování snímků spolu s RESTORE DATABASE. Syntaxe podporuje zápis metadat zálohování snímků do DISK nebo URL. Sady zálohování snímků se dají připojit také stejně jako streamované sady záloh do jednoho souboru.

Poznámka

Pro zálohování na URL se upřednostňují blokové objekty blob, i když jsou pro SQL Server na Windows podporovány i stránkové objekty blob. Pro SQL Server na Linuxu a v kontejnerech se podporují jenom blokové blob objekty.

A. Pozastavení izolované uživatelské databáze pro zálohování snímků a zaznamenání zálohy databáze

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

B. Pozastavení více uživatelských databází pro zálohování snímků

Pokud je na stejném podkladovém disku více databází, můžete pomocí následujícího příkazu pozastavit více databází.

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));

BACKUP GROUP testdb1, testdb2
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

C. Pozastavení všech uživatelských databází na serveru pro zálohování snímků

Pokud je potřeba pozastavit všechny uživatelské databáze na serveru, použijte následující příkaz.

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP SERVER
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

Poznámka

Žádný z těchto příkazů nepodporuje pozastavení systémových databází (master, modela msdb) pro zálohování snímků.

D. Pozastavení více uživatelských databází jedním příkazem

Zaznamenejte snímek všech uživatelských databází na serveru do jedné zálohovací sady:

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));

BACKUP GROUP testdb1, testdb2
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

Poznámka

Ve výchozím nastavení příkaz SUSPEND_FOR_SNAPSHOT_BACKUP vymaže rozdílovou bitmapu. Pokud dáváte přednost provedení zálohy pouze kopií, použijte klíčové slovo COPY_ONLY, jak je znázorněno v následujících příkladech.

E. Zálohování snímků jen pro kopírování

Vzhledem k tomu, že rozdílový bitmap je vymazán před zmrazením, SUSPEND_FOR_SNAPSHOT_BACKUP poskytuje možnost (COPY_ONLY) nevymazat rozdílový bitmap před zmrazením.

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);

BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2), MODE = COPY_ONLY);

BACKUP GROUP testdb1, testdb2
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);

BACKUP SERVER
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

Poznámka

V příkazu BACKUP není nutné používat COPY_ONLY, protože už je zadaná při pozastavení databáze pro zálohování snímků.

F. Zálohujte databázi s datovými a logovými soubory na různých discích

Pokud máte databázi s datovými soubory (.mdf a .ndf) na více jednotkách a soubor transakčního protokolu (.ldf) na jiné jednotce, můžete provést zálohování snímků následujícím způsobem:

Pozastavte databázi (která zablokuje vstupně-výstupní operace zápisu u souborů dat i protokolů).
```
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
```
Pořiďte snímek všech podkladových disků, kde jsou data databáze a soubory protokolu. Tento krok je závislý na hardwaru.
Proveďte zálohování pomocí možnosti METADATA_ONLY, která vytvoří výstup obsahující metadata zálohování snímků (.bkm).
```
BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;
```

Pokud chcete tuto zálohu obnovit v pozdější fázi, postupujte takto:

Připojte nebo namontujte snímkové disky na virtuální počítač, kde chcete provést obnovení.
Při obnovení databáze použijte soubor .bkm (z kroku 3 v předchozím seznamu).
Pokud se jednotky během obnovení liší, použijte možnost MOVE pro logické soubory, aby je umístila do požadovaného umístění. Příklad najdete v Příklad N.

G. Označte zálohovací sadu

Pomocí možností MEDIANAME a MEDIADESCRIPTION v příkazu zálohování můžete označit identifikátor URI přidružený k snímku. Toto použití umožňuje záložnímu souboru přenášet podkladové informace o snímku spolu s metadaty databáze. Můžete také použít možnosti NAME a DESCRIPTION k označení identifikátoru URI jednotlivými snímky zálohovací sady.

SQL Server neinterpretuje LABEL informace žádným způsobem. Pomáhá ale uživateli zobrazit identifikátor URI přidružený k zálohování snímků pomocí příkazu RESTORE LABELONLY.

Pak můžete k virtuálnímu počítači připojit snímkové disky umístěné na identifikátoru URI, aby se snímek obnovil. Identifikátor URI snímku uložený v MEDIANAME a MEDIADESCRIPTION je také k dispozici pro zobrazení v tabulce databáze msdbdbo.backupmediaset.

H. Výstup zálohování snímků použitím RESTORE HEADERONLY

Výstup s RESTORE HEADERONLY vypadá jako následující ukázka, pokud se databáze, skupina a server spouštějí postupně a zapisují do stejného výstupního souboru:

RESTORE HEADERONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

Já. Výstup zálohování snímků s použitím RESTORE FILELISTONLY

Výstup s RESTORE FILELISTONLY ve výchozím nastavení zobrazí první sadu záloh:

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

J. Filtrování výstupu RESTORE FILELISTONLY do zálohovací sady

Chcete-li konkrétně vybrat určitou sadu záloh z více sad záloh s RESTORE FILELISTONLY, použijte klauzuli FILE, která je již podporována v RESTORE FILELISTONLY.

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;

snímek obrazovky s výstupem SSMS pro nastavení zálohování z dotazu

K. Filtrovat výstup RESTORE FILELISTONLY do databáze

Pokud chcete dále vybrat jednu databázi z více databází ve vybrané sadě záloh s RESTORE FILELISTONLY, použijte klauzuli FILE s klauzulí DBNAME. Klauzuli DBNAME lze použít pouze u sad záloh snímků.

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';

snímek obrazovky s výsledky filtrování výstupu RESTORE FILELISTONLY do databáze

L. Obnovení databáze snímků

Obnovení databáze ze zálohy snímku je jako připojení k databázi. Spusťte příkaz pro obnovení bez možnosti RECOVERY, pokud je potřeba databázi připojit bez obnovení. Ve výchozím nastavení RESTORE vybere první databázi v sadě záloh snímků. Následující příklad obnoví testdb1. Pokud testdb1 již na serveru existuje, uveďte klauzuli REPLACE. Před spuštěním RESTOREje nutné připojit soubory databáze .

RESTORE DATABASE testdb1
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, REPLACE, --> no DBNAME clause - restore first database in backup set
MOVE 'testdb1' TO 'D:\Temp\snap\testdb1.mdf',
MOVE 'testdb1_log' TO 'D:\Temp\snap\testdb1_log.ldf';

M. Obnovení databáze snímků uvedené uprostřed

Pokud je databáze, která musí být RESTORED, je uprostřed, zadejte databázi, která se má obnovit pomocí klauzule DBNAME. Následující syntaxe obnoví zadanou databázi v klauzuli DBNAME.

RESTORE DATABASE testdb3
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> restores testdb3 database
MOVE 'testdb3' TO 'D:\Temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'D:\Temp\snap\testdb3_log.ldf',
NORECOVERY;

N. Obnovení databáze s jiným názvem

Databázi můžete obnovit pod jiným názvem. Pokud je databáze, která musí být RESTORED, umístěna uprostřed, uveďte databázi, která má být obnovena pomocí klauzule DBNAME. Následující syntaxe obnoví zadanou databázi s klauzulí DBNAME a přejmenuje ji na testdb33.

RESTORE DATABASE testdb33 --> renames the specified database testdb3 to testdb33.
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> original name specified here
MOVE 'testdb3' TO 'D:\Temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'D:\Temp\snap\testdb3_log.ldf',
NORECOVERY;

O. Použití FUNKCE RESTORE BACKUPSETONLY k extrahování databází ze zálohovací sady obsahující více databází

Sadu záloh snímků obsahující více databází ze snímku skupiny nebo serveru je možné rozdělit pomocí příkazu RESTORE BACKUPSETONLY. Tento příkaz vytvoří jednu sadu záloh na databázi.

Pokud snímek serveru obsahuje tři databáze v záložním souboru obsahujícím jednu sadu záloh, následující příkaz vygeneruje tři sady záloh, jednu pro každou databázi. Vytvoří adresář s <file_name_prefix>_<unique_time_stamp> pro výstupní soubory.

RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db1.bkm'
WITH METADATA_ONLY;

P. Použití FUNKCE RESTORE BACKUPSETONLY k extrahování konkrétní databáze v zálohovací sadě obsahující více databází

RESTORE BACKUPSETONLY podporuje parametr DBNAME, pokud chce uživatel vybrat jednu databázi ze tří databází v záložní sadě. Podporuje také parametr FILE k filtrování více sad záloh v záložním souboru.

RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';

Q. Monitorování stavu pozastavení a získaných zámků

Můžete použít následující zobrazení dynamické správy (DMV):

sys.dm_server_suspend_status (zobrazení stavu pozastavení)
sys.dm_tran_locks (zobrazení získaných zámků)

R. Výpis podrobností o zálohovací sadě

Následující ukázkový skript obsahuje informace o zálohovací sadě pro Transact-SQL zálohování snímků.

SELECT database_name,
    type,
    backup_size,
    backup_start_date,
    backup_finish_date,
    is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;

S. Kontrola, jestli byla databáze pozastavená pro zálohování snímků

Následující ukázkový skript vypíše vlastnosti na úrovni databáze pro databáze, které jsou pozastavené pro zálohování snímků.

SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');

T. Ukázkový skript pro řešení potíží s T-SQL

Následující ukázkový skript zjistí pozastavené databáze na serveru a v případě potřeby je zruší.

IF (SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') = 1)
BEGIN
    --full server suspended, requires server level thaw
    PRINT 'Full server is suspended, requires server level thaw'

    ALTER SERVER CONFIGURATION
    SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF
END
ELSE
BEGIN
    IF (SERVERPROPERTY('SuspendedDatabaseCount') > 0)
    BEGIN
        DECLARE @curdb SYSNAME
        DECLARE @sql NVARCHAR(500)

        DECLARE mycursor CURSOR FAST_FORWARD
        FOR
        SELECT db_name
        FROM sys.dm_server_suspend_status;

        OPEN mycursor

        FETCH NEXT
        FROM mycursor
        INTO @curdb

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'unfreezing DB ' + @curdb

            SET @sql = 'ALTER DATABASE ' + @curdb + ' SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF'

            EXEC sp_executesql @SQL

            FETCH NEXT
            FROM mycursor
            INTO @curdb
        END

        PRINT 'All DB unfrozen'

        CLOSE mycursor;

        DEALLOCATE mycursor;
    END
    ELSE
        -- no suspended database, thus no user action needed.
        PRINT 'No database/server is suspended for snapshot backup'
END

backupmediaset (Transact-SQL)
BACKUP (Transact-SQL)

Sdílet prostřednictvím

Vytvoření zálohy snímku Transact-SQL

Pracovní postup

Omezení

Příklady

A. Pozastavení izolované uživatelské databáze pro zálohování snímků a zaznamenání zálohy databáze

B. Pozastavení více uživatelských databází pro zálohování snímků

C. Pozastavení všech uživatelských databází na serveru pro zálohování snímků

D. Pozastavení více uživatelských databází jedním příkazem

E. Zálohování snímků jen pro kopírování

F. Zálohujte databázi s datovými a logovými soubory na různých discích

G. Označte zálohovací sadu

H. Výstup zálohování snímků použitím RESTORE HEADERONLY

Já. Výstup zálohování snímků s použitím RESTORE FILELISTONLY

J. Filtrování výstupu RESTORE FILELISTONLY do zálohovací sady

K. Filtrovat výstup RESTORE FILELISTONLY do databáze

L. Obnovení databáze snímků

M. Obnovení databáze snímků uvedené uprostřed

N. Obnovení databáze s jiným názvem

O. Použití FUNKCE RESTORE BACKUPSETONLY k extrahování databází ze zálohovací sady obsahující více databází

P. Použití FUNKCE RESTORE BACKUPSETONLY k extrahování konkrétní databáze v zálohovací sadě obsahující více databází

Q. Monitorování stavu pozastavení a získaných zámků

R. Výpis podrobností o zálohovací sadě

S. Kontrola, jestli byla databáze pozastavená pro zálohování snímků

T. Ukázkový skript pro řešení potíží s T-SQL

Váš názor

Další materiály

Sdílet prostřednictvím

Vytvoření zálohy snímku Transact-SQL

Pracovní postup

Omezení

Příklady

A. Pozastavení izolované uživatelské databáze pro zálohování snímků a zaznamenání zálohy databáze

B. Pozastavení více uživatelských databází pro zálohování snímků

C. Pozastavení všech uživatelských databází na serveru pro zálohování snímků

D. Pozastavení více uživatelských databází jedním příkazem

E. Zálohování snímků jen pro kopírování

F. Zálohujte databázi s datovými a logovými soubory na různých discích

G. Označte zálohovací sadu

H. Výstup zálohování snímků použitím RESTORE HEADERONLY

Já. Výstup zálohování snímků s použitím RESTORE FILELISTONLY

J. Filtrování výstupu RESTORE FILELISTONLY do zálohovací sady

K. Filtrovat výstup RESTORE FILELISTONLY do databáze

L. Obnovení databáze snímků

M. Obnovení databáze snímků uvedené uprostřed

N. Obnovení databáze s jiným názvem

O. Použití FUNKCE RESTORE BACKUPSETONLY k extrahování databází ze zálohovací sady obsahující více databází

P. Použití FUNKCE RESTORE BACKUPSETONLY k extrahování konkrétní databáze v zálohovací sadě obsahující více databází

Q. Monitorování stavu pozastavení a získaných zámků

R. Výpis podrobností o zálohovací sadě

S. Kontrola, jestli byla databáze pozastavená pro zálohování snímků

T. Ukázkový skript pro řešení potíží s T-SQL

Související obsah

Váš názor

Další materiály