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


Samenvatting


Edities van SQL Server Express 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 andere benadering voor back-up van uw database wanneer u deze edities.   

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:

    Maak een volledige back-up Database (SQL Server)
  • Gebruik een Transact-SQL-script dat van de familie BACKUP DATABASE opdrachten gebruikmaakt. Ga naar de volgende MSDN-website voor meer informatie:

    BACKUP (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.)

USE [master] 

GO 

/****** Object:  StoredProcedure [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]   

@databaseName sysname = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

 

SET NOCOUNT inschakelen;  

           

            DECLARE @DBs TABLE

            (

ID int IDENTITY primaire sleutel

                  DBNAME nvarchar(500)

            )

           

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

--Als specifieke database is gekozen om een back-up van alleen eruit die uit @DBs

In @DBs (DBNAME) invoegen

SELECT naam FROM master.sys.databases

            where state=0

            AND name=@DatabaseName

Of @DatabaseName IS NULL

Met de naam BESTELLEN

           

--Uitfilteren geen back-ups hoeft van databases

            IF @backupType='F'

                  BEGIN

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

                  END

ELSE IF @backupType = had'

                  BEGIN

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

                  END

ELSE IF @backupType = "L"

                  BEGIN

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

                  END

            ELSE

                  BEGIN

                  RETURN

                  END

           

--Declareren van variabelen

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000) 

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                  

                       

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

Selecteer lus @ = min(ID) FROM @DBs

 

Als @Loop niet NULL IS

      BEGIN

 

--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),'/','') + '_' + vervangen (converteren (VARCHAR, GETDATE(),108),':','')

 

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

      IF @backupType = 'F'

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

ELSE IF @backupType = had'

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

ELSE IF @backupType = "L"

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@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 = 'D'

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' 

                  BEGIN

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

                  END

       IF @backupType = 'D'

                  BEGIN

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

                  END

       IF @backupType = 'L' 

                  BEGIN

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

                  END

 

--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's Bureau-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 De map voor het uitvoerbare bestand van SQLCMD is over 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).

Zich bewust zijn van het volgende wanneer u de procedure die wordt beschreven in dit artikel:

  • 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