Skapa en säkerhetskopia av Transact-SQL ögonblicksbild

Artikel
2025-03-05

gäller för: SQL Server 2022 (16.x)

Den här artikeln förklarar vad, varför och hur du använder Transact-SQL säkerhetskopieringar av ögonblicksbilder. Transact-SQL ögonblicksbildbackuper (T-SQL) introducerades i SQL Server 2022 (16.x).

Databaser blir större och större varje dag. Traditionellt är SQL Server-säkerhetskopior strömmande säkerhetskopior. En säkerhetskopiering av direktuppspelning beror på databasens storlek. Säkerhetskopieringsåtgärder förbrukar resurser (CPU, minne, I/O, nätverk) som påverkar dataflödet för den samtidiga OLTP-arbetsbelastningen under säkerhetskopieringen. Ett sätt att göra säkerhetskopieringens prestanda konstant, i stället för att vara beroende av storleken på data, är att utföra en säkerhetskopiering av ögonblicksbilder med hjälp av mekanismer som tillhandahålls av den underliggande lagringsmaskinvaran eller tjänsten.

Eftersom själva säkerhetskopieringen sker på maskinvarunivå är den här funktionen inte en ren SQL Server-lösning. SQL Server måste först förbereda data och loggfiler för ögonblicksbilden, så att filerna garanteras vara i ett tillstånd som senare kan återställas. När det här steget är klart pausas skrivåtgärderna på SQL Server (läsbegäranden tillåts fortfarande) och kontrollen överlämnas till säkerhetskopieringsprogrammet för att slutföra ögonblicksbilden. När ögonblicksbilden är klar måste programmet returnera kontrollen tillbaka till SQL Server där skrivåtgärderna sedan återupptas.

Eftersom vi måste frysa skrivåtgärder under ögonblicksbildsåtgärden är det viktigt att ögonblicksbilden sker snabbt, så att arbetsbelastningen på servern inte avbryts under en längre period. Tidigare förlitade sig användarna på icke-Microsoft-lösningar som byggdes ovanpå SQL Writer-tjänsten för att slutföra säkerhetskopieringar av ögonblicksbilder. SQL Writer-tjänsten är beroende av Windows VSS (Volume Shadow Service) tillsammans med SQL Server VDI (Virtual Device Interface) för att utföra orkestreringen mellan SQL Server och ögonblicksbilden på disknivå.

Säkerhetskopieringsklienter baserade på SQL Writer-tjänsten tenderar att vara komplexa och fungerar bara i Windows. Med säkerhetskopiering av T-SQL-ögonblicksbilder kan SQL Server-sidan av orkestreringen hanteras med en serie T-SQL-kommandon. Med den här funktionen kan användarna skapa egna små säkerhetskopieringsprogram som kan köras på antingen Windows eller Linux, eller till och med skriptlösningar om den underliggande lagringen stöder ett skriptgränssnitt för att initiera en ögonblicksbild.

Här är ett PowerShell-exempelskript, som visar en lösning från slutpunkt till slutpunkt för säkerhetskopiering och återställning av en databas i en virtuell Azure SQL IaaS-dator. Exemplet använder funktionerna för säkerhetskopiering av T-SQL-ögonblicksbilder som introducerades i SQL Server 2022 (16.x).

Arbetsflöde

Syntaxen för T-SQL-ögonblicksbildens säkerhetskopiering frikopplar den leverantörsberoende ögonblicksbildmekanismen från fördröjnings- och säkerhetskopieringsåtgärderna. Med den här syntaxen kan du:

Lås en databas med kommandot ALTER, vilket ger dig möjlighet att utföra ögonblicksbilden av den underliggande lagringen. Därefter kan du tina upp databasen och registrera ögonblicksbilden med kommandot BACKUP.
Utför ögonblicksbilder av flera databaser samtidigt med de nya kommandona BACKUP GROUP och BACKUP SERVER. Med det här alternativet kan ögonblicksbilder utföras på ögonblicksbildens nivå för den underliggande lagringen, vilket eliminerar behovet av att ta ögonblicksbilder av samma disk flera gånger.
Utför FULL säkerhetskopior och COPY_ONLY FULL säkerhetskopior. Dessa säkerhetskopior registreras även i msdb.
Utför återställning till specifik tidpunkt med hjälp av loggsäkerhetskopior som görs med den normala strömningsmetoden efter säkerhetskopian med ögonblicksbilden FULL. Differentiella säkerhetskopieringar för direktuppspelning stöds också om så önskas.

Notera

Differentiella bitmappar rensas under den första fasen när databasen pausas med kommandot ALTER. Om användaren bestämmer sig för att tina upp databasen utan att utföra en säkerhetskopia på grund av att ögonblicksbilden misslyckades eller av någon annan anledning är den differentiella bitmappen ogiltig. Eventuella efterföljande differentiella säkerhetskopior är mer I/O-intensiva, eftersom de måste skanna hela databasen för att göra differentiell säkerhetskopiering. Den differentiella bitmappen blir giltig igen efter en lyckad snapshot-säkerhetskopiering.

Följande diagram illustrerar arbetsflödet på hög nivå för säkerhetskopiering av T-SQL-ögonblicksbilder:

Det mellersta ögonblicksbildssteget kräver att du initierar ögonblicksbilden på den underliggande lagringen. Följande diagram visar ett exempel på hur ett säkerhetskopieringsskript kan fungera med SQL Server för att slutföra säkerhetskopieringen av ögonblicksbilder:

På samma sätt kan ett återställningsskript fungera på följande sätt:

Begränsningar

Det maximala antalet databaser som du kan säkerhetskopiera med den här funktionen är 64. Om det finns fler än 64 databaser på servern visas följande fel:

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.

Exempel

Följande avsnitt visar olika T-SQL-kommandon som används för att utföra säkerhetskopiering av ögonblicksbilder till disk. När en säkerhetskopiering av ögonblicksbilder skrivs till disk skrivs endast metadata som är anslutna till säkerhetskopieringen av ögonblicksbilden till filen. Utdata innehåller inte något av databasinnehållet förutom rubriken och filinnehållet. Shell-filen som skapas som en del av säkerhetskopieringen av ögonblicksbilder ska användas med den faktiska ögonblicksbilds-URI:n för att göra en fullständig säkerhetskopia. En RESTORE av en databas från den här filen kräver att användaren kopierar databasfilerna från ögonblicksbildens URI till monteringspunkten innan RESTORE-kommandot utfärdas. Användarna kan köra alla traditionella T-SQL-kommandon, till exempel RESTORE HEADERONLY och RESTORE FILELISTONLY, på den här metadatafilen för säkerhetskopiering av ögonblicksbilder, tillsammans med RESTORE DATABASE. Syntaxen stöder skrivning av metadata för säkerhetskopiering av ögonblicksbilder till DISK eller URL. Säkerhetskopieringsuppsättningar för ögonblicksbilder kan också läggas till precis som säkerhetskopieringsuppsättningar för direktuppspelning till en enda fil.

Not

För säkerhetskopiering till URL är blockblobbar att föredra, även om sidblobbar stöds för SQL Server i Windows. För SQL Server på Linux och containrar stöds endast blockblobar.

A. Pausa en enskild användardatabas för säkerhetskopiering av ögonblicksbilder och registrera en säkerhetskopia av en databas

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

B. Pausa flera användardatabaser för säkerhetskopiering av ögonblicksbilder

Om flera databaser finns på samma underliggande disk kan du pausa flera databaser med följande kommando.

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. Pausa alla användardatabaser på servern för säkerhetskopiering av ögonblicksbilder

Om alla användardatabaser på servern måste pausas använder du följande kommando.

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

Not

Inget av dessa kommandon har stöd för att pausa systemdatabaser (master, modeloch msdb) för säkerhetskopiering av ögonblicksbilder.

D. Pausa flera användardatabaser med ett enda kommando

Registrera en ögonblicksbild av alla användardatabaser på servern i en enda säkerhetskopieringsuppsättning:

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;

Not

Som standard rensar SUSPEND_FOR_SNAPSHOT_BACKUP-kommandot den differentiella bitmappen. Om du föredrar att endast utföra en kopieringssäkerhetskopia använder du nyckelordet COPY_ONLY enligt följande exempel.

E. Utföra säkerhetskopiering av ögonblicksbilder endast för kopiering

Eftersom den differentiella bitmappen rensas före frysningen ger SUSPEND_FOR_SNAPSHOT_BACKUP ett alternativ (COPY_ONLY) för att inte rensa differentiell bitmapp före frysningen.

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;

Anteckning

Det är inte nödvändigt att använda COPY_ONLY i kommandot BACKUP, eftersom det redan har angetts när databasen för säkerhetskopiering av ögonblicksbilder pausas.

F. Säkerhetskopiera en databas med data och loggfiler på olika enheter

Om du har en databas med datafiler (.mdf och .ndf) på flera enheter och transaktionsloggfilen (.ldf) på en annan enhet kan du utföra en säkerhetskopiering av ögonblicksbilder på följande sätt:

Pausa databasen (vilket låser skriv-I/O på både data- och loggfiler).
```
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
```
Ta en ögonblicksbild av alla de underliggande diskarna där databasdata och loggfiler finns. Det här steget är maskinvaruberoende.
Utför säkerhetskopieringen med alternativet METADATA_ONLY, vilket skapar utdata som innehåller metadata för säkerhetskopiering av ögonblicksbilder (.bkm).
```
BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;
```

Följ dessa steg för att återställa den här säkerhetskopian i ett senare skede:

Montera eller koppla ögonblicksbilddiskarna på den virtuella dator där du vill återställa.
Använd filen .bkm (från steg 3 i föregående lista) när du utför en databasåterställning.
Om enheterna skiljer sig under återställningen använder du kommandot MOVE för att placera de logiska filerna i den önskade destinationen. Ett exempel finns i Exempel N.

G. Tagga säkerhetskopian

Du kan använda alternativen MEDIANAME och MEDIADESCRIPTION i säkerhetskopieringskommandot för att tagga den URI som är associerad med ögonblicksbilden. Med den här användningen kan säkerhetskopieringsfilen bära den underliggande ögonblicksbildsinformationen tillsammans med databasmetadata. Du kan också använda alternativen NAME och DESCRIPTION för att tagga URI:n med den individuella ögonblicksbilden av säkerhetskopieringsuppsättningen.

SQL Server tolkar inte LABEL information på något sätt. Det hjälper dock användaren att visa den URI som är associerad med säkerhetskopieringen av ögonblicksbilden med kommandot RESTORE LABELONLY.

Du kan sedan koppla ögonblicksbilddiskarna som finns vid URI:n till den virtuella datorn för att återställa ögonblicksbilden. Ögonblicksbilds-URI:n som lagras i MEDIANAME och MEDIADESCRIPTION är också tillgänglig för visning i msdb databastabellen dbo.backupmediaset.

H. Utdata från säkerhetskopiering av ögonblicksbilder med RESTORE HEADERONLY

Utdata med RESTORE HEADERONLY ser ut som följande exempel, om databasen, gruppen och servern körs i följd och skrivs till samma utdatafil:

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

Jag. Utdata från säkerhetskopiering av ögonblicksbilder med RESTORE FILELISTONLY

Utdata med RESTORE FILELISTONLY visar den första säkerhetskopieringen som standard:

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

J. Filtrera RESTORE FILELISTONLY-utdata till en säkerhetskopieringsuppsättning

Om du vill välja en viss säkerhetskopieringsuppsättning från flera säkerhetskopieringsuppsättningar med RESTORE FILELISTONLYanvänder du FILE-satsen som redan stöds på RESTORE FILELISTONLY.

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

Skärmbild av SSMS-utdata till säkerhetskopieringsuppsättning från fråga.

K. Filtrera RESTORE FILELISTONLY-utdata till en databas

Om du vill välja en enskild databas från flera databaser i den valda säkerhetskopieringsuppsättningen med RESTORE FILELISTONLYanvänder du FILE-satsen med DBNAME-satsen. Satsen DBNAME kan endast användas på säkerhetskopieringsuppsättningar för ögonblicksbilder.

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

Skärmbild av resultatet av filtrering av RESTORE FILELISTONLY-utdata till en databas.

L. Återställa en ögonblicksbilddatabas

Att återställa en databas från säkerhetskopiering av ögonblicksbilder är som att koppla en databas. Kör återställningskommandot utan alternativet RECOVERY om databasen behöver kopplas utan återställning. Som standard väljer RESTORE den första databasen i uppsättningen för säkerhetskopiering av ögonblicksbilder. I följande exempel återställs testdb1. Om testdb1 redan finns på servern inkluderar du satsen REPLACE. Du måste montera databasfilerna innan du kör RESTORE.

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. Återställa en ögonblicksbilddatabas som visas i mitten

Om databasen som måste vara RESTORED är i mitten anger du den databas som ska återställas med DBNAME-satsen. Följande syntax återställer den angivna databasen i DBNAME-satsen.

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. Återställa databasen med ett annat namn

Du kan återställa databasen med ett annat namn. Om databasen som måste vara RESTORED är i mitten anger du den databas som ska återställas med DBNAME-satsen. Följande syntax återställer den angivna databasen med DBNAME-satsen och byter namn på den till 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. Använd RESTORE BACKUPSETONLY för att extrahera databaser från en säkerhetskopia som innehåller flera databaser

En ögonblicksbildssäkerhetskopia som innehåller flera databaser från en grupp eller server kan delas med kommandot RESTORE BACKUPSETONLY. Det här kommandot genererar en säkerhetskopieringsuppsättning per databas.

Om en serverögonblicksbild innehåller tre databaser i en säkerhetskopia som innehåller en enda säkerhetskopieringsuppsättning genererar följande kommando tre säkerhetskopieringsuppsättningar, en för varje databas. Den skapar en katalog med <file_name_prefix>_<unique_time_stamp> för utdatafilerna.

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

P. Använd RESTORE BACKUPSETONLY för att extrahera en specifik databas i en säkerhetskopia som innehåller flera databaser

RESTORE BACKUPSETONLY stöder parametern DBNAME om användaren vill mata ut en databas från de tre databaserna i säkerhetskopieringsuppsättningen. Den stöder också parametern FILE för att filtrera flera säkerhetskopieringsuppsättningar i säkerhetskopieringsfilen.

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

Q. Övervaka pausstatusen och de lås som har förvärvats

Du kan använda följande dynamiska hanteringsvyer (DMV:er):

sys.dm_server_suspend_status (visa uppehållsstatus)
sys.dm_tran_locks (visa de inköpta låsen)

R. Lista information om säkerhetskopior

Följande exempelskript visar information om säkerhetskopieringsuppsättningar för Transact-SQL säkerhetskopiering av ögonblicksbilder.

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

S. Kontrollera om en databas har pausats för säkerhetskopiering av ögonblicksbilder

Följande exempelskript matar ut egenskaper på databasnivå för databaser som pausas för säkerhetskopiering av ögonblicksbilder.

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

T. Exempel på felsökningsskript för T-SQL

Följande exempelskript identifierar inaktiverade databaser på servern och avanvänder dem om det behövs.

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

Dela via