Schemalägga och automatisera säkerhetskopieringar av SQL Server Express-databaser

Ursprunglig produktversion: SQL Server
Ursprungligt KB-nummer: 2019698

Sammanfattning

SQL Server Express inkluderar inte SQL Server Agent, så du kan inte schemalägga säkerhetskopieringsjobb eller underhållsplaner på det sätt som du kan i andra SQL Server utgåvor. Om du vill automatisera säkerhetskopieringar på SQL Server Express kombinerar du tre komponenter: den sp_BackupDatabases lagrade proceduren, sqlcmd kommandoradsverktyget och Windows Schemaläggaren. Den här artikeln går igenom de steg som konfigurerar schemalagda fullständiga säkerhetskopieringar, differentiella säkerhetskopieringar och transaktionsloggar för SQL Server Express-databaser. Den förklarar också hur du bekräftar att en schemalagd säkerhetskopiering kördes.

Den här artikeln gäller endast för SQL Server Express-utgåvor. Det gäller inte för SQL Server Express LocalDB.

Varför SQL Server Express inte kan schemalägga säkerhetskopieringar med SQL Server Agent

SQL Server Agent är komponenten som kör schemalagda jobb och underhållsplaner, och den ingår inte i SQL Server Express. En fullständig jämförelse av vad varje utgåva innehåller finns i Utgåvor och funktioner som stöds i SQL Server.

Utan SQL Server Agent kan du fortfarande säkerhetskopiera en SQL Server Express-databas på begäran med något av följande verktyg:

sqlcmd-verktyg
SQL Server Management Studio (SSMS)
MSSQL-tillägg för Visual Studio Code
Ett Transact-SQL skript som använder kommandofamiljen BACKUP (Transact-SQL)

Om du vill köra dessa säkerhetskopior enligt ett schema i stället för på begäran använder du Windows Schemaläggaren som schemaläggningsmotor enligt beskrivningen i följande steg. Bakgrund om säkerhetskopieringstyper och -strategi finns i Säkerhetskopiera och återställa SQL Server databaser.

Förutsättningar

Innan du börjar kontrollerar du att du har:

En instans av SQL Server Express som körs. Exemplen i den här artikeln använder den namngivna standardinstansen , .\SQLEXPRESS.
Medlemskap i den fasta serverrollen sysadmin på den instansen, vilket krävs för att skapa en lagrad procedur i master databasen.
En lokal mapp eller enhet med tillräckligt med ledigt utrymme för att lagra säkerhetskopiorna. Exemplen använder D:\SQLBackups.
Behörighet att skapa schemalagda aktiviteter på datorn som kör SQL Server Express.

Steg 1: Skapa den sp_BackupDatabases lagrade proceduren

sp_BackupDatabasesär en Microsoft lagrad procedur som kör rätt BACKUP kommando mot en databas eller varje onlinedatabas på instansen. Du skapar den en gång i master databasen.

Ladda ned skriptet från SQL_Express_Backups.sql och spara det lokalt, till exempel som C:\Temp\SQL_Express_Backups.sql. Skapa sedan den lagrade proceduren med hjälp av antingen SSMS eller sqlcmd.

Skapa den lagrade proceduren med hjälp av SSMS

Öppna SSMS och anslut till din SQL Server Express-instans. I Servernamn anger du .\SQLEXPRESSoch väljer sedan Anslut.
Välj Arkiv>Öppna>fil, välj den SQL_Express_Backups.sql fil som du sparade och välj sedan Öppna.
Bekräfta att databaslistan i verktygsfältet visar master. Skriptet börjar med USE [master], så det riktar sig automatiskt mot rätt databas.
Välj Kör (eller tryck på F5). Panelen Meddelanden rapporterar Commands completed successfully.

Skapa den lagrade proceduren med hjälp av sqlcmd

Kör följande kommando från en kommandotolk:

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

Bekräfta att den lagrade proceduren har skapats

Kör följande fråga mot instansen. Den returnerar en rad om den lagrade proceduren finns:

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

sp_BackupDatabases parametrar

Den lagrade proceduren accepterar följande parametrar:

Parameter	Required	Description
`@backupLocation`	Ja	Mappen som tar emot säkerhetskopieringsfilerna, till exempel `D:\SQLBackups\`. Mappen måste redan finnas.
`@backupType`	Ja	Typ av säkerhetskopiering: `F` för fullständig, `D` differentiell eller `L` för transaktionslogg.
`@databaseName`	No	Databasen som ska säkerhetskopieras. Om du utelämnar den här parametern säkerhetskopierar den lagrade proceduren alla onlinedatabaser på instansen förutom de databaser som skriptet exkluderar.

Den lagrade proceduren säkerhetskopierar aldrig tempdb. Den hoppar också över exempeldatabaserna Northwind, pubsoch AdventureWorks för varje typ av säkerhetskopiering och hoppar över master differentiella säkerhetskopieringar och loggsäkerhetskopior eftersom dessa säkerhetskopieringstyper inte stöds för master databasen.

Important

De exkluderade databasnamnen hårdkodas i skriptet. Om du har en databas som använder ett av dessa namn hoppar den lagrade proceduren över den utan att rapportera ett fel. För att säkerhetskopiera en sådan databas redigerar du DELETE @DBs WHERE DBNAME IN (...) listorna i skriptet innan du skapar den lagrade proceduren.

Steg 2: Installera sqlcmd-verktyget

Med sqlcmd verktyget kan du köra Transact-SQL-instruktioner, systemlagringsprocedurer och skriptfiler från kommandoraden. Den installeras med SSMS och är även tillgänglig som en fristående nedladdning för datorer som inte har SSMS. Information om hur du installerar det finns i Ladda ned och installera sqlcmd-verktyget.

Bekräfta att sqlcmd är installerat och tillgängligt genom att köra sqlcmd -? i en kommandotolk.

Mappen som innehåller den sqlcmd körbara filen läggs vanligtvis till i Path miljövariabeln när du installerar SQL Server eller de fristående verktygen. Om sqlcmd -? rapporterar att kommandot inte känns igen lägger du antingen till mappen i variabeln Path eller anger den fullständiga sökvägen till verktyget i batchfilen.

Steg 3: Skapa Sqlbackup.bat batchfil

I en textredigerare skapar du en batchfil med namnet Sqlbackup.bat. Kopiera texten från något av följande exempel till filen, beroende på ditt scenario.

Tänk på följande innan du väljer ett exempel:

Varje exempel använder D:\SQLBackups som platshållare. Ändra sökvägen till den enhets- och säkerhetskopieringsmapp som du vill använda i din miljö och kontrollera att mappen finns.
Om du använder SQL Server autentisering lagras lösenordet i klartext i batchfilen. Begränsa åtkomsten till den mapp som endast innehåller batchfilen till behöriga användare.

Exempel 1: Fullständiga säkerhetskopior av alla databaser med hjälp av Windows-autentisering

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

Exempel 2: Differentiella säkerhetskopior av alla databaser med hjälp av SQL Server-autentisering

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

Anmärkning

För att köra säkerhetskopieringar måste inloggningskontot vara medlem i den fasta databasrollen db_backupoperator eller db_owner i varje databas som du säkerhetskopierar, eller medlem i den fasta serverrollen sysadmin. Mer information finns i Roller på databasnivå.

Exempel 3: Säkerhetskopiering av transaktionsloggar för alla databaser med hjälp av Windows-autentisering

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

Säkerhetskopieringar av transaktionsloggar kräver den fullständiga eller massloggade återställningsmodellen, och de kräver minst en tidigare fullständig säkerhetskopiering. Mer information finns i Recovery-modeller (SQL Server).

Exempel 4: Fullständig säkerhetskopiering av en enskild databas med hjälp av Windows-autentisering

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

Om du vill göra en differentiell säkerhetskopia av USERDBändrar du parametern @backupType till D. Om du vill göra en säkerhetskopia av transaktionsloggen ändrar du den till L.

Alternativet -b avslutar sqlcmd och returnerar ett DOS ERRORLEVEL värde för 1 när ett SQL Server fel har en allvarlighetsgrad som är större än 10. Utan -b returnerar sqlcmd0 även när säkerhetskopieringen misslyckas, och Aktivitetsschemaläggaren rapporterar aktiviteten som lyckad.

Innan du fortsätter, kör Sqlbackup.bat manuellt från kommandotolken och bekräfta att säkerhetskopieringsfiler visas i säkerhetskopieringsmappen. Det är enklare att åtgärda fel nu än att diagnostisera dem senare via Schemaläggaren.

Steg 4: Schemalägg batchfilen i Windows Schemaläggaren

Följ dessa steg för att köra Sqlbackup.bat enligt ett schema:

På datorn som kör SQL Server Express väljer du Start och skriver Schemaläggaren i textrutan.
Under Bästa matchning väljer du Schemaläggaren för att starta den.
I Schemaläggaren högerklickar du på Schemaläggaren (lokal) och väljer Skapa grundläggande aktivitet.
Ange ett namn för den nya aktiviteten (till exempel SQLBackup) och välj sedan Nästa.
Välj Dagligen för aktivitetsutlösaren och välj sedan Nästa.
Ange upprepningen till en dag och välj sedan Nästa.
Välj Starta ett program som åtgärd och välj sedan Nästa.
Välj Bläddra, välj den Sqlbackup.bat fil som du skapade i steg 3: Skapa Sqlbackup.bat batchfil och välj sedan Öppna.
Markera kryssrutan Öppna dialogrutan Egenskaper för den här aktiviteten när jag klickar på Slutför och välj sedan Slutför.
På fliken Allmänt granskar du säkerhetsalternativen och bekräftar följande för kontot som anges under När du kör uppgiften använder du följande användarkonto:
- Kontot har minst behörigheten Läs och kör för att köra sqlcmd-verktyget.
- Om batchfilen använder Windows-autentisering har kontot behörighet att säkerhetskopiera SQL Server databaser.
- Om batchfilen använder SQL Server-autentisering, har SQL Server-inloggningen som används i batchfilen behörighet att säkerhetskopiera databaserna.
Justera de återstående inställningarna så att de matchar dina krav och välj sedan OK.

Tips

Som ett test kan du köra Sqlbackup.bat från en kommandoprompt som du startade med samma användarkonto som äger uppgiften. Det här steget bekräftar att kontot har de behörigheter som krävs innan schemat körs.

Mer information om schemaläggningsalternativen finns i Schemaläggaren.

Kontrollera att den schemalagda säkerhetskopieringen kördes

Efter den första schemalagda körningen bekräftar du att säkerhetskopieringen lyckades:

Kontrollera säkerhetskopieringsmappen för nya .bak filer (fullständiga och differentiella säkerhetskopior) eller .trn-filer (säkerhetskopiering av transaktionsloggar). Den lagrade proceduren lägger till en datum- och tidsstämpel för varje filnamn.
I Aktivitetsschemaläggaren väljer du Aktivitetsschemaläggarbiblioteket, markerar den aktivitet och granskar kolumnen Resultat för senaste körningen. Den här kolumnen visar slutkoden som batchfilen returnerade. Eftersom exemplen -b använder alternativet innebär värdet 0x1 att säkerhetskopieringskommandot misslyckades. För andra värden, se fel- och lyckandekonstanter för Aktivitetsschemaläggaren.
Fråga säkerhetskopieringshistoriken på instansen:
```
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;
```
Dessa tabeller är en del av den säkerhetskopieringshistorik som SQL Server underhåller i msdb databasen. Mer information finns i Säkerhetskopieringshistorik och rubrikinformation (SQL Server).