Het plannen en het automatiseren van back-ups van SQL Server-databases in SQL Server Express

Van toepassing: Microsoft SQL Server

Samenvatting


SQL Server Express-edities bieden een manier om het plannen van taken of onderhoudsplannen omdat het onderdeel SQL Server Agent niet is opgenomen in deze edities niet. U moet daarom een verschillende aanpak voor back-up van uw database wanneer u deze versies gebruikt.   

Op dit moment kunnen SQL Server Express-gebruikers back-up van hun databases met behulp van een van de volgende methoden:

  • Gebruik SQL Server Management Studio Express. Dit is samen met de Service SQL Server Express geavanceerde of SQL Server Express Toolkit geïnstalleerd. Ga naar de volgende website van Microsoft Developer Network (MSDN) voor meer informatie: Maken van een volledige databaseback-up (SQL Server)
  • Gebruik een Transact-SQL-script dat van de familie BACKUP DATABASE opdrachten gebruikmaakt. Ga naar de volgende MSDN-website voor meer informatie: Back-up (Transact-SQL)

In dit artikel wordt beschreven hoe een Transact-SQL-script met de Taakplanner van Windows gebruiken voor het automatiseren van back-ups van SQL Server Express-databases op gezette tijden.

Meer informatie


U hebt deze drie stappen om een back-up van SQL Server-databases met behulp van Taakplanner van Windows:

Stap voor stap een: Gebruik SQL Server Management Studio Express of Sqlcmd maken de volgende opgeslagen procedure in de database master:

Copyright © Microsoft Corporation.  Alle rechten voorbehouden.

Deze code is vrijgegeven onder de voorwaarden van de

/ / Microsoft Public License (MS-PL http://opensource.org/licenses/ms-pl.html.)

Gebruik [model] 

GO 

/ *** Object:  opgeslagen procedure [dbo]. [sp_BackupDatabases] *** / 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

 

-- ============================================= 

--Auteur: Microsoft 

--Maak datum: 2010-02-06

--Beschrijving: back-up van Databases voor SQLExpress

--Parameter1: databaseName 

--Parameter2: TypeBack-up F = volledig, D = verschil, L = log

--Parameter3: locatie een back-up

-- =============================================

 

CREATE PROCEDURE [dbo]. [sp_BackupDatabases]  

            sysname @databaseName = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

 

       SET NOCOUNT INSCHAKELEN; 

           

            @DBs tabel DECLAREREN

            (

                  Int IDENTITY primaire sleutel-ID

                  DBNAME nvarchar(500)

            )

           

             --Kies uit alleen databases die on line zijn in het geval alle databases worden back-up moet worden gekozen

             Als u deze specifieke database wilt back-up worden gemaakt alleen eruit die uit @DBs

            PLAATS in @DBs (DBNAME)

            Selecteer de naam van master.sys.databases

            waar staat = 0

            EN name=@DatabaseName

            OF @DatabaseName IS NULL

            SORTEREN op naam

           

            --Uitfilteren geen back-ups hoeft van databases

            IF @backupType = 'F'

                  BEGINNEN

                  VERWIJDEREN @DBs waar DBNAME IN ('tempdb', 'Noordenwind', 'pubs', 'AdventureWorks')

                  EINDE

            ELSE IF @backupType = had'

                  BEGINNEN

                  VERWIJDEREN @DBs waar DBNAME IN ('tempdb', 'Noordenwind', 'pubs', 'master', 'AdventureWorks')

                  EINDE

            ELSE IF @backupType = "L"

                  BEGINNEN

                  VERWIJDEREN @DBs waar DBNAME IN ('tempdb', 'Noordenwind', 'pubs', 'master', 'AdventureWorks')

                  EINDE

            ANDERS

                  BEGINNEN

                  RETURN

                  EINDE

           

            --Declareren van variabelen

            @BackupName varchar(100) DECLAREREN

            @BackupFile varchar(100) DECLAREREN

            @DBNAME varchar(300) DECLAREREN

            @SqlCommand NVARCHAR(1000) DECLAREREN 

        @DateTime NVARCHAR(20) DECLAREREN

            VERKLAREN @Loop int                  

                       

            --De databases één voor één doorlopen

            SELECT @Loop = min(ID) van @DBs

 

      TIJDENS het @Loop IS NOT NULL

      BEGINNEN

 

--Databasenamen hebben in [dbname] indeling omdat sommige hebben- of _ in hun naam

      SET @DBNAME = "[' +(SELECT DBNAME FROM @DBs WHERE ID = @Loop) +']"

 

--De huidige datum en tijd yyyyhhmmss n opmaak instellen

      SET @dateTime = vervangen (converteren (VARCHAR, GETDATE(),101),'/','') + '_' +  (omzetten (VARCHAR, GETDATE(),108),':','') vervangen  

 

--Back-bestandsnaam in path\filename.extension-indeling voor volledige, diff en log back-ups maken

      IF @backupType = 'F'

            SET @BackupFile = @backupLocation + vervangen (vervangen (@DBNAME, ' [', ''),'] ','') + '_FULL_' @dateTime+ '. BAK'

      ELSE IF @backupType = had'

            SET @BackupFile = @backupLocation + vervangen (vervangen (@DBNAME, ' [', ''),'] ','') + '_DIFF_' @dateTime+ '. BAK'

      ELSE IF @backupType = "L"

            SET @BackupFile = @backupLocation + vervangen (vervangen (@DBNAME, ' [', ''),'] ','') + '_LOG_' @dateTime+ '. TRN'

 

--De back-up een naam opgeven voor het opslaan in de media

      IF @backupType = 'F'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') + "volledige back-up voor ' + @dateTime

      IF @backupType = had'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differentiële back-up voor ' + @dateTime

      IF @backupType = "L"

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') + "back-up voor ' + @dateTime

 

--Genereren van de dynamische SQL-opdracht wordt uitgevoerd

 

       IF @backupType = 'F' 

                  BEGINNEN

               SET @sqlCommand = +@DBNAME+ 'Back-up DATABASE'  ' op schijf = '''+@BackupFile+ ''' met INIT, naam = ''' +@BackupName+'' ', NOSKIP, NOFORMAT'

                  EINDE

       IF @backupType = had'

                  BEGINNEN

               SET @sqlCommand = +@DBNAME+ 'Back-up DATABASE'  ' op schijf = '''+@BackupFile+ ''' met DIFFERENTIEEL, INIT, naam = ''' +@BackupName+'' ', NOSKIP, NOFORMAT'        

                  EINDE

       IF @backupType = "L" 

                  BEGINNEN

               SET @sqlCommand = +@DBNAME+ 'BACKUP LOG'  ' op schijf = '''+@BackupFile+ ''' met INIT, naam = ''' +@BackupName+'' ', NOSKIP, NOFORMAT "        

                  EINDE

 

--De gegenereerde SQL-opdracht uitvoeren

       EXEC(@sqlCommand)

 

--Ga naar de volgende database

SELECT @Loop = min(ID) FROM @DBs waarbij ID > @Loop

 

EINDE

Stap B: Maak een batchbestand met de naam Sqlbackup.bat In een teksteditor en kopieer de tekst in dat bestand, afhankelijk van het scenario van een van de volgende voorbeelden:

Example1:Volledige back-ups van alle databases in de lokale benoemde SQLEXPRESS met behulp van Windows-verificatie

// Sqlbackup.bat

sqlcmd -S. \EXPRESS – E -Q "EXEC sp_BackupDatabases @backupLocation = had: \SQLBackups\', @backupType 'F' =" 

Voorbeeld2:Differentiële back-up van alle databases in de lokale benoemd exemplaar SQLEXPRESS met behulp van een SQLLogin en het wachtwoord van de

// Sqlbackup.bat

Sqlcmd - U SQLLogin -P wachtwoord -S. \SQLEXPRESS -Q "EXEC sp_BackupDatabases  @backupLocation = D:\SQLBackups, @BackupType = had'"

Opmerking: de SQLLogin shouldhave op ten minste de back-upoperator rol in SQL Server.

Voorbeeld 3:Logboek voor back-ups van alle databases in lokale benoemde SQLEXPRESS met behulp van Windows-verificatie

// Sqlbackup.bat

Sqlcmd -S. \SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation = had: \SQLBackups\', @backupType 'L' = '

Voorbeeld 4: Volledige back-ups van de database USERDB in de lokale benoemde SQLEXPRESS met behulp van Windows-verificatie

// Sqlbackup.bat

Sqlcmd -S. \SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation = had: \SQLBackups\', @databaseName USERDB, @backupType = 'F' = '

Ook kunt u een differentiële back-up van USERDB door te plakken in zou ' voor de parameter @backupType en een back-up van USERDB door "L" voor de parameter @backupType .

Stap C:Planning een taak met behulp van Windows Taakplanner voor het uitvoeren van het batchbestand dat u hebt gemaakt in stap B. Ga hiervoor als volgt te werk:

  1. Op de computer waarop SQL Server Express wordt uitgevoerd, klikt u op Start, wijs Alle programma'sBureau-accessoires, Systeemwerkseten klik vervolgens op geplande taken. 
  2. Dubbelklik op een geplande taak toevoegen. 
  3. Klik op volgendein de Wizard taak plannen. 
  4. Klik op Bladeren, klik op het batchbestand dat u in stap B hebt gemaakt en klik op openen. 
  5. SQLBACKUP typt u de naam van de taak, klikt u op dagelijksen klik vervolgens op volgende. 
  6. Informatie voor een schema voor de taak opgeven. (Het is raadzaam dat u deze taak ten minste één keer elke dag uitvoeren.) Klik vervolgens op volgende.
  7. Typ een gebruikersnaam en typ een wachtwoord in het veld invoeren het wachtwoord in het veld invoeren als u de gebruikersnaam van de . Opmerking Deze gebruiker moet ten minste beschikken over de rol van de BackupOperator op het niveau van SQL Server als u een van de batchbestanden in voorbeeld 1, 3 of 4.
  8. Klik op volgendeen klik vervolgens op Voltooien. 
  9. Uitvoeren van de geplande taak ten minste één keer om ervoor te zorgen dat de back-up is gemaakt.

Opmerking Het uitvoerbare bestand van de map voor de SQLCMD is in het algemeen in de Path-variabelen voor de server nadat SQL Server is geïnstalleerd, maar als deze map niet wordt vermeld in de variabele Path, u deze onder < locatie > \90\Tools\Binn vindt (bijvoorbeeld: C:\ Program Files\Microsoft SQL Server\90\Tools\Binn).

 Let op het volgende wanneer u de procedure die in dit artikel wordt beschreven:

  • De service Taakplanner van Windows moet worden uitgevoerd op het moment dat de taak is gepland. Het is raadzaam dat u het opstarttype voor deze service als automatischinstellen. Dit zorgt ervoor dat de service zelfs op opnieuw zal worden uitgevoerd.
  • Er moet veel ruimte op het station waarop de back-ups worden geschreven. Wij raden u aan het schoonmaken van de oude bestanden in de map met reservekopieën regelmatig om er zeker van te zijn dat u niet voldoende schijfruimte uitvoert. Het script bevat de logica om oude bestanden op te schonen.

Aanvullende naslaginformatie