Back-ups van SQL Server Express-databases plannen en automatiseren

Oorspronkelijke productversie: SQL Server
Oorspronkelijk KB-nummer: 2019698

Samenvatting

SQL Server Express bevat geen SQL Server Agent, zodat u geen back-uptaken of onderhoudsplannen kunt plannen zoals in andere SQL Server edities. Als u back-ups op SQL Server Express wilt automatiseren, combineert u drie onderdelen: de sp_BackupDatabases opgeslagen procedure, het sqlcmd opdrachtregelprogramma en Windows Task Scheduler. In dit artikel worden de stappen beschreven voor het instellen van geplande back-ups van volledige, differentiële en transactielogboekback-ups van SQL Server Express-databases. Ook wordt uitgelegd hoe u kunt bevestigen dat een geplande back-up is uitgevoerd.

Dit artikel is alleen van toepassing op SQL Server Express-edities. Dit geldt niet voor SQL Server Express LocalDB.

Waarom SQL Server Express geen back-ups kan plannen met SQL Server Agent

SQL Server Agent is het onderdeel waarmee geplande taken en onderhoudsplannen worden uitgevoerd en deze niet is opgenomen in SQL Server Express. Zie Edities en ondersteunde functies van SQL Server voor een volledige vergelijking van wat elke editie bevat.

Zonder SQL Server Agent kunt u nog steeds een back-up maken van een SQL Server Express-database op aanvraag met behulp van een van de volgende hulpprogramma's:

sqlcmd-hulpprogramma
SQL Server Management Studio (SSMS)
MSSQL-extensie voor Visual Studio Code
Een Transact-SQL-script dat gebruikmaakt van de opdrachtfamilie BACKUP (Transact-SQL)

Als u deze back-ups volgens een planning wilt uitvoeren in plaats van op aanvraag, gebruikt u Windows Task Scheduler als de planningsengine, zoals beschreven in de volgende stappen. Zie Back-up maken en herstellen van SQL Server databases voor achtergrondinformatie over back-uptypen en strategie.

Prerequisites

Voordat u begint, moet u ervoor zorgen dat u het volgende hebt:

Een actief exemplaar van SQL Server Express. In de voorbeelden in dit artikel wordt het standaard benoemde exemplaar gebruikt. .\SQLEXPRESS
Lidmaatschap van de vaste serverrol sysadmin op dat exemplaar. Dit is vereist voor het maken van een opgeslagen procedure in de master database.
Een lokale map of schijf met voldoende vrije ruimte voor het opslaan van de back-upbestanden. In de voorbeelden wordt D:\SQLBackups gebruikt.
Machtiging voor het maken van geplande taken op de computer waarop SQL Server Express wordt uitgevoerd.

Stap 1: Maak de stored procedure sp_BackupDatabases aan

sp_BackupDatabases is een door Microsoft geleverde opgeslagen procedure die het juiste BACKUP-commando uitvoert op één database of op elke online database op het serverexemplaar. U maakt deze eenmalig in de master database.

Download het script uit SQL_Express_Backups.sql en sla het lokaal op, bijvoorbeeld als C:\Temp\SQL_Express_Backups.sql. Maak vervolgens de opgeslagen procedure met behulp van SSMS of sqlcmd.

De opgeslagen procedure maken met behulp van SSMS

Open SSMS en maak verbinding met uw SQL Server Express-exemplaar. Voer bij Servernaam.\SQLEXPRESS in en selecteer vervolgens Verbinden.
Selecteer Bestand>openen>, selecteer het SQL_Express_Backups.sql bestand dat u hebt opgeslagen en selecteer vervolgens Openen.
Controleer of de databaselijst op de werkbalk wordt weergegeven master. Het script begint met USE [master], zodat het automatisch op de juiste database is gericht.
Selecteer Uitvoeren (of druk op F5). Het deelvenster Berichten geeft Commands completed successfully weer.

De opgeslagen procedure maken met behulp van sqlcmd

Voer de volgende opdracht uit vanaf een opdrachtprompt:

sqlcmd -S .\SQLEXPRESS -E -i "C:\Temp\SQL_Express_Backups.sql"

Controleer of de opgeslagen procedure is aangemaakt

Voer de volgende query uit tegen de instantie. Er wordt één rij geretourneerd als de opgeslagen procedure bestaat:

SELECT name, create_date
FROM master.sys.procedures
WHERE name = 'sp_BackupDatabases';

sp_BackupDatabases-parameters

De opgeslagen procedure accepteert de volgende parameters:

Parameter	Required	Description
`@backupLocation`	Yes	De map die de back-upbestanden ontvangt, bijvoorbeeld `D:\SQLBackups\`. De map moet al bestaan.
`@backupType`	Yes	Het back-uptype: `F` voor volledig, `D` voor differentieel of `L` voor transactielogboek.
`@databaseName`	No	De database waarvan een back-up moet worden gemaakt. Als u deze parameter weglaat, maakt de opgeslagen procedure een back-up van elke onlinedatabase op het exemplaar, behalve de databases die het script uitsluit.

De opgeslagen procedure maakt nooit een back-up tempdb. Het slaat ook de voorbeelddatabases Northwindover, pubsen AdventureWorks voor elk back-uptype, en slaat het over master voor differentiële en logboekback-ups omdat deze back-uptypen niet worden ondersteund voor de master database.

Important

De uitgesloten databasenamen zijn vastgelegd in het script. Als u een database hebt die een van deze namen gebruikt, slaat de opgeslagen procedure deze over zonder een fout te melden. Als u een back-up van een dergelijke database wilt maken, bewerkt u de DELETE @DBs WHERE DBNAME IN (...)-lijsten in het script voordat u de stored procedure maakt.

Stap 2: het sqlcmd-hulpprogramma installeren

Met sqlcmd het hulpprogramma kunt u Transact-SQL instructies, door het systeem opgeslagen procedures en scriptbestanden vanaf de opdrachtregel uitvoeren. Het is geïnstalleerd met SSMS en is ook beschikbaar als zelfstandige download voor computers die geen SSMS hebben. Zie Het hulpprogramma sqlcmd downloaden en installeren om deze te installeren.

Om te bevestigen dat sqlcmd is geïnstalleerd en beschikbaar is, voert u sqlcmd -? uit in een opdrachtprompt.

De map met het sqlcmd uitvoerbare bestand wordt meestal toegevoegd aan de Path omgevingsvariabele wanneer u SQL Server of de zelfstandige hulpprogramma's installeert. Als sqlcmd -? u rapporteert dat de opdracht niet wordt herkend, voegt u de map toe aan de Path variabele of geeft u het volledige pad op naar het hulpprogramma in uw batchbestand.

Stap 3: het Sqlbackup.bat batchbestand maken

Maak in een teksteditor een batchbestand met de naam Sqlbackup.bat. Kopieer de tekst uit een van de volgende voorbeelden naar dat bestand, afhankelijk van uw scenario.

Houd rekening met de volgende punten voordat u een voorbeeld kiest:

Elk voorbeeld gebruikt D:\SQLBackups als plaatshouder. Wijzig het pad naar het schijfstation en naar de back-upmap die u in uw omgeving wilt gebruiken, en zorg ervoor dat de map bestaat.
Als u SQL Server-verificatie gebruikt, wordt het wachtwoord opgeslagen in duidelijke tekst in het batchbestand. Beperk de toegang tot de map met het batchbestand alleen voor geautoriseerde gebruikers.

Voorbeeld 1: Volledige back-ups van alle databases met behulp van Windows-verificatie

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"

Voorbeeld 2: Differentiële back-ups van alle databases met behulp van SQL Server-verificatie

REM Sqlbackup.bat
sqlcmd -U <YourSQLLogin> -P <StrongPassword> -S .\SQLEXPRESS -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='D'"

Notitie

Als u back-ups wilt uitvoeren, moet de aanmelding lid zijn van de db_backupoperator of db_owner vaste databaserol in elke database waarvan u een back-up maakt, of lid zijn van de vaste serverrol sysadmin . Zie Rollen op databaseniveau voor meer informatie.

Voorbeeld 3: Back-ups van transactielogboeken van alle databases met behulp van Windows-verificatie

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='L'"

Back-ups van transactielogboeken vereisen het volledige of bulksgewijs vastgelegde herstelmodel en ze vereisen ten minste één vorige volledige back-up. Zie Recovery-modellen (SQL Server)voor meer informatie.

Voorbeeld 4: Volledige back-up van één database met behulp van Windows-verificatie

REM Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -b -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName='USERDB', @backupType='F'"

Als u een differentiële back-up van USERDBwilt maken, wijzigt u de @backupType parameter in D. Als u een back-up van een transactielogboek wilt maken, wijzigt u het Lin .

Met de optie -b wordt sqlcmd afgesloten en wordt een DOS ERRORLEVEL-waarde van 1 geretourneerd wanneer een SQL Server-fout een ernstniveau van meer dan 10 heeft. Zonder -b, sqlcmd retourneert 0 zelfs wanneer de back-up mislukt en Task Scheduler rapporteert de taak als geslaagd.

Voordat u doorgaat, voert u Sqlbackup.bat handmatig uit vanaf een opdrachtprompt en bevestigt u dat back-upbestanden worden weergegeven in de back-upmap. Het is nu eenvoudiger om fouten op te lossen dan ze later via Task Scheduler te diagnosticeren.

Stap 4: Het batchbestand plannen in Windows Task Scheduler

Volg deze stappen om Sqlbackup.bat uit te voeren volgens een schema:

Op de computer waarop SQL Server Express wordt uitgevoerd, selecteert u Start en typt u Task Scheduler in het tekstvak.
Selecteer Beste overeenkomst en klik op Taakplanner om deze te starten.
Klik in Task Scheduler met de rechtermuisknop op Task Scheduler (Lokaal) en selecteer Basistaak maken.
Voer een naam in voor de nieuwe taak (bijvoorbeeld SQLBackup) en selecteer vervolgens Volgende.
Selecteer Dagelijks als taaktrigger en selecteer vervolgens Volgende.
Stel de herhaling in op 1 dag en selecteer vervolgens Volgende.
Selecteer Een programma starten als actie en selecteer vervolgens Volgende.
Selecteer Bladeren, selecteer het Sqlbackup.bat bestand dat u in stap 3 hebt gemaakt : maak het Sqlbackup.bat batchbestand en selecteer vervolgens Openen.
Schakel het selectievakje Het dialoogvenster Eigenschappen voor deze taak openen wanneer ik op Voltooien klik in en selecteer vervolgens Voltooien.
Controleer op het tabblad Algemeen de beveiligingsopties en bevestig het volgende voor het account dat wordt vermeld bij het uitvoeren van de taak, gebruik het volgende gebruikersaccount:
- Het account heeft ten minste lees- en uitvoeringsmachtigingen om het sqlcmd hulpprogramma uit te voeren.
- Als het batchbestand gebruikmaakt van Windows-verificatie, is het account gemachtigd om een back-up te maken van de SQL Server-databases.
- Als het batchbestand gebruikmaakt van SQL Server-verificatie, heeft het SQL Server-aanmeldingsaccount in het batchbestand toestemming om een back-up van de databases te maken.
Pas de overige instellingen aan zodat deze aan uw vereisten voldoen en selecteer VERVOLGENS OK.

Tip

Voer bij wijze van test Sqlbackup.bat uit in een opdrachtprompt die u hebt gestart met hetzelfde gebruikersaccount dat de eigenaar van de taak is. Met deze stap wordt bevestigd dat het account over de benodigde machtigingen beschikt voordat het schema wordt uitgevoerd.

Zie Task Scheduler voor meer informatie over de planningsopties.

Controleer of de geplande back-up is uitgevoerd

Controleer na de eerste geplande uitvoering of de back-up is geslaagd:

Controleer de back-upmap op nieuwe .bak bestanden (volledige en differentiële back-ups) of TRN-bestanden (back-ups van transactielogboeken). Met de opgeslagen procedure wordt een datum- en tijdstempel toegevoegd aan elke bestandsnaam.
Selecteer in Taakplanner Taakplannerbibliotheek, selecteer de taak en controleer de kolom Resultaat van de laatste uitvoering. In deze kolom ziet u de afsluitcode die het batchbestand heeft geretourneerd. Omdat in de voorbeelden de -b-optie wordt gebruikt, betekent een waarde van 0x1 dat de back-upopdracht is mislukt. Zie Taakplanner-fout- en succesconstanten voor andere waarden.

Vraag de back-upgeschiedenis op van de instantie:

SELECT database_name, type, backup_start_date, backup_finish_date, physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
    ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_start_date DESC;

Deze tabellen maken deel uit van de back-upgeschiedenis die SQL Server in de msdb database onderhoudt. Zie back-upgeschiedenis en headerinformatie (SQL Server) voor meer informatie.