Jak przywrócić wiele baz danych w SQL Server (3 metody)

Czy możesz przywrócić wiele baz danych SQL naraz?

Jest przykro nam, ale interfejs graficzny SSMS obsługuje tylko przywracanie jednej bazy danych na raz. Jeśli wykonałeś kopię zapasową kilku baz danych do różnych plików BAK, czy można je wszystkie przywrócić naraz?

Odpowiedź brzmi TAK. Ogólnie rzecz biorąc, możesz użyć prawidłowego skryptu T-SQL, aby przywrócić wiele baz danych naraz, ale tylko wtedy, gdy pliki kopii zapasowych mają dokładnie nazwy odpowiadające bazom danych i nie zawierają dodatkowych informacji, takich jak data/czas.

Jeśli chcesz mieć większą elastyczność, istnieje również proste alternatywne rozwiązanie z interfejsem graficznym użytkownika. Wybierz tylko sposób, który preferujesz.

Wymagania przed przywróceniem kilku baz danych w SQL Server

Przywracanie kilku baz danych w SQL Server może być procesem skomplikowanym. Aby zminimalizować okres niedostępności, przed rozpoczęciem należy spełnić kilka wymagań wstępnych:

• Mieć uprawnienia administratora i dostęp do plików kopii zapasowych.
• Każdy plik kopii zapasowej powinien odpowiadać jednej bazie danych.
• Plik kopii zapasowej powinien zawierać tylko dwa pliki bazy danych: plik dziennika i plik danych.
• Ponieważ używamy nazwy bazy danych do nazywania jej fizycznych plików na dysku, znaki dozwolone w nazwach plików fizycznych nowo przywracanej bazy danych muszą być dozwolone.

Sposób 1. Przywracanie wielu baz danych z plików BAK za pomocą xp_cmdshell

Możesz użyć skryptu do przywracania wielu baz danych w SQL Server z folderu. Przed tym jednak połącz się z instancją i włącz xp_cmdshell.

1. Kliknij Nowe zapytanie i wpisz następującą komendę:

-- Aby umożliwić zmianę zaawansowanych opcji.
EXEC sp_configure "show advanced options", 1;
GO
-- Aby zaktualizować bieżącą wartość opcji zaawansowanych.
RECONFIGURE;
GO
-- Aby włączyć funkcję.
EXEC sp_configure "xp_cmdshell", 1;
GO
-- Aby zaktualizować bieżącą wartość dla tej funkcji.
RECONFIGURE;
GO

Wykonaj zapytanie, a funkcja zostanie włączona.

2. Następnie możesz przywrócić wiele baz danych SQL Server za pomocą skryptu (proszę zamienić „D:\backup\” na własny katalog zawierający wszystkie pliki kopii zapasowych):

DECLARE @FilesCmdshell TABLE (
outputCmd NVARCHAR (255)
)
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)

INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell "dir /B D:\backup\*.bak"
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell

OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @cmd NVARCHAR(MAX) = "RESTORE DATABASE [" SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX(".", @FilesCmdshellOutputCmd)) "] FROM DISK = N"'D:\backup\" SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX(".", @FilesCmdshellOutputCmd)) ".bak"' WITH FILE = 1, NOUNLOAD, STATS = 10"
EXEC(@cmd)

FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END

✎Uwaga: Jeśli otrzymasz błąd „Końcówka dziennika bazy danych nie została zabezpieczona. Użyj polecenia BACKUP LOG WITH NORECOVERY, aby wykonać kopię zapasową dziennika, jeśli zawiera on pracę, której nie chcesz stracić. Użyj klauzuli WITH REPLACE lub WITH STOPAT instrukcji RESTORE, aby nadpisać zawartość dziennika”, rozwiązanie jest właśnie takie, jak sugeruje komunikat.

Na przykład możesz zmodyfikować polecenie przywracania do „…WITH FILE = 1, REPLACE, NOUNLOAD, STATS = 10”

Metoda 2. Generowanie skryptu do przywracania wszystkich baz danych w SQL Server

Alternatywnie, można użyć skryptu do generowania poleceń przywracania dla wszystkich baz danych w SQL Server i połączyć je jako nowy skrypt do wykonania.

1. Kliknij Nowe zapytanie i wprowadź następujące polecenia w oknie SQLQuery:

DECLARE @folderpath VARCHAR (1000)
SELECT @folderpath = "D:\Backup\" -- Lokalizacja kopii zapasowych
SELECT "RESTORE DATABASE[" NAME "] FROM DISK = "'" @folderpath name ".bak"' WITH NORECOVERY,
REPLACE, STATS = 5"
FROM master.sys.databases
WHERE name NOT IN ("master","model","msdb","tempdb","distribution")

To spowoduje wygenerowanie serii poleceń do przywracania wielu baz danych w SQL Server z plików bak o tych samych nazwach.

2. Kliknij prawym przyciskiem myszy dowolne polecenie z Rezultatów, wybierz Zaznacz wszystko, a następnie Kopiuj je (lub użyj Ctrl A i Ctrl C).

3. Wklej te polecenia do okna SQLQuery jako nowy skrypt do Wykonania. Spowoduje to przywrócenie wszystkich baz danych SQL Server z plików bak o odpowiednich nazwach plików.

Metoda 3. Tworzenie kopii zapasowych i przywracanie wielu baz danych SQL za pomocą interfejsu graficznego (najłatwiejsza)

Wyżej wymienione skrypty są stosowane tylko wtedy, gdy pliki bak mają dokładnie takie same nazwy jak bazy danych SQL, co jest dość ograniczające w praktyce. Dlatego chciałbym zaproponować bardziej wygodny sposób tworzenia kopii zapasowych i przywracania wielu baz danych, a nawet całej instancji.

AOMEI Cyber Backup to niezawodne rozwiązanie zarządzania w centralnym miejscu do tworzenia kopii zapasowych i przywracania baz danych SQL na wszystkich komputerach stacjonarnych, laptopach i serwerach w ramach sieci LAN.

Działa na systemach Windows 11/10/8.1/8/7, Windows Server 2022/2019/2016/2012 (R2)/2008 (R2) i obsługuje tworzenie kopii zapasowych dla systemu SQL Server 2005 do 2022. Oprócz bazy danych SQL, obsługuje również tworzenie kopii zapasowych maszyn wirtualnych Hyper-V i VMware.

• Uruchom pobrany plik .exe, aby zainstalować AOMEI Cyber Backup i uruchomić jego główny interfejs. Kliknij Urządzenie źródłowe -> Microsoft SQL -> Dodaj Microsoft SQL.

• Wybierz Pobierz program proxy lub Kopiuj link, aby pobrać program agenta i zainstaluj go na urządzeniu, na którym zainstalowany jest SQL Server. Następnie przejdź do Proxy już zainstalowane, aby wybrać urządzenie.
• Następnie kliknij -> Uwierzytelnianie w celu potwierdzenia instancji bazy danych. Możesz wybrać Uwierzytelnianie systemu Windows lub Uwierzytelnianie SQL.

To wszystko, teraz możesz zaplanować tworzenie kopii zapasowych danych dla dodanego serwera SQL.

◉ Jak utworzyć kopię zapasową wielu baz danych w SQL Server:

1. Kliknij Zadanie tworzenia kopii zapasowej -> Utwórz nowe zadanie, a następnie wybierz typ kopii zapasowej jako Kopia zapasowa Microsoft SQL.

2. Kliknij na Nazwa urządzenia, aby określić instancję SQL i bazy danych do kopii zapasowej. Możesz wybrać jedną lub wiele baz danych według potrzeb.

3. Wybierz docelowy Magazyn, w którym chcesz zapisać kopie zapasowe SQL. Możesz określić ścieżkę lokalną lub sieciową.

4. Skonfiguruj Harmonogram, aby wykonywać codzienne, tygodniowe lub miesięczne kopie zapasowe bazy danych SQL, oraz wybierz metodę Kopiowania pełnego, inkrementalnego lub różnicowego.

Kliknij Rozpocznij kopię zapasową , aby utworzyć i uruchomić zadanie. Po zakończeniu znajdziesz je w zakładce Zadanie kopii zapasowej.

✍Więcej przydatnych funkcji:

• Wyczyść kopie zapasowe pomaga w automatycznym usuwaniu starszych wersji kopii zapasowych, co pozwala zaoszczędzić miejsce na dysku.
• Powiadomienia email umożliwiają otrzymywanie powiadomień email, gdy zadanie nie przebiega prawidłowo lub jest zakończone sukcesem.
• Archiwizacja pomaga archiwizować pliki kopii zapasowych do magazynu AWS S3.

◉ Jak przywrócić wiele baz danych z kopii zapasowej:

1. Kliknij Zadanie kopii zapasowej na pasku menu po lewej stronie, wyszukaj zadanie do przywrócenia i kliknij -> Przywróć.

2. Wybierz wersję kopii zapasowej, z której chcesz przywrócić bazy danych SQL.

3. Następnie wybierz lokalizację docelową, do której chcesz przywrócić dane. Istnieją 2 opcje:

• Przywróć do oryginalnej lokalizacji (domyślnie): przywracanie w miejscu, które pozwala przywrócić bazy danych SQL do starszej wersji.
• Przywróć do nowej lokalizacji: wybierz inny cel, aby przeprowadzić przywracanie poza miejscem. Możesz określić nazwę nowej bazy danych i zmienić lokalizację przechowywania.

Nadpisz bazę danych o tej samej nazwie: Jeżeli zaznaczysz tę opcję, skopiowane bazy danych nadpiszą docelowe bazy danych o tych samych nazwach. Jeżeli pozostawisz to odznaczone, bazy danych docelowe o tych samych nazwach zostaną pominięte podczas przywracania.

4. Po ustawieniu wszystkich parametrów, kliknij przycisk Rozpocznij przywracanie, aby rozpocząć proces przywracania i cierpliwie poczekaj aż zostanie zakończony. Możesz kliknąć przycisk , aby zobaczyć szczegóły przywracania lub je anulować.

✍Uwagi:

• "Przywróć do oryginalnego miejsca" musi potwierdzić istnienie oryginalnego miejsca. W przeciwnym przypadku można wybrać tylko "Przywróć do nowego miejsca".
• "Przywróć do oryginalnego miejsca" nadpisze lub usunie dane oryginalnej bazy danych, jeśli oryginalna baza danych zawiera ważne dane, zaleca się wybranie "Przywróć do nowego miejsca".

Podsumowanie

Interfejs GUI SSMS umożliwia przywracanie tylko jednej bazy danych na raz. Jeśli chcesz przywrócić wiele baz danych w SQL Server naraz, najczęściej stosowanym rozwiązaniem jest użycie skryptu T-SQL. Jednakże, ma on surowe ograniczenia dotyczące nazw plików kopii zapasowych, a operacje takie jak przywracanie baz danych do innego egzemplarza są trudne do wykonania. Dlatego warto spróbować oprogramowania do tworzenia kopii zapasowych SQL - AOMEI Cyber Backup.

Ułatwia to operacje. Na przykład, można automatycznie tworzyć kopie zapasowe baz danych SQL za pomocą kilku prostych kliknięć, a następnie równie łatwo przywrócić wiele baz danych naraz.