Maak een back-up en herstel SQL Server-databases

Van toepassing op:SQL Server

Dit artikel beschrijft de voordelen van het back-uppen van SQL Server-databases, introduceert basistermen voor back-up en herstel, en behandelt back-up- en herstelstrategieën en beveiligingsoverwegingen voor SQL Server.

De SQL Server-back-up- en herstelcomponent biedt een essentiële beveiliging voor kritieke gegevens die in je SQL Server-databases zijn opgeslagen. Om het risico op catastrofale dataverlies te minimaliseren, maak je regelmatig back-ups van je databases om wijzigingen in je data te behouden. Een goed doordachte back-up- en herstelstrategie helpt databases te beschermen tegen dataverlies veroorzaakt door vele soorten storingen. Test je strategie door een set back-ups te herstellen en vervolgens je database te herstellen, zodat je klaar bent om op een ramp te reageren.

Naast lokale opslag ondersteunt SQL Server ook back-ups naar en herstel vanuit Azure Blob Storage. Voor meer informatie, zie SQL Server backup en herstel met Azure Blob Storage. Voor databasebestanden die zijn opgeslagen met Behulp van Azure Blob Storage, biedt SQL Server 2016 (13.x) de optie om Azure-momentopnamen te gebruiken voor bijna directe back-ups en snellere herstelbewerkingen. Zie Back-ups van bestandssnapshots voor databasebestanden in Azure voor meer informatie. Azure biedt ook een bedrijfsgebaseerde back-upoplossing voor SQL Server die wordt uitgevoerd op azure-VM's. Een volledig beheerde back-upoplossing biedt ondersteuning voor AlwaysOn-beschikbaarheidsgroepen, langetermijnretentie, herstel naar een bepaald tijdstip en centraal beheer en bewaking. Zie Back-up van SQL Server op Virtuele Azure-machines voor meer informatie.

Waarom een back-up maken?

• Het back-uppen van je SQL Server-databases, het uitvoeren van testherstelprocedures op je back-ups en het opslaan van kopieën van back-ups op een veilige, externe locatie beschermt je tegen potentieel catastrofale dataverlies. Back-ups maken is de enige manier om uw gegevens te beveiligen.

  Met geldige back-ups van een database kunt u uw gegevens herstellen van veel fouten, zoals:

  • Mediafout.

  • Gebruikersfouten, bijvoorbeeld, een tabel per ongeluk verwijderen.

  • Hardwarefouten, bijvoorbeeld een beschadigd schijfstation of permanent verlies van een server.

  • Natuurrampen. Door SQL Server Backup te gebruiken naar Azure Blob Storage kun je een off-site back-up maken in een andere regio dan je on-premises locatie, om te gebruiken als een natuurramp je on-premises locatie treft.

• Daarnaast zijn back-ups van een database handig voor routinebeheerdoeleinden, zoals het kopiëren van een database van de ene server naar de andere, het instellen van AlwaysOn-beschikbaarheidsgroepen of het spiegelen van databases en archivering.

Woordenlijst met back-uptermen

Strategieën voor back-up en herstel

Je moet back-up- en herstelstrategieën aanpassen aan je omgeving en beschikbare bronnen. Betrouwbaar herstel vereist een back-up- en herstelstrategie. Een goed ontworpen strategie balanceert de zakelijke eisen voor maximale databeschikbaarheid en minimaal dataverlies tegen de kosten van het onderhouden en opslaan van back-ups.

Een back-up- en herstelstrategie bevat een back-upgedeelte en een herstelgedeelte. Het back-upgedeelte definieert het type en de frequentie van back-ups, het type en de snelheid van de benodigde hardware, hoe back-ups te testen, en waar en hoe back-upmedia op te slaan (inclusief beveiligingsoverwegingen). Het herstelgedeelte definieert wie verantwoordelijk is voor het uitvoeren van herstels, hoe je herstels uitvoert om je doelen voor databasebeschikbaarheid en minimaal dataverlies te bereiken, en hoe je herstelopdrachten test.

Een effectieve back-up- en herstelstrategie vereist zorgvuldige planning, implementatie en testen. Testen zijn verplicht. Je hebt geen back-upstrategie totdat je succesvol back-ups hebt hersteld in elke combinatie die in je herstelstrategie is opgenomen en elke herstelde database test op fysieke consistentie. Houd rekening met verschillende factoren, waaronder:

• De doelstellingen van uw organisatie met betrekking tot uw productiedatabases, met name de vereisten voor beschikbaarheid en bescherming van gegevens tegen verlies of schade.

• De aard van elke database: de grootte, de gebruikspatronen, de aard van de inhoud, de vereisten voor de gegevens, enzovoort.

• Beperkingen voor resources, zoals hardware, personeel, ruimte voor het opslaan van back-upmedia, de fysieke beveiliging van de opgeslagen media, enzovoort.

Aanbevelingen voor best practices

Geef de accounts die back-ups of hersteloperaties uitvoeren geen meer privileges dan nodig is. Voor meer informatie, zie back-up en herstel voor specifieke toestemmingsdetails. Versleutel database-back-ups en, indien mogelijk, comprimeer ze.

Gebruik consistente bestandsextensies om back-ups makkelijker te identificeren en te beheren. SQL Server vereist of handhaaft deze extensies niet, maar consistentie helpt bij operationele taken zoals het configureren van antivirusuitsluitingen voor back-upbestanden. Zie Antivirussoftware configureren voor gebruik met SQL Server voor meer informatie.

• Database-back-upbestanden zouden de .BAK extensie moeten hebben.
• Logboekback-upbestanden moeten de .TRN extensie hebben.

Gebruik aparte opslag

Plaats je database-back-ups op een aparte fysieke locatie of op een apart apparaat dan de databasebestanden. Wanneer de fysieke schijf die je databases opslaat uitvalt of crasht, hangt herstel af van je vermogen om toegang te krijgen tot de aparte schijf of het externe apparaat waarop de back-ups zijn opgeslagen. Je kunt meerdere logische volumes of partities aanmaken vanaf dezelfde fysieke schijf. Bekijk zorgvuldig de schijfpartitie- en logische volumelay-outs voordat je een opslaglocatie voor de back-ups kiest.

Het juiste herstelmodel kiezen

Back-up- en herstelbewerkingen worden uitgevoerd binnen de context van een herstelmodel. Een herstelmodel is een database-eigenschap die bepaalt hoe het transactielogboek wordt beheerd. Daarom bepaalt het herstelmodel van een database welke soorten back-up- en herstelscenario's de database ondersteunt, en de grootte van de back-ups van de transactielogboeken. Normaal gesproken maakt een database gebruik van het eenvoudige herstelmodel of het volledige herstelmodel. U kunt het volledige herstelmodel aanvullen door vóór bulkbewerkingen over te schakelen naar het bulkgewijs gelogde herstelmodel. Zie het transactielogboek voor een inleiding tot deze herstelmodellen en hoe deze van invloed zijn op transactielogboekbeheer.

De beste keuze voor databaseherstelmodel hangt af van de eisen van uw bedrijf. Gebruik het eenvoudige herstelmodel om het beheer van transactielogboeken te voorkomen en back-up en herstel te vereenvoudigen. Gebruik het volledige herstelmodel om het risico op werkverlies te minimaliseren, ten koste van extra beheeroverhead. Om het effect op de loggrootte tijdens bulk-logged operaties te minimaliseren en toch herstel van die operaties mogelijk te maken, gebruik je het bulk-logged recovery-model. Voor informatie over het effect van herstelmodellen op back-up en herstel, zie Backup overzicht (SQL Server).

Uw back-upstrategie ontwerpen

Nadat je een herstelmodel hebt gekozen dat voldoet aan je bedrijfsbehoeften voor een specifieke database, plan en implementeer je een bijpassende back-upstrategie. De beste back-upstrategie hangt af van verschillende factoren. De volgende factoren zijn bijzonder belangrijk:

• Hoeveel uur per dag hebben applicaties nodig om toegang te krijgen tot de database?

  Als er een voorspelbare laagperiode is, moet je volledige databaseback-ups voor die periode plannen.

• Hoe vaak zijn wijzigingen en updates waarschijnlijk?

  Als de wijzigingen frequent zijn, overweeg dan:

  • Onder het eenvoudige herstelmodel kun je differentiële back-ups plannen tussen volledige database-back-ups. Een differentiële back-up legt alleen de wijzigingen vast sinds de laatste volledige databaseback-up.

  • Onder het volledige herstelmodel kun je frequente logback-ups plannen. Het plannen van differentiële back-ups tussen volledige back-ups kan de hersteltijd verminderen door het aantal logboekback-ups te verminderen dat u moet herstellen nadat u de gegevens hebt hersteld.

• Zullen veranderingen waarschijnlijk slechts in een klein deel van de database plaatsvinden, of in een groot deel?

  Voor een grote database waarin wijzigingen geconcentreerd zijn in een subset van de bestanden of bestandsgroepen, kunnen gedeeltelijke back-ups of volledige bestandsback-ups nuttig zijn. Voor meer informatie, zie Partial Backups (SQL Server) en Full File Backups (SQL Server).

• Hoeveel schijfruimte heeft een volledige database-back-up nodig?

• Hoe ver in het verleden moet uw bedrijf back-ups onderhouden?

  Zorg ervoor dat je een goed back-upschema hebt dat aansluit bij de behoeften en zakelijke eisen van de applicatie. Naarmate back-ups ouder worden, neemt het risico op dataverlies toe, tenzij je een manier hebt om alle data tot het punt van uitval te regenereren. Voordat je oude back-ups verwijdert vanwege beperkte opslagruimte, moet je nagaan of je herstel van zo ver in het verleden nodig hebt.

De grootte van een volledige databaseback-up schatten

Voordat je een back-up- en herstelstrategie implementeert, schat in hoeveel schijfruimte een volledige database-back-up gebruikt. Met de back-upbewerking worden de gegevens in de database gekopieerd naar het back-upbestand. De back-up bevat alleen de daadwerkelijke gegevens in de database, geen ongebruikte ruimte. Daarom is de back-up meestal kleiner dan de database zelf. Om de grootte van een volledige database-back-up te schatten, gebruik je de sp_spaceused systeemopgeslagen procedure. Zie sp_spaceusedvoor meer informatie.

Back-ups plannen

Een back-up heeft minimale invloed op het uitvoeren van transacties, dus je kunt back-ups maken tijdens normale operaties. U kunt een SQL Server-back-up uitvoeren met minimaal effect op productieworkloads.

Nadat je hebt besloten welke soorten back-ups je nodig hebt en hoe vaak je elk type uitvoert, plan dan regelmatige back-ups in als onderdeel van een databaseonderhoudsplan. Zie de wizard Onderhoudsplan gebruiken voor informatie over onderhoudsplannen en het maken ervan voor databaseback-ups en logboekback-ups.

Uw back-ups testen

U hebt pas een herstelstrategie als u uw back-ups test. Test je back-upstrategie grondig voor elke database door een kopie van de database terug te zetten op een testsysteem. U moet het terugzetten van elk type back-up dat u wilt gebruiken testen. Nadat je de back-up hebt hersteld, voer je DBCC CHECKDB uit op de database om te controleren of het back-upmedium niet beschadigd is.

Mediastabiliteit en consistentie controleren

Gebruik de verificatieopties die worden aangeboden door de back-uphulpprogramma's (BACKUPT-SQL-commando, SQL Server Maintenance Plans, je back-upsoftware of -oplossing, enzovoort). Zie RESTORE Statements - VERIFYONLY voor een voorbeeld.

Gebruik geavanceerde functies zoals BACKUP CHECKSUM om problemen met het back-upmedium zelf te detecteren. Zie Possible Media Errors During Backup and Restore (SQL Server) voor meer informatie.

Strategie voor documentback-up/herstel

Documenteer je back-up- en herstelprocedures en bewaar een kopie van de documentatie in je runbook.

Je moet ook een bedieningshandleiding bijhouden voor elke database. Deze bedieningshandleiding moet de locatie van de back-ups, de namen van back-upapparaten (indien aanwezig) en de tijd die nodig is om de testback-ups te herstellen documenteren.

Beveiligingsrisico voor het herstellen van back-ups van niet-vertrouwde bronnen

In deze sectie wordt een overzicht gegeven van het beveiligingsrisico dat is gekoppeld aan het herstellen van back-ups van niet-vertrouwde bronnen naar een SQL Server-omgeving, waaronder on-premises, Azure SQL Managed Instance, SQL Server op virtuele Azure-machines (VM's) en andere omgevingen.

Waarom dit belangrijk is

Het herstellen van SQL-back-upbestanden (.bak) brengt een potentieel risico met zich mee als de back-up afkomstig is van een niet-vertrouwde bron. Het beveiligingsrisico wordt verder verergerd wanneer een SQL Server-omgeving meerdere exemplaren heeft, omdat dit het bedreigingsgebied versterkt. Hoewel back-ups die binnen een vertrouwde grens blijven geen beveiligingsprobleem vormen, kan het herstellen van een schadelijke back-up de beveiliging van de hele omgeving in gevaar brengen.

Een schadelijk .bak bestand kan:

• Neem het hele SQL Server-exemplaar over.
• Escaleer bevoegdheden en krijg onbevoegde toegang tot de onderliggende host of virtuele machine.

Deze aanval vindt plaats voordat eventuele validatiescripts of beveiligingscontroles kunnen worden uitgevoerd, waardoor het uiterst gevaarlijk is. Het herstellen van een niet-vertrouwde back-up is gelijk aan het uitvoeren van niet-vertrouwde toepassingen op een kritieke server of virtuele machine en het introduceren van willekeurige code-uitvoering in uw omgeving.

Beste praktijken

Volg deze aanbevolen procedures voor back-upbeveiliging om de bedreiging voor uw SQL Server-omgevingen te verminderen:

• Herstel van back-ups behandelen als een bewerking met een hoog risico.
• Verminder het bedreigingsservicegebied met behulp van geïsoleerde exemplaren.
• Alleen vertrouwde back-ups toestaan: herstel nooit back-ups van onbekende of externe bronnen.
• Alleen back-ups toestaan die binnen een vertrouwde grens zijn gebleven: ervoor zorgen dat back-ups afkomstig zijn van binnen de vertrouwde grens.
• Omzeil beveiligingscontroles niet voor het gemak.
• Schakel controle op serverniveau in om back-up - en herstelgebeurtenissen vast te leggen en controleontduiking te beperken.

Voortgang bewaken met XEvent

Back-up- en hersteloperaties kunnen lang duren vanwege de omvang van een database en de complexiteit van de betrokken bewerkingen. Wanneer er problemen optreden bij een van beide operaties, gebruik dan het backup_restore_progress_trace uitgebreide evenement om de voortgang live te monitoren. Zie Overzicht van uitgebreide gebeurtenissen voor meer informatie over uitgebreide gebeurtenissen.

-- Create the backup_restore_progress_trace extended event session
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file (SET filename = N'BackupRestoreTrace')
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

-- Start the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = START;
GO

-- Stop the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = STOP;
GO

Voorbeelduitvoer van een Extended Event

Meer informatie over back-uptaken

• Een onderhoudsplan maken
• Een SQL Server Agent-taak maken in SQL Server Management Studio
• Planning configureren voor SQL Server Agent-taak

Werken met back-upapparaten en back-upmedia

• Een logisch back-upapparaat definiëren voor een schijfbestand (SQL Server)
• Een logisch back-upapparaat definiëren voor een tapestation (SQL Server)
• Een schijf- of tapeback-upbestemming opgeven (SQL Server)
• Een back-upapparaat verwijderen (SQL Server)
• De vervaldatum instellen voor een back-up (SQL Server)
• De inhoud van een back-upband of -bestand weergeven (SQL Server)
• De gegevens en logboekbestanden weergeven in een back-upset (SQL Server)
• De eigenschappen en inhoud van een logisch back-upapparaat (SQL Server) weergeven
• Een back-up herstellen vanaf een apparaat (SQL Server)

Back-ups maken

Voor gedeeltelijke back-ups of back-ups van alleen een kopie gebruik je respectievelijk de Transact-SQL-instructie BACKUP met de optie PARTIAL of COPY_ONLY.

SSMS gebruiken

• Een volledige databaseback-up maken
• Een back-up maken van een transactielogboek
• Maak een back-up van bestanden en bestandsgroepen
• Een differentiële databaseback-up maken (SQL Server)

T-SQL gebruiken

• Gebruik Resource Governor om het CPU-gebruik te beperken door back-upcompressie
• Een back-up maken van het transactielogboek wanneer de database is beschadigd (SQL Server)
• Back-upcontrolesommen in- of uitschakelen tijdens back-up of herstel (SQL Server)
• Back-up of herstel opgeven om door te gaan of te stoppen na een fout

Back-ups van gegevens herstellen

SSMS gebruiken

• Een databaseback-up herstellen met behulp van SSMS
• Een database herstellen naar een nieuwe locatie (SQL Server)
• Een back-up van een differentiële database herstellen (SQL Server)
• Bestanden en bestandsgroepen herstellen (SQL Server)

T-SQL gebruiken

• Herstel een database-back-up onder het eenvoudige herstelmodel
• Database herstellen naar storingspunt - volledig herstel
• Bestanden en bestandsgroepen herstellen over bestaande bestanden (SQL Server)
• Bestanden herstellen naar een nieuwe locatie (SQL Server)
• Herstel de masterdatabase

Transactielogboeken herstellen (volledig herstelmodel)

SSMS gebruiken

• Een database herstellen naar een gemarkeerde transactie (SQL Server Management Studio)
• Een back-up van een transactielogboek herstellen (SQL Server)
• Herstel een SQL Server-database naar een bepaald moment (volledig herstelmodel)

T-SQL gebruiken

• Herstel een SQL Server-database naar een bepaald moment (volledig herstelmodel)
• een onderbroken herstelbewerking opnieuw starten
• Herstel een database zonder data te herstellen

Verwante inhoud

• Overzicht van back-ups (SQL Server)
• Overzicht van terugzetten en herstel (SQL Server)
• BACKUP (Transact-SQL)
• RESTORE Instructies (Transact-SQL)
• Back-up en herstel van Analysis Services-databases
• Maak een back-up en herstel volledige tekstcatalogi en indexen
• Maak een back-up en herstel gerepliceerde databases
• Het transactielogboek
• Herstelmodellen (SQL Server)
• Mediasets, mediafamilies en back-upsets (SQL Server)