Transact-SQL pillanatkép biztonsági mentésének létrehozása

A következőkre vonatkozik: SQL Server 2022 (16.x) és újabb verziók

Ez a cikk ismerteti, hogy mi, miért és hogyan használható Transact-SQL pillanatképek biztonsági mentése. Transact-SQL (T-SQL) pillanatképek biztonsági másolatai a 2022-SQL Server (16.x) alatt lettek bevezetve.


Az adatbázisok napról napra egyre nagyobbak lesznek. A SQL Server mentések hagyományosan folyamatos mentések. A streamelési biztonsági mentés az adatbázis méretétől függ. A biztonsági mentési műveletek erőforrásokat (CPU, memória, I/O, hálózat) használnak fel, amelyek befolyásolják az egyidejű OLTP számítási feladatok átviteli sebességét a biztonsági mentés során. A biztonsági mentés hatékonyságának állandóvá tétele az adatok méretétől függetlenül úgy valósítható meg, ha pillanatkép alapú biztonsági mentést végez az alapul szolgáló tárolóhardver vagy szolgáltatás által biztosított mechanizmusokkal.

Mivel maga a biztonsági mentés hardverszinten történik, ez a funkció nem tiszta SQL Server megoldás. SQL Server először elő kell készítenie az adatokat és a naplófájlokat a pillanatképhez, hogy a fájlok garantáltan olyan állapotban legyenek, amely később visszaállítható. A lépés befejezése után az írási műveletek szüneteltetve lesznek SQL Server (olvasási kérelmek továbbra is engedélyezettek), és a rendszer átadja a vezérlést a biztonsági mentési alkalmazásnak a pillanatkép befejezéséhez. A pillanatkép sikeres befejezése után az alkalmazásnak vissza kell adnia a vezérlést SQL Server, ahol az írási műveletek ezután folytatódnak.

Mivel a pillanatkép-művelet során rögzíteni kell az írási műveleteket, elengedhetetlen, hogy a pillanatkép gyorsan történjen, hogy a kiszolgáló számítási feladatai ne szakadjon meg hosszabb ideig. Korábban a felhasználók az SQL Writer szolgáltatásra épülő nem Microsoft megoldásokra támaszkodtak a pillanatképek biztonsági mentésének elvégzéséhez. Az SQL Writer szolgáltatás Windows VSS -től (Volume Shadow Service) és SQL Server virtuáliseszköz-kezelőfelülettől (VDI) függ a SQL Server és a lemezszintű pillanatkép közötti vezénylés végrehajtásához.

Az SQL Writer szolgáltatáson alapuló biztonsági mentési ügyfelek általában összetettek, és csak Windows rendszereken működnek. A T-SQL-pillanatképek biztonsági mentésével a vezénylés SQL Server oldala több T-SQL-parancstal is kezelhető. Ez a funkció lehetővé teszi a felhasználók számára, hogy saját kis biztonsági mentési alkalmazásokat hozzanak létre, amelyek futtathatók Windows vagy Linux rendszeren, vagy akár parancsfájlalapú megoldásokat is, ha a mögöttes tároló támogatja a szkriptelési felületet a pillanatképek indításához.

Íme egy sample PowerShell-szkript, amely egy adatbázis biztonsági mentésének és visszaállításának végpontok közötti megoldását mutatja be egy Azure SQL IaaS virtuális gépen. A minta a 2022-SQL Server (16.x) rendszerben bevezetett T-SQL-pillanatkép-biztonsági mentési képességeket használja.

Workflow

A T-SQL-pillanatkép biztonsági mentési szintaxisa leválasztja a gyártótól függő pillanatkép-mechanizmust a felfüggesztési és biztonsági mentési műveletekről. Ezzel a szintaxissal a következőt teheti:

  1. Egy adatbázist a ALTER parancs segítségével lehet rögzíteni, amely lehetőséget biztosít a mögöttes tároló pillanatképének elkészítésére. Ezután feloldja az adatbázist, és a BACKUP paranccsal rögzítheti a pillanatképet.

  2. Több adatbázis pillanatképeinek végrehajtása egyidejűleg az új BACKUP GROUP és BACKUP SERVER a parancsokkal. Ezzel a beállítással a pillanatképek a mögöttes tároló pillanatképének részletességével végezhetők el, így nem kell többször is pillanatképet készíteni ugyanarról a lemezről.

  3. Hajtsa végre a FULL és COPY_ONLY FULL biztonsági mentéseket. Ezek a biztonsági másolatok msdb-ban is rögzítve vannak.

  4. Időponthoz kötött helyreállítást végezhet a pillanatkép FULL biztonsági mentése után a normál streamelési megközelítéssel készített naplók biztonsági mentésével. A streamelési különbözeti biztonsági mentések is támogatottak, ha szükséges.

Megjegyzés:

A különbségi bitképek az első szakaszban törlődnek, amikor felfüggeszti az adatbázist a ALTER paranccsal. Ha a felhasználó úgy dönt, hogy biztonsági mentés nélkül olvasztja fel az adatbázist, mert a pillanatkép sikertelen volt, vagy bármilyen más okból, a különbségi bitkép érvénytelen. A későbbi különbségi biztonsági mentések I/O-igényesebbek, mivel a különbségi biztonsági mentéshez a teljes adatbázist át kell vizsgálniuk. A különbségi bitkép a sikeres pillanatkép alapú biztonsági mentés után ismét érvényessé válik.

Az alábbi ábra a T-SQL-pillanatképek biztonsági mentéseinek magas szintű munkafolyamatát mutatja be:

Diagram, amely a folyamatot mutatja a felfüggesztéstől a pillanatképen át a biztonsági mentésig.

A középső pillanatkép-lépéshez el kell indítania a pillanatképet az alapul szolgáló tárolón. Az alábbi ábrán egy példa látható arra, hogyan működhet egy biztonsági mentési szkript SQL Server a pillanatkép biztonsági mentési folyamatának befejezéséhez:

Diagram példa arra, hogyan használható a biztonsági mentési szkript SQL Server a biztonsági mentési folyamat befejezéséhez.

Hasonlóképpen, a visszaállítási szkriptek a következőképpen működhetnek:

Diagram bemutatja, hogy a visszaállítási szkript hogyan használható SQL Server a visszaállítási feladat egy pillanatkép biztonsági mentéséből való végrehajtásához

Limitations

A funkcióval biztonsági másolat készíthető adatbázisok maximális száma 64. Ha a kiszolgálón több mint 64 adatbázis található, a következő hibaüzenet jelenik meg:

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éldák

A következő szakaszok különböző T-SQL-parancsokat mutatnak be a lemezre történő pillanatkép-biztonsági mentés végrehajtásához. Amikor a rendszer lemezre készít pillanatkép-biztonsági mentést, a rendszer csak a pillanatkép-biztonsági mentéshez kapcsolódó metaadatokat írja a fájlba. A kimenet nem tartalmazza az adatbázis tartalmát, kivéve a fejlécet és a fájl tartalmát. A pillanatkép biztonsági mentésének részeként létrehozott rendszerhéjfájlt a pillanatkép URI-jával kell használni a teljes biztonsági mentéshez. A RESTORE fájlból való adatbázis használatához a felhasználónak át kell helyeznie az adatbázisfájlokat a pillanatkép URI-jából a csatlakoztatási pontra, mielőtt kiadja a RESTORE parancsot. A felhasználók az összes hagyományos T-SQL-parancsot futtathatják ezen a pillanatkép-biztonsági mentési metaadatfájlon, mint például a RESTORE HEADERONLY, RESTORE FILELISTONLY és RESTORE DATABASE. A szintaxis támogatja a pillanatképek biztonsági mentési metaadatainak írását a DISK vagy a URL. A pillanatkép biztonsági mentési készletei ugyanúgy hozzáfűzhetők, mint a biztonsági mentési készletek egyetlen fájlba való streamelése.

Megjegyzés:

Az URL-címre történő biztonsági mentéshez előnyben részesítik a blokkblobokat, bár a lapblobok támogatottak az SQL Server Windows esetén. A SQL Server on Linux és a tárolók esetében csak a blokkblobok támogatottak.

A. Egyetlen felhasználói adatbázis felfüggesztése pillanatképek biztonsági mentéséhez és adatbázis biztonsági mentésének rögzítéséhez

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

B. Több felhasználói adatbázis felfüggesztése pillanatkép-biztonsági mentéshez

Ha több adatbázis található ugyanazon a mögöttes lemezen, az alábbi paranccsal felfüggeszthet több adatbázist.

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. A kiszolgáló összes felhasználói adatbázisának felfüggesztése pillanatkép-biztonsági mentéshez

Ha a kiszolgálón lévő összes felhasználói adatbázist fel kell függeszteni, használja az alábbi parancsot.

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

Megjegyzés:

Ezen parancsok egyike sem támogatja a rendszeradatbázisok (mastermodelés msdb) felfüggesztését a pillanatképek biztonsági mentéséhez.

D. Több felhasználói adatbázis felfüggesztése egyetlen paranccsal

Készítsen pillanatképet a kiszolgálón található összes felhasználói adatbázisról egyetlen biztonsági mentési csoportba:

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;

Megjegyzés:

Alapértelmezés szerint a SUSPEND_FOR_SNAPSHOT_BACKUP parancsok törlik a különbségi bitképet. Ha inkább csak másolatot szeretne készíteni, használja a COPY_ONLY kulcsszót az alábbi példákban látható módon.

E. Csak másolatképek mentése biztonsági célból

Mivel a különbségi bitkép a rögzítés előtt törlődik, lehetőség van (SUSPEND_FOR_SNAPSHOT_BACKUP) arra, COPY_ONLY hogy a rögzítés előtt ne törölje a különbségi bitképet.

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;

Megjegyzés:

Nem szükséges a BACKUP parancsban a COPY_ONLY használata, mivel az már meg van adva, amikor az adatbázist felfüggeszti a pillanatképek biztonsági mentéséhez.

F. Adatbázis biztonsági mentése különböző meghajtókon tárolt adatokkal és naplófájlokkal

Ha több meghajtón lévő adatfájlokat (.mdf és ) tartalmazó .ndfadatbázissal rendelkezik, és a tranzakciós naplófájl (.ldf) egy másik meghajtón található, az alábbiak szerint végezhet pillanatkép-biztonsági mentést:

  1. Függessze fel az adatbázist (ami megállítja az írási I/O-t mind az adatok, mind a naplófájlok esetében).

    ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

  2. Készítsen pillanatképet az összes mögöttes lemezről, ahol az adatbázis adatai és naplófájljai találhatók. Ez a lépés hardverfüggő.

  3. Végezze el a biztonsági mentést a METADATA_ONLY beállítással, amely létrehozza a pillanatkép biztonsági mentési metaadatait (.bkm) tartalmazó kimenetet.

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

A biztonsági mentés későbbi visszaállításához kövesse az alábbi lépéseket:

  1. Csatlakoztassa vagy csatolja a pillanatképlemezeket azon a virtuális gépen, ahová vissza szeretné állítani.

  2. Adatbázis-visszaállításkor használja a .bkm fájlt (az előző lista 3. lépéséből).

  3. Ha a meghajtók a visszaállítás során eltérőek, a MOVE logikai fájlok beállításával helyezze el őket a szükséges célhelyre. Példaként lásd az N példát.

G. A biztonsági mentési halmaz címkézése

A MEDIANAME és MEDIADESCRIPTION opciókat a biztonsági mentési parancsban használhatja a pillanatképhez társított URI megjelölésére. Ez a használat lehetővé teszi, hogy a biztonsági mentési fájl az adatbázis metaadataival együtt hordozhassa az alapul szolgáló pillanatkép-információkat. Az NAME és DESCRIPTION opciókat is használhatja az URI egyes biztonsági másolat készletek pillanatképével való megjelölésére.

SQL Server semmilyen módon nem értelmezi a LABEL adatokat. Ez azonban segít a felhasználónak megtekinteni a pillanatkép biztonsági mentéséhez társított URI-t a RESTORE LABELONLY paranccsal.

Ezután csatolhatja az URI-n található pillanatképlemezeket a virtuális géphez a pillanatkép visszaállításához. A pillanatkép URI-ja, amelyet a MEDIANAME és MEDIADESCRIPTION tárol, az msdb adatbázistábla dbo.backupmediaset is elérhető megtekintés céljából.

H. Pillanatkép biztonsági mentésének kimenete a RESTORE HEADERONLY használatával

A kimenet a következő mintához RESTORE HEADERONLY hasonlóan néz ki, ha az adatbázis, a csoport és a kiszolgáló végrehajtása sorrendben történik, és ugyanarra a kimeneti fájlra van írva:

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

I. Pillanatkép biztonsági mentésének kimenete a RESTORE FILELISTONLY használatával

Az alapértelmezett kimenet RESTORE FILELISTONLY az első mentési készletet jeleníti meg.

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

J. A RESTORE FILELISTONLY kimenet szűrése egy mentési készletre

Ha egy bizonyos biztonsági mentési csoportot szeretne kijelölni a több biztonsági mentési csoport közül RESTORE FILELISTONLY, használja a(z) FILE záradékot, amelyet már támogat a RESTORE FILELISTONLY.

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

Képernyőkép az SSMS-kimenetről a lekérdezésből származó biztonsági mentési készlethez.

K. A RESTORE FILELISTONLY kimenetét szűrés egy adatbázisra

Ha a kijelölt biztonsági mentési csoporton RESTORE FILELISTONLYbelül több adatbázisból szeretne további adatbázist kijelölni, használja a FILE záradékot a DBNAME záradékkal. A DBNAME záradék csak pillanatkép biztonsági mentési készletekben használható.

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

Képernyőkép a RESTORE FILELISTONLY-kimenet adatbázisra való szűrésének eredményeiről.

L. Pillanatkép-adatbázis visszaállítása

Az adatbázis pillanatkép-biztonsági mentésből való visszaállítása olyan, mint egy adatbázis csatolása . Futtassa a visszaállítási parancsot a RECOVERY beállítás nélkül, ha az adatbázist helyreállítás nélkül kell csatolni. Alapértelmezés szerint RESTORE az első adatbázist választja ki a pillanatkép-mentési készletben. Az alábbi példa visszaállítja az testdb1-t. Ha testdb1 már létezik a kiszolgálón, adja meg a záradékot REPLACE . Az RESTORE futtatása előtt csatlakoztatnia kell az adatbázisfájlokat.

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. Középen lévő pillanatkép-adatbázis visszaállítása

Ha a szükséges adatbázis RESTORED középen van, adja meg a záradékkal DBNAME visszaállítani kívánt adatbázist. Az alábbi szintaxis visszaállítja a záradékban megadott adatbázist 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. Az adatbázist más néven állítsa vissza

Az adatbázist más néven állíthatja vissza. Ha a szükséges adatbázis RESTORED középen van, adja meg a záradékkal DBNAME visszaállítani kívánt adatbázist. Az alábbi szintaxis visszaállítja a megadott adatbázist a DBNAME záradékkal, és átnevezi a következőre 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. Adatbázisok kinyerése több adatbázist tartalmazó biztonsági mentési csoportból a RESTORE BACKUPSETONLY használatával

A több adatbázist tartalmazó pillanatkép biztonsági mentési készlet, amely egy csoport vagy kiszolgáló pillanatképéből származik, felosztható a RESTORE BACKUPSETONLY paranccsal. Ez a parancs adatbázisonként egy biztonsági mentési csoportot hoz létre.

Ha egy kiszolgálói pillanatkép három adatbázist tartalmaz egy biztonságimásolat-készletet tartalmazó biztonsági mentési fájlban, az alábbi parancs három biztonsági mentési csoportot hoz létre, egyet az egyes adatbázisokhoz. Létrehoz egy könyvtárat <file_name_prefix>_<unique_time_stamp> a kimeneti fájlokhoz.

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

P. A RESTORE BACKUPSETONLY használatával kinyerhet egy adott adatbázist egy több adatbázist tartalmazó biztonsági mentési csoportban

RESTORE BACKUPSETONLY akkor támogatja a DBNAME paramétert, ha a felhasználó a biztonsági mentési csoportban lévő három adatbázisból egy adatbázist szeretne kihozni. Emellett támogatja a FILE paramétert több biztonsági mentési csoport szűréséhez a biztonsági mentési fájlban.

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

K. A felfüggesztési állapot és a lekért zárolások figyelése

A következő dinamikus felügyeleti nézetek (DMV-k) használhatók:

  • sys.dm_server_suspend_status (a felfüggesztés állapotának megtekintése)
  • sys.dm_tran_locks (a megszerzett zárolások megtekintése)

R. Biztonsági mentési adatkészlet részleteinek listázása

Az alábbi példaszkript Transact-SQL pillanatfelvétel-biztonsági mentések adatait sorolja fel.

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

S. Annak ellenőrzése, hogy egy adatbázist felfüggesztettek-e pillanatkép-biztonsági mentéshez

Az alábbi példaszkript adatbázisszintű tulajdonságokat ad ki a pillanatkép-biztonsági mentéshez felfüggesztett adatbázisokhoz.

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

T. T-SQL-hibaelhárító példaszkript

Az alábbi példaszkript észleli a kiszolgálón található felfüggesztett adatbázisokat, és szükség esetén feloldja a felfüggesztésüket.

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

Kapcsolódó tartalom

  • Last updated on