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

Dotyczy: Microsoft SQL Server

Podsumowanie


Wersje programu SQL Server Express 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: Kopia zapasowa (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.)

UŻYJ [główny] 

PRZEJDŹ 

/ *** Obiekt:  procedura przechowywana [dbo]. [sp_BackupDatabases] *** / 

Ustaw ANSI_NULLS NA 

PRZEJDŹ 

Ustaw QUOTED_IDENTIFIER NA 

PRZEJDŹ 

 

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

--Autor: Microsoft 

--Data utworzenia: 2010-02-06

— Opis: wykonaj kopię zapasową bazy danych SQLExpress

--Parametr1: 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) 

JAKO 

 

       SET NOCOUNT; 

           

            ZADEKLAROWAĆ @DBs tabeli

            (

                  Int identyfikator klucza podstawowego tożsamości,

                  DBNAME nvarchar(500)

            )

           

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

             --Jeżeli wybrano konkretnej bazy danych do wykonania kopii zapasowej tylko wybierz który się na @DBs

            WŁÓŻ do @DBs (DBNAME)

            SELECT Nazwisko FROM master.sys.databases

            w przypadku gdy stan = 0

            I name=@DatabaseName

            LUB @DatabaseName IS NULL

            SORTUJ wg nazwy

           

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

            IF @backupType = 'F'

                  ROZPOCZĄĆ

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

                  KONIEC

            ELSE IF @backupType = miał "

                  ROZPOCZĄĆ

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

                  KONIEC

            ELSE IF @backupType = 'L'

                  ROZPOCZĄĆ

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

                  KONIEC

            ELSE

                  ROZPOCZĄĆ

                  ZWROT

                  KONIEC

           

            --Deklarowanie zmiennych

            ZADEKLAROWAĆ @BackupName varchar(100)

            ZADEKLAROWAĆ @BackupFile varchar(100)

            ZADEKLAROWAĆ @DBNAME varchar(300)

            ZADEKLAROWAĆ @sqlCommand NVARCHAR(1000) 

        ZADEKLAROWAĆ @dateTime NVARCHAR(20)

            ZADEKLAROWAĆ @Loop int                  

                       

            --Pętli baz danych jeden po drugim

            Wybierz @Loop = min(ID) FROM @DBs

 

      Podczas @Loop IS NOT NULL

      ROZPOCZĄĆ

 

--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),'/','') + '_' +  ZAMIENIĆ (CONVERT (VARCHAR, GETDATE(),108),':','')  

 

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

      IF @backupType = 'F'

            @BackupFile zestaw = @backupLocation + zastąpić (zastąpić (@DBNAME, "[', ''),'] ','')"_FULL_"+ @dateTime+ +". BAK "

      ELSE IF @backupType = miał "

            @BackupFile zestaw = @backupLocation + zastąpić (zastąpić (@DBNAME, "[', ''),'] ','')"_DIFF_"+ @dateTime+ +". BAK "

      ELSE IF @backupType = 'L'

            @BackupFile zestaw = @backupLocation + zastąpić (zastąpić (@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 = miał "

            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' 

                  ROZPOCZĄĆ

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

                  KONIEC

       IF @backupType = miał "

                  ROZPOCZĄĆ

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

                  KONIEC

       IF @backupType = 'L' 

                  ROZPOCZĄĆ

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

                  KONIEC

 

--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 Plik wykonywalny folder dla SQLCMD jest na ogół 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).

 Użyć procedury, którą opisano w tym artykule, należy pamiętać o następujących czynności:

  • 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