Een volledige databaseback-up maken

van toepassing op:SQL Server-

In dit artikel wordt beschreven hoe u een volledige databaseback-up maakt in SQL Server met behulp van SQL Server Management Studio, Transact-SQL of PowerShell.

Zie back-up en herstel van SQL Server met Azure Blob Storage en SQL Server-back-up naar URL voor Azure Blob Storage voor meer informatie.

Beperkingen

• De BACKUP-instructie is niet toegestaan in een expliciete of impliciete transactie.
• Back-ups die zijn gemaakt door recentere versies van SQL Server, kunnen niet worden hersteld in eerdere versies van SQL Server.

Zie Back-upoverzicht (SQL Server) voor een overzicht van en meer informatie over back-upconcepten en -taken voordat u doorgaat.

Aanbevelingen

• Naarmate een database groter wordt, duurt het langer om volledige databaseback-ups te voltooien en is er meer opslagruimte nodig. Voor grote databases kunt u overwegen volledige databaseback-ups aan te vullen met een reeks differentiële databaseback-ups.
• Maak een schatting van de grootte van een volledige databaseback-up met behulp van de sp_spaceused door het systeem opgeslagen procedure.
• Bij elke geslaagde back-upbewerking wordt standaard een vermelding toegevoegd aan het SQL Server-foutenlogboek en in het gebeurtenislogboek van het systeem. Als u regelmatig een back-up maakt, worden succesberichten snel verzameld, wat resulteert in enorme foutenlogboeken, waardoor het vinden van andere berichten moeilijk is. In dergelijke gevallen kunt u deze back-uplogboekvermeldingen onderdrukken met behulp van traceringsvlag 3226 als geen van uw scripts afhankelijk is van deze vermeldingen. Zie Traceringsvlagmen instellen met DBCC TRACEON voor meer informatie.

Veiligheid

TRUSTWORTHY is ingesteld op OFF een back-up van een database. Zie TRUSTWORTHY voor meer informatie over hoe u ONdit instelt.

Vanaf SQL Server 2012 (11.x) zijn de PASSWORD en MEDIAPASSWORD opties niet beschikbaar voor het maken van back-ups. U kunt nog steeds back-ups herstellen die zijn gemaakt met wachtwoorden.

Machtigingen

BACKUP DATABASE- en BACKUP LOG-machtigingen zijn standaard ingesteld op leden van de sysadmin vaste serverfunctie en de db_owner en db_backupoperator vaste databaserollen.

Eigendoms- en machtigingsproblemen in het fysieke bestand van het back-upapparaat kunnen een back-upbewerking verstoren. De SQL Server-service moet lezen van en schrijven naar het apparaat. Het account waaronder de SQL Server-service wordt uitgevoerd, moet schrijfmachtigingen hebben voor het back-upapparaat. Echter, sp_addumpdevice, waarmee een vermelding voor een back-upapparaat in de systeemtabellen wordt toegevoegd, controleert geen machtigingen voor bestandstoegang. Problemen in het fysieke bestand van het back-upapparaat worden mogelijk pas weergegeven als de back-up wordt gebruikt of een poging tot herstel is uitgevoerd.

SQL Server Management Studio gebruiken

1. Nadat u verbinding hebt gemaakt met het juiste exemplaar van de SQL Server Database Engine, vouwt u in Objectverkenner de serverstructuur uit.

2. Vouw Databasesuit en selecteer een gebruikersdatabase of vouw Systeemdatabases uit en selecteer een systeemdatabase.

3. Klik met de rechtermuisknop op de database waarvoor u een back-up wilt maken, wijs Taken aan en selecteer Vervolgens Back-up maken....

4. In het dialoogvenster Back-updatabase wordt de geselecteerde database weergegeven in de vervolgkeuzelijst. (U kunt de database wijzigen in elke andere database op de server.)

5. Selecteer een back-uptype in de lijst Back-uptypen . De standaardwaarde is Vol.

   Belangrijk

   U moet ten minste één volledige databaseback-up uitvoeren voordat u een differentiële of transactielogboekback-up kunt uitvoeren.

6. Selecteer Databaseonder Back-uponderdeel.

7. Controleer in de sectie Bestemming de standaardlocatie voor het back-upbestand (in de .. /mssql/data folder).

   U kunt de back-up gebruiken om een ander apparaat te selecteren. Selecteer Toevoegen om back-upobjecten en/of bestemmingen toe te voegen. U kunt de back-up set over meerdere bestanden spreiden voor een snellere back-upsnelheid.

   Als u een back-upbestemming wilt verwijderen, selecteert u deze en selecteert u Verwijderen. Als u de inhoud van een bestaande back-upbestemming wilt weergeven, selecteert u deze en selecteert u Vervolgens Inhoud.

8. (Optioneel) Bekijk de andere beschikbare instellingen op de pagina's Mediaopties en Back-upopties .

   Zie Back-updatabase (algemene pagina) maken, back-up maken van database (pagina Mediaopties) en back-up maken van database (pagina Back-upopties) en back-up maken van database (pagina Back-upopties) voor meer informatie over de verschillende back-upopties.

9. Selecteer OK- om de back-up te starten.

10. Wanneer de back-up is voltooid, selecteert u OK om het dialoogvenster SQL Server Management Studio te sluiten.

Aanvullende informatie

• Nadat u een volledige databaseback-up hebt gemaakt, kunt u een differentiële databaseback-up maken of een transactielogboekback-up.

• (Optioneel) U kunt het selectievakje Alleen-kopiëren inschakelen om een alleen-kopiëren back-up te maken. Een alleen-kopiëren back-up is een SQL Server-back-up die onafhankelijk is van de reeks conventionele SQL Server-back-ups. Zie Alleen-kopiëren back-ups voor meer informatie. Er is geen back-up met alleen kopiëren beschikbaar voor het differentiële back-uptype.

• De optie Media overschrijven is uitgeschakeld op de pagina Mediaopties als u een back-up maakt van een URL.

Voorbeelden

Maak voor de volgende voorbeelden een testdatabase met de volgende Transact-SQL code:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

Een. Volledige back-up naar schijf naar de standaardlocatie

In dit voorbeeld wordt er een back-up van de SQLTestDB database gemaakt op schijf op de standaardback-uplocatie.

1. Nadat u verbinding hebt gemaakt met het juiste exemplaar van de SQL Server Database Engine, vouwt u in Objectverkenner de serverstructuur uit.

2. Vouw Databasesuit, klik met de rechtermuisknop op SQLTestDB, wijs Takenaan en selecteer Back-up....

3. Selecteer OK-.

4. Wanneer de back-up is voltooid, selecteert u OK om het dialoogvenster SQL Server Management Studio te sluiten.

B. Volledige back-up naar schijf naar een niet-standaardlocatie

In dit voorbeeld wordt een back-up van de SQLTestDB database gemaakt op schijf op een locatie die u kiest.

1. Nadat u verbinding hebt gemaakt met het juiste exemplaar van de SQL Server Database Engine, vouwt u in Objectverkenner de serverstructuur uit.

2. Vouw Databasesuit, klik met de rechtermuisknop op SQLTestDB, wijs Takenaan en selecteer Back-up....

3. Selecteer Op de pagina Algemeen in de sectie Bestemmingde optie Schijf in de lijst Back-up maken .

4. Selecteer Verwijderen totdat alle bestaande back-upbestanden worden verwijderd.

5. Selecteer Toevoegen. Het dialoogvenster Back-updoel selecteren wordt geopend.

6. Voer een geldig pad en een geldige bestandsnaam in het vak Bestandsnaam in. Gebruik .bak als extensie om de classificatie van het bestand te vereenvoudigen.

7. Selecteer OK- en selecteer vervolgens OK opnieuw om de back-up te starten.

8. Wanneer de back-up is voltooid, selecteert u OK om het dialoogvenster SQL Server Management Studio te sluiten.

C. Een versleutelde back-up maken

In dit voorbeeld wordt een back-up van de SQLTestDB database gemaakt met versleuteling naar de standaardback-uplocatie.

1. Nadat u verbinding hebt gemaakt met het juiste exemplaar van de SQL Server Database Engine, vouwt u in Objectverkenner de serverstructuur uit.

2. Vouw Databases uit, vouw Systeemdatabases uit, klik met de rechtermuisknop masteren selecteer vervolgens Nieuwe query om een queryvenster te openen met een verbinding met uw SQLTestDB database.

3. Voer de volgende opdrachten uit om een databasehoofdsleutel en een certificaat in de master database te maken.

   -- Create the master key.
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
   
   -- If the master key already exists, open it in the same session that you create the certificate. (See next step.)
   OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'
   
   -- Create the certificate encrypted by the master key.
   CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';
   

4. Klik in Objectverkennerin het knooppunt Databases met de rechtermuisknop op SQLTestDB, wijs Taken aan en selecteer Back Up....

5. Selecteer op de pagina Mediaopties in de sectie Media overschrijven de optie Back-up maken naar een nieuwe mediaset en wis alle bestaande back-upsets.

6. Selecteer Op de pagina Back-upopties in de sectie Versleuteling de optie Back-up versleutelen.

7. Selecteer AES 256 in de lijst Algoritme.

8. Selecteer in de lijst MyCertificate.

9. Selecteer OK-.

D. Een back-up maken van Azure Blob Storage

In dit voorbeeld wordt een volledige databaseback-up van SQLTestDB Azure Blob Storage gemaakt. Het voorbeeld wordt geschreven met de aanname dat u al een opslagaccount met een blobcontainer hebt. In het voorbeeld wordt een handtekening voor gedeelde toegang gemaakt. Het voorbeeld mislukt als de container een bestaande Shared Access Signature heeft.

Als u geen Blob Storage-container in een opslagaccount hebt, maakt u er een voordat u doorgaat. Zie Een opslagaccount voor algemeen gebruik maken en Een container maken.

1. Nadat u verbinding hebt gemaakt met het juiste exemplaar van de SQL Server Database Engine, vouwt u in Objectverkenner de serverstructuur uit.

2. Vouw Databasesuit, klik met de rechtermuisknop op SQLTestDB, wijs Takenaan en selecteer Back-up....

3. Selecteer op de pagina Algemeen in de sectie Bestemming de URL in de lijst Back-up maken .

4. Selecteer Toevoegen. Het dialoogvenster Back-updoel selecteren wordt geopend.

5. Als u eerder de Azure Storage-container hebt geregistreerd die u wilt gebruiken met SQL Server Management Studio, selecteert u deze. Selecteer anders Nieuwe container om een nieuwe container te registreren.

6. Meld u aan bij uw account in het dialoogvenster Verbinding maken met een Microsoft-abonnement .

7. Selecteer uw opslagaccount in het vak Opslagaccount selecteren.

8. Selecteer uw blobcontainer in het vak Blobcontainer selecteren.

9. Selecteer in het vak Vervaldatum van gedeeld toegangsbeleid een vervaldatum voor het beleid voor gedeelde toegang dat u in dit voorbeeld maakt.

10. Selecteer Referentiegegevens maken om een gedeelde toegangshandtekening en referentiegegevens te genereren in SQL Server Management Studio.

11. Selecteer OK sluit het dialoogvenster Verbinding maken met een Microsoft-abonnement .

12. Wijzig in het vak Back-upbestand desgewenst de naam van het back-upbestand.

13. Selecteer OK om het dialoogvenster Een back-upbestemming selecteren te sluiten.

14. Selecteer OK- om de back-up te starten.

15. Wanneer de back-up is voltooid, selecteert u OK om het dialoogvenster SQL Server Management Studio te sluiten.

Gebruik Transact-SQL

Maak een volledige databaseback-up door de BACKUP DATABASE instructie uit te voeren, waarbij u het volgende opgeeft:

• De naam van de database waarvan een back-up moet worden gemaakt.
• Het back-upapparaat waarin de volledige databaseback-up wordt geschreven.

De basissyntaxis Transact-SQL voor een volledige databaseback-up is:

BACKUP DATABASE <database>
TO <backup_device> [ , ...n ]
[ WITH <with_options> [ , ...o ] ];

Geef desgewenst een of meer WITH opties op. Hier worden enkele basisopties WITH beschreven. Zie WITH voor meer informatie over alle opties.

Opties voor basisback-upset WITH :

• { COMPRESSION | NO_COMPRESSION }. In SQL Server 2008 (10.0.x) Enterprise en hoger geeft u aan of back-upcompressie wordt uitgevoerd op de back-up, waarbij de standaardwaarde op serverniveau wordt overschreven.
• VERSLEUTELING (ALGORITME, SERVERCERTIFICAAT | ASYMMETRISCHE SLEUTEL). Alleen in SQL Server 2014 of hoger geeft u het versleutelingsalgoritmen op dat moet worden gebruikt en het certificaat of de asymmetrische sleutel die moet worden gebruikt om de versleuteling te beveiligen.
• DESCRIPTION = { 'text' | @text_variable }. Hiermee geeft u de vrije tekst die de back-upset beschrijft. De tekenreeks mag maximaal 255 tekens bevatten.
• NAME = { backup_set_name | @backup_set_name_var }. Hiermee geeft u de naam van de back-upset. Namen mogen maximaal 128 tekens bevatten. Als NAME dit niet is opgegeven, is deze leeg.

Standaard voegt BACKUP de back-up toe aan een bestaande mediaset, waarbij bestaande back-upsets behouden blijven. Als u deze configuratie expliciet wilt opgeven, gebruikt u de NOINIT optie. Zie Mediasets, mediafamilies en back-upsets (SQL Server) voor meer informatie over het toevoegen aan bestaande back-upsets.

Als u de back-upmedia wilt opmaken, gebruikt u de FORMAT volgende optie:

FORMAT [ , MEDIANAME = { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]

Gebruik de component wanneer u media voor de FORMAT eerste keer gebruikt of wanneer u alle bestaande gegevens wilt overschrijven. Wijs desgewenst de nieuwe media een medianaam en -beschrijving toe.

Voorbeelden

Maak voor de volgende voorbeelden een testdatabase met de volgende Transact-SQL code:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT GETDATE()
)
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

Een. Een back-up maken van een schijfapparaat

In het volgende voorbeeld wordt een back-up gemaakt van de volledige SQLTestDB database naar schijf. Er wordt gebruikgemaakt FORMAT van het maken van een nieuwe mediaset.

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

B. Een back-up maken van een tapeapparaat

In het volgende voorbeeld wordt een back-up gemaakt van de volledige SQLTestDB database naar tape. De back-up wordt toegevoegd aan de vorige back-ups.

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of SQLTestDB';
GO

C. Een back-up maken naar een logisch tapeapparaat

In het volgende voorbeeld wordt een logisch back-upapparaat voor een tapestation gemaakt. In het voorbeeld wordt vervolgens een back-up gemaakt van de volledige SQLTestDB database naar dat apparaat.

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0'; USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO SQLTestDB_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'SQLTestDB_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0',
      NAME = 'Full Backup of SQLTestDB';
GO

PowerShell gebruiken

Gebruik de Backup-SqlDatabase cmdlet. Als u expliciet een volledige databaseback-up wilt aangeven, geeft u de parameter op met de -BackupAction standaardwaarde. Database Deze parameter is optioneel voor volledige databaseback-ups.

Voorbeelden

Een. Volledige back-up (lokaal)

In het volgende voorbeeld wordt een volledige databaseback-up van de <myDatabase>-database gemaakt naar de standaardback-uplocatie van het serverexemplaar Computer\Instance. Optioneel geeft -BackupAction Databasedit voorbeeld aan.

Zie Backup-SqlDatabase voor volledige syntaxisvoorbeelden.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Volledige back-up naar Azure

In het volgende voorbeeld wordt een volledige back-up van de database <myDatabase> op het <myServer> exemplaar gemaakt naar Blob Storage. Er is een toegangsbeleid opgeslagen met lees-, schrijf- en lijstrechten. De SQL Server-referentie, is https://<myStorageAccount>.blob.core.windows.net/<myContainer>gemaakt met behulp van een Shared Access Signature die is gekoppeld aan het opgeslagen toegangsbeleid. De opdracht gebruikt de $backupFile parameter om de locatie (URL) en de naam van het back-upbestand op te geven.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

Gerelateerde taken

• Een differentiële databaseback-up maken (SQL Server)
• Een databaseback-up herstellen met behulp van SSMS
• Een databaseback-up herstellen onder het eenvoudige herstelmodel (Transact-SQL)
• Database herstellen naar storingspunt - volledig herstel
• Een database herstellen naar een nieuwe locatie (SQL Server)
• Gebruik de Wizard Onderhoudsplan

Verwante inhoud

• Problemen met back-up- en herstelbewerkingen van SQL Server oplossen
• Overzicht van back-ups (SQL Server)
• Back-ups van transactielogboeken (SQL Server)
• Mediasets, mediafamilies en back-upsets (SQL Server)
• sp_addumpdevice (Transact-SQL)
• BACKUP (Transact-SQL)
• back-up maken van database (algemene pagina)
• back-up maken van database (pagina Back-upopties)
• Differentiële back-ups (SQL Server)
• Volledige databaseback-ups (SQL Server)