Hoe meerdere databases te herstellen in SQL Server (3 methoden)

Kunt u meerdere SQL-databases tegelijkertijd herstellen?

Het is spijtig dat SSMS GUI slechts ondersteuning biedt voor het herstellen van één database per keer. Als u meerdere databases naar verschillende bak-bestanden heeft geback-upt, kunt u ze allemaal tegelijk herstellen?

Het antwoord is JA. Over het algemeen kunt u een geldig T-SQL-script gebruiken om meerdere databases in één keer te herstellen, maar alleen als de back-upbestanden exact zijn vernoemd naar de bijbehorende databases en geen extra informatie bevatten zoals datum/tijd.

Als u het flexibeler wilt doen, is er ook een eenvoudig alternatief met GUI. Kies gewoon de methode die u verkiest.

Vereisten voor het herstellen van meerdere databases in SQL Server

Het herstellen van meerdere databases in SQL Server kan een complex proces zijn. Om de downtime te minimaliseren, moeten er verschillende vereisten worden voldaan voordat u begint:

• U moet administratieve privileges hebben en toegang hebben tot de back-upbestanden.
• Er mag slechts één databaseback-up per back-upbestand zijn.
• Een back-upbestand voor een database mag slechts twee bestanden bevatten: een logbestand en een databestand.
• Aangezien we de fysieke bestandsnamen op de schijf baseren op de databasenaam, moeten de tekens die kunnen worden gebruikt voor de fysieke bestandsnamen in de nieuwe database die we herstellen toegestaan zijn.

Methode 1: Herstel meerdere databases vanuit bak-bestanden met behulp van xp_cmdshell

U kunt een script gebruiken om meerdere databases in SQL Server te herstellen vanuit een map. Voordat u dat doet, moet u echter verbinding maken met de instantie en xp_cmdshell inschakelen.

1. Klik op Nieuwe query en voer de volgende opdracht in:

-- Om geavanceerde opties te kunnen wijzigen.
EXEC sp_configure "show advanced options", 1;
GO
-- Om de momenteel geconfigureerde waarde voor geavanceerde opties bij te werken.
RECONFIGURE;
GO
-- Om de functie in te schakelen.
EXEC sp_configure "xp_cmdshell", 1;
GO
-- Om de momenteel geconfigureerde waarde voor deze functie bij te werken.
RECONFIGURE;
GO

Voer de query uit en de functie wordt ingeschakeld.

2. Vervolgens kunt u meerdere databases van SQL Server herstellen met behulp van het script (vervang alstublieft "D:\backup\" door uw eigen map die alle back-upbestanden bevat):

DECLARE @FilesCmdshell TABLE (
outputCmd NVARCHAR (255)
)
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)

INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell "dir /B D:\backup\*.bak"
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell

OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @cmd NVARCHAR(MAX) = "RESTORE DATABASE [" SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX(".", @FilesCmdshellOutputCmd)) "] FROM DISK = N"'D:\backup\" SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX(".", @FilesCmdshellOutputCmd)) ".bak"' WITH FILE = 1, NOUNLOAD, STATS = 10"
EXEC(@cmd)

FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END

✎Opmerking: Als u de foutmelding "De staart van het logboek voor de database is niet geback-upt. Gebruik BACKUP LOG MET NORECOVERY om het logboekback-up te maken als het werk bevat dat u niet wilt verliezen. Gebruik de clausule WITH REPLACE of WITH STOPAT van de RESTORE-instructie om alleen de inhoud van het logboek te overschrijven", ontvangt, is de oplossing precies zoals aangegeven.

U kunt bijvoorbeeld de herstelopdracht wijzigen in "...WITH FILE = 1, REPLACE, NOUNLOAD, STATS = 10"

Manier 2. Genereren van scripts om alle databases in SQL Server te herstellen

Als alternatief kunt u een script gebruiken om herstelopdrachten te genereren voor alle databases in SQL Server en deze combineren tot een nieuw script om uit te voeren.

1. Klik op Nieuwe Query en voer de volgende opdrachten in in het SQLQuery-venster:

DECLARE @folderpath VARCHAR (1000)
SELECT @folderpath = "D:\Backup\" -- Locatie van de back-up
SELECT "RESTORE DATABASE[" NAME "] FROM DISK = "'" @folderpath name ".bak"' WITH NORECOVERY,
REPLACE, STATS = 5"
FROM master.sys.databases
WHERE name NOT IN ("master","model","msdb","tempdb","distribution")

Dit zal een reeks opdrachten genereren om meerdere databases in SQL Server te herstellen vanuit bak-bestanden met dezelfde namen.

2. Klik met de rechtermuisknop op een willekeurige opdracht in Resultaten, kies Alles selecteren en vervolgens Kopiëren (of gebruik Ctrl A en Ctrl C).

3. Plak deze opdrachten in het SQLQuery-venster als een nieuw script om Uit te voeren. Dit zal alle databases van SQL Server herstellen vanuit de bak-bestanden met de bijbehorende bestandsnamen.

Manier 3. Back-up maken en meerdere SQL-databases herstellen met een GUI (eenvoudigste)

De bovenstaande scripts zijn alleen van toepassing wanneer uw bak-bestanden exact de namen hebben van de SQL-databases, wat in de praktijk nogal beperkend is. Daarom wil ik een handigere manier bieden om meerdere databases, of zelfs de volledige instantie, te back-uppen en te herstellen.

AOMEI Cyber Backup is een betrouwbare geïntegreerde beheeroplossing voor het maken van back-ups en het herstellen van SQL-databases op alle desktops, laptops en servers binnen het LAN.

Het werkt op Windows 11/10/ 8.1/8/7, Windows Server 2022/2019/2016/2012 (R2)/2008 (R2), en ondersteunt het maken van back-ups van SQL Server 2005 tot 2022. Naast SQL-databases, ondersteunt het ook het maken van back-ups van virtuele machines van Hyper-V en VMware.

• Voer het gedownloade .exe-bestand uit om AOMEI Cyber Backup te installeren en start de hoofdinterface. Klik op Bronapparaat -> Microsoft SQL -> Microsoft SQL toevoegen.

• Kies Proxyprogramma downloaden of Link kopiëren om het Agent-programma te downloaden en te installeren op het apparaat waarop SQL Server is geïnstalleerd. Schakel vervolgens over naar Al geïnstalleerde proxy om het apparaat te selecteren.
• Klik vervolgens op -> Verificatie om de database-instantie te valideren. U kunt kiezen voor Windows-verificatie of SQL-verificatie.

Dat is alles, u kunt nu een gegevensback-up plannen voor de toegevoegde SQL Server.

◉ Hoe meerdere databases in SQL Server te back-uppen:

1. Klik op Back-uptaak -> Nieuwe taak maken, en kies het back-uptype als Microsoft SQL-back-up.

2. Klik op Apparaatnaam om de SQL-instantie en databases voor back-up te specificeren. U kunt één of meerdere databases selecteren zoals u nodig heeft.

3. Selecteer een Doelopslag om uw SQL-back-ups op te slaan. U kunt een lokale pad of een netwerkpad specificeren.

4. Configureer het Schema om de SQL-databaseback-up dagelijks, wekelijks of maandelijks uit te voeren en kies de back-upmethode als Volledige, Incrementele of Differentiële back-up.

Klik op Start back-up om de taak te maken en uit te voeren. Zodra het klaar is, vindt u het terug in het tabblad Back-uptaak.

✍Meer handige functies:

• Back-up opruimen helpt u oudere back-upversies automatisch te verwijderen en bespaart daardoor ruimte.
• E-mailmelding stelt u in staat e-mailmeldingen te ontvangen wanneer de taak abnormaal is of succesvol is voltooid.
• Archiveren helpt u de back-upbestanden naar een AWS S3-opslag te archiveren.

◉ Hoe meerdere databases vanuit een back-up te herstellen:

1. Klik op Back-uptaak in het linkermenu, zoek de taak die u wilt herstellen en klik op -> Herstellen.

2. Selecteer een back-upversie waarvan u SQL-databases wilt herstellen.

3. Selecteer vervolgens de doellocatie waar u naar wilt herstellen. Er zijn 2 opties:

• Herstellen naar oorspronkelijke locatie (standaard): herstellen op de huidige locatie en SQL-databases terugzetten naar een oudere versie.
• Herstellen naar nieuwe locatie: selecteer een andere doellocatie voor het uitvoeren van een herstel bij tekort aan ruimte. U kunt de naam van de nieuwe database specificeren en de opslaglocatie aanpassen.

Overschrijf de database met dezelfde naam: Als u deze optie aanvinkt, worden de back-updatabases overschreven door de doeldatabases met dezelfde namen. Als u het niet aanvinkt, worden de doeldatabases met dezelfde namen tijdens het herstellen overgeslagen.

4. Klik na alle instellingen op Start Herstel om het herstelproces te starten en wacht geduldig tot het voltooid is. U kunt op de knop klikken om de hersteldetails te bekijken of het herstel te annuleren.

✍ Opmerkingen:

• "Herstellen naar oorspronkelijke locatie" moet bevestigen dat de oorspronkelijke locatie bestaat. Anders kunt u alleen "Herstellen naar nieuwe locatie" selecteren.
• "Herstellen naar oorspronkelijke locatie" zal de originele databasegegevens overschrijven of verwijderen, als de oorspronkelijke database belangrijke gegevens bevat, wordt aanbevolen om "Herstellen naar nieuwe locatie" te kiezen.

Conclusie

De SSMS herstel-GUI maakt het mogelijk om slechts één database tegelijk te herstellen. Als u meerdere databases in SQL Server tegelijk wilt herstellen, is de meest gebruikelijke aanpak het gebruik van een T-SQL-script. Het heeft echter strikte beperkingen op de bestandsnamen van back-upbestanden en maakt het moeilijk om bewerkingen zoals het herstellen van databases naar een andere instantie uit te voeren. Daarom kunt u ook de SQL-back-upsoftware - AOMEI Cyber Backup proberen.

Het maakt de bewerking veel gemakkelijker. Bijvoorbeeld, u kunt met enkele simpele klikken SQL-databases automatisch back-uppen en met even eenvoudige handelingen meerdere databases tegelijk herstellen.