Sposób planowania i automatyzacji wykonywania kopii zapasowych baz danych SQL Server w programie SQL Server Express


Streszczenie


Programu SQL Server Express Edition oferuje metody do planowania zadań lub plany konserwacji, ponieważ składnik programu SQL Server Agent nie jest zawarte w tych wersjach. W związku z tym należy podjąć inne podejście, aby wykonać kopię zapasową bazy danych, korzystając z tych wersji.   

Obecnie użytkownicy programu SQL Server Express można wykonywać kopie zapasowe swoich baz danych przy użyciu jednej z następujących metod:

  • Użyj skryptu języka Transact-SQL, który używa poleceń rodziny kopii zapasowej bazy danych. Aby uzyskać więcej informacji przejdź do następującej witryny sieci Web MSDN:

    BACKUP (Transact-SQL)

W tym artykule opisano sposób za pomocą skryptu języka Transact-SQL wraz z harmonogramu zadań systemu Windows do automatyzacji wykonywania kopii zapasowych programu SQL Server Express baz danych na podstawie harmonogramu.

Więcej informacji


Należy wykonać trzy następujące czynności, aby wykonać kopię zapasową bazy danych SQL Server przy użyciu harmonogramu zadań systemu Windows:

Krok A: Użyj programu SQL Server Management Studio Express lub Sqlcmd, aby utworzyć następującą procedurę przechowywaną w bazie danych master:

Copyright © Microsoft Corporation.  Wszelkie prawa zastrzeżone.

Ten kod wydany zgodnie z warunkami

/ / Licencja publiczna Microsoft (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 

 

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

--Autor: Microsoft

--Data utworzenia: 2010-02-06

— Opis: wykonaj kopię zapasową bazy danych SQLExpress

-- Parameter1: databaseName 

--Parametr2: typ kopii zapasowej F = pełny, D = różnicowego, L = log

--Parametr3: Lokalizacja pliku kopii zapasowej

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

 

CREATE PROCEDURE [dbo]. [sp_BackupDatabases]   

@databaseName sysname = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

 

SET NOCOUNT;  

           

            DECLARE @DBs TABLE

            (

Int identyfikator klucza podstawowego tożsamości,

                  DBNAME nvarchar(500)

            )

           

--Dobrać tylko baz danych, które są w trybie online w przypadku wszystkich baz danych są wybierane do wykonania kopii zapasowej

--Jeśli wybrano konkretnej bazy danych do wykonania kopii nawet tylko wybierz który się na @DBs

WSTAWIĆ do @DBs (DBNAME)

SELECT Nazwisko FROM master.sys.databases

            where state=0

            AND name=@DatabaseName

Lub @DatabaseName ma wartość NULL

Zamówienia według nazwy

           

--Odfiltrować baz danych, które nie muszą się do kopii zapasowej

            IF @backupType='F'

                  BEGIN

@DBs Usuń gdzie w DBNAME ("tempdb", "Northwind", "pubs", "AdventureWorks")

                  END

ELSE IF @backupType = miał "

                  BEGIN

@DBs Usuń gdzie w DBNAME ("tempdb', 'Northwind', 'pubs', 'master',"AdventureWorks")

                  END

ELSE IF @backupType = 'L'

                  BEGIN

@DBs Usuń gdzie w DBNAME ("tempdb', 'Northwind', 'pubs', 'master',"AdventureWorks")

                  END

            ELSE

                  BEGIN

                  RETURN

                  END

           

--Deklarowanie zmiennych

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000) 

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                  

                       

--Pętlę za pomocą bazy danych jeden po drugim

Wybierz pętli @ = min(ID) FROM @DBs

 

a @Loop nie ma wartości NULL

      BEGIN

 

--Nazwy bazy danych muszą być w formacie [dbname], ponieważ niektóre z nich - lub _ w ich nazwy

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

 

--Ustaw bieżący format daty i godziny n yyyyhhmmss

SET @dateTime = Zastąp (Konwertuj (VARCHAR, GETDATE(),101),'/','') + '_' + zastąpić (Konwertuj (VARCHAR, GETDATE(),108),':','')

 

--Tworzenie nazwy pliku kopii zapasowej w formacie path\filename.extension pełne, diff i dziennika kopii zapasowych

      IF @backupType = 'F'

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

ELSE IF @backupType = miał "

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

ELSE IF @backupType = 'L'

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

 

--Kopii zapasowej nazwa określona do przechowywania w mediach

      IF @backupType = 'F'

SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') + "pełnej kopii zapasowej dla" + @dateTime

      IF @backupType = 'D'

SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') + "różnicowej kopii zapasowej dla" + @dateTime

      IF @backupType = 'L'

SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') + "Kopia zapasowa dziennika dla" + @dateTime

 

--Generowanie dynamicznego polecenia SQL do wykonania

 

       IF @backupType = 'F' 

                  BEGIN

@SqlCommand zestaw = +@DBNAME+ 'Kopia zapasowa bazy danych' "na dysk = '''+@BackupFile+ ''" z INIT, nazwa = ''' +@BackupName+'' ', NOSKIP, można "

                  END

       IF @backupType = 'D'

                  BEGIN

@SqlCommand zestaw = +@DBNAME+ "Kopia zapasowa bazy danych" "na dysk = '''+@BackupFile+ ''" o NAZWIE różnicowego, INIT, = '' "+@BackupName+'' ', NOSKIP, można"

                  END

       IF @backupType = 'L' 

                  BEGIN

@SqlCommand zestaw = +@DBNAME+ "BACKUP LOG" "na dysk = '''+@BackupFile+ ''" z INIT, nazwa = ''' +@BackupName+'' ', NOSKIP, można "

                  END

 

--Wykonania wygenerowany polecenia SQL

       EXEC(@sqlCommand)

 

— Przejdź do kolejnej bazy danych

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

 

KONIEC

Krok B: W edytorze tekstu, Utwórz plik wsadowy o nazwie Sqlbackup.bat, a następnie skopiuj tekst z jednego z następujących przykładów do tego pliku, w zależności od danego scenariusza:

Example1: Pełne kopie zapasowe wszystkich baz danych w lokalne wystąpienie nazwane SQLEXPRESS przy użyciu uwierzytelniania systemu Windows

// Sqlbackup.bat

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

Example2: Różnicowe kopie zapasowe wszystkich baz danych w lokalne wystąpienie nazwane SQLEXPRESS przy użyciu SQLLogin i jego hasło

// Sqlbackup.bat

polecenie sqlcmd - U SQLLogin -P hasło -S. \SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ="D:\SQLBackups", @BackupType = miał" "

Uwaga: shouldhave SQLLogin, o co najmniej roli operatora kopii zapasowych programu SQL Server.

Przykład 3: Kopie zapasowe dziennika wszystkich baz danych w lokalne wystąpienie nazwane SQLEXPRESS przy użyciu uwierzytelniania systemu Windows

// Sqlbackup.bat

-S polecenie sqlcmd. \SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation = miał: \SQLBackups\", @backupType = 'L' "

Przykład 4: Pełne kopie zapasowe bazy danych USERDB w lokalne wystąpienie nazwane SQLEXPRESS przy użyciu uwierzytelniania systemu Windows

// Sqlbackup.bat

-S polecenie sqlcmd. \SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation = miał: \SQLBackups\", @databaseName = "USERDB", @backupType = 'F' "

Podobnie, można dokonać różnicowa kopii USERDB, wklejając w byłby ' dla parametru @backupType i kopia zapasowa dziennika USERDB przez wklejenie 'L' dla parametru @backupType .

Krok C: Harmonogram pracy przy użyciu harmonogramu zadań systemu Windows do wykonywania pliku wsadowego, który został utworzony w kroku B. Aby to zrobić, wykonaj następujące kroki:

  1. Na komputerze, na którym jest uruchomiony program SQL Server Express, kliknij przycisk Start, wskaż Wszystkie programy, wskaż polecenie Akcesoria, wskaż polecenie Narzędzia systemowe, a następnie kliknij Zaplanowane zadania.  
  2. Kliknij dwukrotnie ikonę Dodaj zaplanowane zadanie.  
  3. W Kreatorze zaplanowanych zadań kliknij przycisk Dalej.  
  4. Kliknij przycisk Przeglądaj, kliknij plik wsadowy utworzony w kroku B, a następnie kliknij przycisk Otwórz.  
  5. Wpisz nazwę zadania SQLBACKUP , kliknij codziennie, a następnie kliknij przycisk Dalej.  
  6. Określ informacje harmonogramu uruchomić zadanie. (Firma Microsoft zaleca się uruchomienie tego zadania co najmniej raz dziennie). Następnie kliknij przycisk Dalej.
  7. W polu Wprowadź nazwę użytkownika wpisz nazwę użytkownika, a następnie wpisz hasło w polu Wprowadź hasło .

    Uwaga Ten użytkownik powinny przynajmniej być przypisane roli BackupOperator na poziomie programu SQL Server Jeśli używasz jednego z plików wsadowych w przykładzie 1, 3 lub 4.
  8. Kliknij przycisk Dalej, a następnie kliknij przycisk Zakończ.  
  9. Wykonać zaplanowane zadanie co najmniej jeden raz, aby upewnić się, że kopia zapasowa jest tworzony pomyślnie.

Uwaga Folder dla pliku wykonywalnego SQLCMD znajduje się zazwyczaj w zmiennych ścieżek dla serwera po zainstalowaniu programu SQL Server, ale jeśli zmiennej Path nie ma tego folderu, możesz go znaleźć, pod \90\Tools\Binn < lokalizacja instalacji > (na przykład: C:\Program Files\Microsoft SQL Server\90\Tools\Binn).

Przypadku użyć procedury, którą opisano w tym artykule należy pamiętać o następujących:

  • Zadanie jest zaplanowane do uruchomienia w chwili musi działać usługa harmonogramu zadań systemu Windows. Zalecane ustawienie Typ uruchomienia dla tej usługi jako Automatyczne. Daje to pewność, że usługa zostanie uruchomiona nawet na ponowne uruchomienie komputera.
  • Powinny być dużo miejsca na dysku, na którym są zapisywane kopie zapasowe. Firma Microsoft zaleca się wyczyszczenie starych plików w folderze kopii zapasowej regularnie, aby upewnić się, że nie zabraknie miejsca na dysku. Skrypt nie zawiera logikę do oczyszczanie starych plików.

Dodatkowe informacje