Delen via

Een back-up van een Transact-SQL momentopname maken

  • Artikel

van toepassing op: SQL Server 2022 (16.x)

In dit artikel wordt uitgelegd wat, waarom en hoe u back-ups van Transact-SQL momentopnamen gebruikt. Transact-SQL (T-SQL) momentopnameback-ups zijn geïntroduceerd in SQL Server 2022 (16.x).


Databases worden elke dag groter en groter. Traditioneel zijn SQL Server-back-ups streaming-back-ups. Een streamingback-up is afhankelijk van de grootte van de database. Back-upbewerkingen verbruiken resources (CPU, geheugen, I/O, netwerk) die van invloed zijn op de doorvoer van de gelijktijdige OLTP-workload tijdens de back-up. Een manier om de back-upprestaties constant te maken, in plaats van afhankelijk te zijn van de grootte van gegevens, is door een momentopnameback-up uit te voeren met behulp van mechanismen die worden geleverd door de onderliggende opslaghardware of -service.

Omdat de back-up zelf op hardwareniveau plaatsvindt, is deze functie geen pure SQL Server-oplossing. SQL Server moet eerst de gegevens en logboekbestanden voorbereiden voor de momentopname, zodat de bestanden gegarandeerd de status hebben die later kan worden hersteld. Zodra deze stap is voltooid, worden schrijfbewerkingen onderbroken op SQL Server (leesaanvragen zijn nog steeds toegestaan) en wordt de controle overgedragen aan de back-uptoepassing om de momentopname te voltooien. Zodra de momentopname is voltooid, moet de applicatie de controle teruggeven aan SQL Server, waar schrijfbewerkingen vervolgens worden hervat.

Omdat we schrijfbewerkingen moeten blokkeren tijdens de momentopnamebewerking, is het essentieel dat de momentopname snel plaatsvindt, zodat de werkbelasting op de server gedurende een langere periode niet wordt onderbroken. In het verleden maakten gebruikers gebruik van niet-Microsoft-oplossingen die zijn gebouwd op de SQL Writer-service om back-ups van momentopnamen te voltooien. De SQL Writer-service is afhankelijk van Windows VSS (Volume Shadow Service) samen met SQL Server VDI (Virtual Device Interface) om de indeling tussen SQL Server en de momentopname op schijfniveau uit te voeren.

Back-upclients op basis van de SQL Writer-service zijn meestal complex en ze werken alleen in Windows. Met back-ups van T-SQL-momentopnamen kan de SQL Server-zijde van de indeling worden verwerkt met een reeks T-SQL-opdrachten. Met deze functionaliteit kunnen gebruikers hun eigen kleine back-uptoepassingen maken die kunnen worden uitgevoerd op Windows of Linux, of zelfs als de onderliggende opslag ondersteuning biedt voor een scriptinterface om een momentopname te starten.

Hier volgt een PowerShell-voorbeeldscript, waarin een end-to-end oplossing wordt gedemonstreert voor het maken van back-ups en het herstellen van een database in een virtuele Azure SQL IaaS-machine. In het voorbeeld worden de back-upmogelijkheden van T-SQL-momentopnamen gebruikt die zijn geïntroduceerd in SQL Server 2022 (16.x).

Werkstroom

De back-upsyntaxis van de T-SQL-momentopname koppelt het leverancierafhankelijke momentopnamemechanisme los van de onderbrekings- en back-upbewerkingen. Met deze syntaxis kunt u het volgende doen:

  1. Bevriezen van een database met de opdracht ALTER, waarmee u de momentopname van de onderliggende opslag kunt uitvoeren. Daarna kunt u de database ontdooien en de momentopname vastleggen met de opdracht BACKUP.

  2. Momentopnamen van meerdere databases tegelijk uitvoeren met de nieuwe BACKUP GROUP en BACKUP SERVER opdrachten. Met deze optie kunnen momentopnamen worden uitgevoerd op de granulariteit van de onderliggende opslag, waardoor er niet meerdere keren een momentopname van dezelfde schijf hoeft te worden uitgevoerd.

  3. Voer FULL-backups en COPY_ONLY FULL-backups uit. Deze back-ups worden ook vastgelegd in msdb.

  4. Voer point-in-time herstel uit met behulp van logboekback-ups die zijn gemaakt met de normale streamingbenadering na de snapshot FULL back-up. Differentiële streamingback-ups worden desgewenst ook ondersteund.

Notitie

Differentiële bitmaps worden gewist tijdens de eerste fase bij het onderbreken van de database met de opdracht ALTER. Als de gebruiker besluit de database te ontdooien zonder een back-up uit te voeren omdat de momentopname is mislukt of om een andere reden, is de differentiële bitmap ongeldig. Eventuele volgende differentiële back-ups zijn I/O-intensief, omdat ze de hele database moeten scannen om de differentiële back-up uit te voeren. De differentiële bitmap wordt opnieuw geldig na een geslaagde back-up van de momentopname.

In het volgende diagram ziet u de werkstroom op hoog niveau van back-ups van T-SQL-momentopnamen:

diagram waarin het proces wordt weergegeven van onderbreken, tot momentopnamen, tot back-up.

Voor de middelste momentopnamestap moet u de momentopname initiëren in de onderliggende opslag. In het volgende diagram ziet u een voorbeeld van hoe een back-upscript kan werken met SQL Server om het back-upproces voor momentopnamen te voltooien:

Diagram toont een voorbeeld van hoe het back-upscript met SQL Server kan werken om het back-upproces te voltooien.

Op dezelfde manier kan een herstelscript als volgt werken:

Diagram laat zien hoe het herstelscript kan werken met SQL Server om de hersteltaak te voltooien vanuit een back-up van een momentopname.

Beperkingen

Het maximum aantal databases waarvan u een back-up kunt maken met deze functie is 64. Als er meer dan 64 databases op de server zijn, ziet u de volgende fout:

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.

Voorbeelden

In de volgende secties ziet u verschillende T-SQL-opdrachten die worden gebruikt om back-ups van momentopnamen naar schijf uit te voeren. Wanneer een momentopnameback-up naar schijf wordt geschreven, worden alleen de metagegevens die zijn verbonden met de back-up van de momentopname naar het bestand geschreven. De uitvoer bevat geen inhoud van de database, met uitzondering van de koptekst en de bestandsinhoud. Het shell-bestand dat is gemaakt als onderdeel van het uitvoeren van momentopnameback-up, moet worden gebruikt met de werkelijke momentopname-URI om een volledige back-up te maken. Voor een RESTORE van een database uit dit bestand moet de gebruiker de databasebestanden van de snapshot-URI naar het koppelpunt kopiëren voordat de RESTORE-opdracht wordt uitgevoerd. Gebruikers kunnen alle traditionele T-SQL-opdrachten uitvoeren, zoals RESTORE HEADERONLY en RESTORE FILELISTONLY, in dit metagegevensbestand voor back-ups van momentopnamen, samen met RESTORE DATABASE. De syntaxis ondersteunt het schrijven van metagegevens van back-ups van momentopnamen naar DISK of URL. De back-upsets voor momentopnamen kunnen ook worden toegevoegd, net als streamingback-upsets in één bestand.

Notitie

Voor back-up naar een URL hebben blokblobs de voorkeur, hoewel paginablobs worden ondersteund voor SQL Server op Windows. Voor SQL Server op Linux en containers worden alleen blok-blobs ondersteund.

Een. Een individuele gebruikersdatabase onderbreken voor back-up van momentopnamen en een databaseback-up vastleggen

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

B. Meerdere gebruikersdatabases onderbreken voor back-up van momentopnamen

Als meerdere databases zich op dezelfde onderliggende schijf bevinden, kunt u meerdere databases onderbreken met de volgende opdracht.

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. Alle gebruikersdatabases op de server onderbreken voor back-up van momentopnamen

Als alle gebruikersdatabases op de server moeten worden onderbroken, gebruikt u de volgende opdracht.

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

Notitie

Geen van deze opdrachten biedt ondersteuning voor het onderbreken van systeemdatabases (master, modelen msdb) voor back-ups van momentopnamen.

D. Meerdere gebruikersdatabases onderbreken met één opdracht

Noteer een momentopname van alle gebruikersdatabases op de server in één back-upset:

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;

Notitie

SUSPEND_FOR_SNAPSHOT_BACKUP opdrachten wissen standaard de differentiële bitmap. Als u liever een alleen-lezen back-up wilt uitvoeren, gebruikt u het trefwoord COPY_ONLY, zoals weergegeven in de volgende voorbeelden.

E. Back-ups van enkel-kopie-momentopnamen uitvoeren

Omdat de differentiële bitmap vóór de bevriezing wordt gewist, biedt SUSPEND_FOR_SNAPSHOT_BACKUP een optie (COPY_ONLY) om de differentiële bitmap niet te wissen voordat de bevriezing plaatsvindt.

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;

Notitie

Het is niet nodig om COPY_ONLY te gebruiken in de opdracht BACKUP, omdat deze al is opgegeven bij het onderbreken van de database voor back-up van momentopnamen.

F. Een back-up maken van een database met gegevens en logboekbestanden op verschillende schijven

Als u een database hebt met gegevensbestanden (.mdf en .ndf) op meerdere stations en het transactielogboekbestand (.ldf) op een ander station, kunt u als volgt een back-up van een momentopname uitvoeren:

  1. De database onderbreken (die de schrijf-I/O blokkeert voor zowel gegevens als logboekbestanden).

    ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

  2. Maak een momentopname van alle de onderliggende schijven waar de databasegegevens en logboekbestanden aanwezig zijn. Deze stap is afhankelijk van hardware.

  3. Voer de back-up uit met behulp van de optie METADATA_ONLY, waarmee de uitvoer wordt gemaakt met de metagegevens van de momentopnameback-up (.bkm).

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

Volg deze stappen om deze back-up in een later stadium te herstellen:

  1. Bevestig de snapshot-schijven aan de virtuele machine waar u deze wilt herstellen.

  2. Gebruik het .bkm-bestand (uit stap 3 in de vorige lijst) wanneer u een database herstelt.

  3. Als de stations verschillen tijdens het herstel, gebruik de MOVE-optie om de logische bestanden op de vereiste bestemming te plaatsen. Zie voorbeeld Nvoor een voorbeeld.

G. De back-upset taggen

U kunt de MEDIANAME- en MEDIADESCRIPTION-opties in de back-upopdracht gebruiken om de URI te taggen die aan de momentopname is gekoppeld. Hierdoor kan het back-upbestand de onderliggende momentopnamegegevens bevatten, samen met de metagegevens van de database. U kunt ook de opties voor NAME en DESCRIPTION gebruiken om de URI te taggen met de afzonderlijke momentopname van de back-upset.

SQL Server interpreteert de LABEL informatie op geen enkele manier. Het helpt de gebruiker echter om de URI weer te geven die is gekoppeld aan de back-up van de momentopname met de opdracht RESTORE LABELONLY.

Vervolgens kunt u de momentopnameschijven die zich op de URI bevinden, koppelen aan de VIRTUELE machine om de momentopname te herstellen. De momentopname-URI die is opgeslagen in de MEDIANAME en MEDIADESCRIPTION is vervolgens ook beschikbaar voor weergave in de msdb databasetabel dbo.backupmediaset.

H. Uitvoer van back-up van momentopnamen met RESTORE HEADERONLY

De uitvoer met RESTORE HEADERONLY lijkt op het volgende voorbeeld, als de database, groep en server op volgorde worden uitgevoerd en naar hetzelfde uitvoerbestand worden geschreven:

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

Ik. Uitvoer van back-up van momentopnamen met RESTORE FILELISTONLY

In de uitvoer met RESTORE FILELISTONLY wordt standaard de eerste back-upset weergegeven.

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

J. RESTORE FILELISTONLY-uitvoer filteren naar een back-upset

Als u specifiek een bepaalde back-upset wilt selecteren uit meerdere back-upsets met RESTORE FILELISTONLY, gebruikt u de FILE component die al wordt ondersteund op RESTORE FILELISTONLY.

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

Schermafbeelding van SSMS-uitvoer naar de back-upset van een query.

K. De uitvoer van RESTORE FILELISTONLY naar een database filteren

Als u nog meer één database wilt selecteren uit meerdere databases in de geselecteerde back-upset met RESTORE FILELISTONLY, gebruikt u de FILE-component met de DBNAME-component. De component DBNAME kan alleen worden gebruikt voor back-upsets voor momentopnamen.

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

Schermopname van de resultaten van het filteren van de uitvoer van RESTORE FILELISTONLY naar een database.

L. Een momentopnamedatabase herstellen

Het herstellen van een database vanuit een back-up van momentopnamen is als het koppelen van een database. Voer de herstelopdracht uit zonder de optie RECOVERY, als de database zonder herstel moet worden gekoppeld. Standaard selecteert RESTORE de eerste database in de back-upset voor momentopnamen. In het volgende voorbeeld wordt testdb1hersteld. Als testdb1 al op de server bestaat, neemt u de REPLACE clausule op. U moet de databasebestanden koppelen voordat u RESTOREuitvoert.

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. Een momentopnamedatabase herstellen die in het midden van de lijst wordt vermeld.

Als de database die moet worden RESTORED zich in het midden bevindt, geeft u de database op die moet worden hersteld met de DBNAME-clausule. Met de volgende syntaxis wordt de opgegeven database in de DBNAME-clausule hersteld.

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. De database herstellen met een andere naam

U kunt de database herstellen met een andere naam. Als de database die moet worden RESTORED zich in het midden bevindt, geeft u de database op die moet worden hersteld met de DBNAME-clausule. De volgende syntaxis herstelt de opgegeven database met de DBNAME-clausule en wijzigt de naam in 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. RESTORE BACKUPSETONLY gebruiken om databases te extraheren uit een back-upset die meerdere databases bevat

Een back-upset voor momentopnamen, met meerdere databases van een groep of een server-snapshot, kan worden gesplitst met de opdracht RESTORE BACKUPSETONLY. Met deze opdracht wordt één back-upset per database gemaakt.

Als een momentopname van een server drie databases in een back-upbestand met één back-upset bevat, genereert de volgende opdracht drie back-upsets, één voor elke database. Er wordt een map gemaakt met <file_name_prefix>_<unique_time_stamp> voor de uitvoerbestanden.

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

P. RESTORE BACKUPSETONLY gebruiken om een specifieke database in een back-upset met meerdere databases te extraheren

RESTORE BACKUPSETONLY ondersteunt de parameter DBNAME als de gebruiker één database uit de drie databases in de back-upset wil uitvoeren. Het ondersteunt ook de parameter FILE om meerdere back-upsets in het back-upbestand te filteren.

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

Q. De pauzestatus en verworven vergrendelingen bewaken

U kunt de volgende dynamische beheerweergaven (DMV's) gebruiken:

  • sys.dm_server_suspend_status (de onderbrekingsstatus weergeven)
  • sys.dm_tran_locks (bekijk de verkregen vergrendelingen)

R. Details van back-upset weergeven

Het volgende voorbeeldscript bevat back-upsetgegevens voor Transact-SQL momentopnameback-ups.

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

S. Controleren of een database is onderbroken voor back-up van momentopnamen

Met het volgende voorbeeldscript worden eigenschappen op databaseniveau uitgevoerd voor databases die zijn onderbroken voor back-up van momentopnamen.

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

T. Voorbeeldscript voor het oplossen van problemen met T-SQL

Het volgende voorbeeldscript detecteert onderbroken databases op de server en maakt deze indien nodig ongedaan.

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

Verwante inhoud