Jak przenieść pliki bazy danych SQL Server?

Mam bazę danych i chcę przenieść pliki .mdfi .ldfdo innej lokalizacji. Ale nie chcę zatrzymywać MSSQLSERVERusługi i nie chcę eksportować na inny serwer.

Jak mogę to zrobić?

Nie musisz zatrzymywać usługi SQL Server, aby przenieść pliki bazy danych, ale musisz przełączyć określoną bazę danych w tryb offline. Wynika to z faktu, że nie można przenosić plików podczas dostępu do nich, a przełączenie bazy danych w tryb offline powoduje, że pliki nie są używane przez aplikację SQL Server.

Proces ich przenoszenia jest dość prosty. Detach / Attach został już opisany, ale nie jest to prawie taki kompleks.

Zmień lokalizacje plików za pomocą ALTER DATABASEpolecenia:

USE master; --do this all from the master
ALTER DATABASE foo
MODIFY FILE (name='DB_Data1'
             ,filename='X:\NewDBFile\DB_Data1.mdf'); --Filename is new location

Uwaga: w tym poleceniu nie musisz deklarować starej lokalizacji. Zmiana tej ścieżki nie obowiązuje od razu, ale zostanie wykorzystana przy następnym uruchomieniu bazy danych.

Przełącz bazę danych w tryb offline

(Używam WITH ROLLBACK IMMEDIATEdo wyrzucania wszystkich i wycofywania wszystkich aktualnie otwartych transakcji)

ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;

Przenieś / skopiuj pliki do nowej lokalizacji

Po prostu skopiuj pliki przy użyciu swojej ulubionej metody (kliknij i przeciągnij, XCopy, Kopiuj element, Robocopy)

Przełącz bazę danych do trybu online

ALTER DATABASE foo SET ONLINE;

Możesz zobaczyć to szczegółowo opisane tutaj .

Pliki MDF i LDF są chronione i nie można ich przenosić, gdy baza danych jest w trybie online.

Jeśli nie masz nic przeciwko zatrzymaniu bazy danych, możesz to DETACHzrobić, przenieść pliki, a następnie ATTACHto.

• Kliknij prawym przyciskiem myszy nazwę bazy danych
• Wybierz Properties
• Idź do Fileszakładki
• Zanotuj Pathi FileNamepłyty MDF i LDF plików . Ten krok jest ważny, jeśli nie chcesz kończyć wyszukiwania brakujących plików ...
• Kliknij prawym przyciskiem myszy nazwę bazy danych
• Wybierz Tasks -> Detach
• Przenieś pliki tam, gdzie chcesz
• Kliknij prawym przyciskiem myszy Databaseswęzeł swojego serwera
• Wybierz Attach
• Kliknij Addprzycisk
• Wskaż nową lokalizację
• Kliknij OK

Powinieneś już być OK. Informacje na temat DETACH- ATTACHprocesu można znaleźć tutaj .

W linku about DETACH- ATTACHzaleca się użycie ALTER DATABASEinstrukcji, jeśli baza danych jest przechowywana w tym samym wystąpieniu programu SQL Server. Więcej informacji w Przenieś bazy danych użytkowników .

Jeśli chcesz, aby działał podczas ruchu, wykonaj BACKUP- RESTORE. W procesie przywracania możesz zdefiniować nową lokalizację plików bazy danych.

Aby przenieść systemowe pliki bazy danych, wykonaj następujące kroki:

1. Zaloguj się jako użytkownik sa w SSMS

2. Dla bezpieczeństwa wykonaj kopię zapasową bazy danych utworzonej przez użytkownika.

3. Zabij wszystkie sesje połączone z serwerem z SSMS.

4. Wykonaj następujące polecenie, aby sprawdzić bieżące położenie pliku systemowych baz danych:

   USE master;
SELECT * FROM sys.master_files;

Zidentyfikuj ścieżkę i zanotuj bieżącą ścieżkę plików.

5. Użyj TSQL, aby zmienić ścieżkę pliku dla wszystkich baz danych oprócz master:

   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

Na przykład:

ALTER DATABASE tempdb
MODIFY FILE ( NAME = tempdev
, FILENAME = "DestinationPath\tempdb.mdf");

ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog
, FILENAME = "DestinationPath\templog.ldf");

ALTER DATABASE model
MODIFY FILE ( NAME = modeldev
, FILENAME = "DestinationPath\model.mdf");

ALTER DATABASE model
MODIFY FILE ( NAME = modellog
, FILENAME = "DestinationPath\modellog.ldf");

ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBData
, FILENAME = "DestinationPath\msdbdata.mdf");

ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBLog
, FILENAME = "DestinationPath\msdblog.ldf");

Teraz lokalizacja pliku została zmieniona.

Pamiętaj, aby przenieść zarówno pliki ldf, jak i mdf

6. W SSMS kliknij prawym przyciskiem myszy Serwer i wybierz właściwości. Wewnątrz właściwości przejdź do Ustawienia bazy danych. Zmień domyślne lokalizacje bazy danych dla Data i Log na ścieżkę docelową. Wyloguj się z serwera.

   Np .: zmień C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\naE:\projects\DataBaseFiles\MSSQL\DATA\

7. Zatrzymaj wystąpienie programu SQL Server.

8. Skopiuj plik lub pliki do nowej lokalizacji. Użyj Robocopy, aby przenieść pliki, aby skopiować uprawnienia dostępu do folderu docelowego. Otwórz cmd i uruchom jako administrator i użyj następującego polecenia:

   robocopy / sec sourceFolder destinationFolder

Lepiej jest przejść do lokalizacji źródłowej, aby uruchomić polecenie. Usuń inne pliki inne niż systemowe pliki bazy danych, które są kopiowane. Na przykład:

 robocopy /sec .\DATA E:\projects\DataBaseFiles\MSSQL\DATA\

(Tutaj przenosimy wszystkie systemowe pliki bazy danych do nowej lokalizacji).

9. Z menu Start wskaż Wszystkie programy, wskaż Microsoft SQL Server, wskaż Narzędzia konfiguracyjne, a następnie kliknij SQL Server Menedżer konfiguracji.

Wykonaj następujące kroki w programie SQL Server Configuration Manager:

W węźle SQL Server Services kliknij prawym przyciskiem myszy wystąpienie SQL Server (na przykład SQL Server (MSSQLSERVER)) i wybierz Właściwości. W oknie dialogowym Właściwości SQL Server (nazwa_instancji) kliknij kartę Parametry uruchamiania. W polu Istniejące parametry wybierz parametr –d, aby przenieść plik danych podstawowych. Kliknij Aktualizuj, aby zapisać zmianę. W polu Określ parametr początkowy zmień parametr na nową ścieżkę głównej bazy danych. W polu Istniejące parametry wybierz parametr –l, aby przenieść główny plik dziennika. Kliknij Aktualizuj, aby zapisać zmianę. W polu Określ parametr początkowy zmień parametr na nową ścieżkę głównej bazy danych.

Wartość parametru dla pliku danych musi być zgodna z parametrem -d, a wartość dla pliku dziennika musi być zgodna z parametrem -l. Poniższy przykład pokazuje wartości parametrów dla domyślnej lokalizacji pliku danych podstawowych.

-dC:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\mastlog.ldf

Jeśli planowanym przeniesieniem dla pliku danych podstawowych jest E: \ SQLData, wartości parametrów zostaną zmienione w następujący sposób:

-dE:\projects\DataBaseFiles\MSSQL\DATA\master.mdf
-lE:\projects\DataBaseFiles\MSSQL\DATA\mastlog.ldf

Zatrzymaj wystąpienie programu SQL Server, klikając prawym przyciskiem myszy nazwę wystąpienia i wybierając polecenie Zatrzymaj. Uruchom ponownie wystąpienie programu SQL Server.

10. Zaloguj się jako saużytkownik w SSMS i sprawdź lokalizację plików bazy danych, wykonując następujące zapytanie:

    USE master;
SELECT * FROM sys.master_files;

Wszystko gotowe.

Robisz krok po kroku:

1. zamknij wszystkie połączenia
   ZMIEŃ BAZĘ DANYCH ZESTAW MyDB POJEDYNCZY_UŻYTKOWNIK Z NATYCHMIASTOWYM ROLLBACK

2. ustaw bazę danych ze statusem offline
   ZMIEŃ BAZĘ DANYCH ZESTAW MyDB OFFLINE

3. Do nowej ścieżki
   ZMIEŃ bazę danych MyDB MODIFY FILE (Name = MyDB, Filename = 'N: \ DATA \ MyDB.MDF')

4. ustaw bazę danych ze statusem online
   ZMIEŃ BAZA DANYCH ZESTAW MyDB ONLINE

5. ustaw dla wielu użytkowników
   ZMIEŃ BAZY DANYCH ZESTAW MyDB MULTI_USER

Istnieje sposób na przeniesienie plików danych bazy danych (jeszcze nie jestem pewien, czy można to zrobić w przypadku plików dziennika) bez przełączania bazy danych w tryb offline.

Dejan Nakarada-Kordic ma wyjaśnienie + skrypty dla tej metody tutaj: https://www.itprotoday.com/sql-server/move-database-files-with-taking-database-offline

Krótka wersja polega na tym, że dodajesz kolejny plik bazy danych w nowej lokalizacji, a następnie używasz pliku DBCC Shrinkfile, z opcją EMPTYFILE, aby przenieść dane ze starego pliku do nowego pliku. Po wykonaniu tej czynności możesz usunąć stary plik danych.

Nie moje rozwiązanie, sam szukałem tego rozwiązania i uznałem je za bardzo przydatne w naszym środowisku produkcyjnym.

Thorfinn

Wykonaj te proste 4 kroki:

1. Otwórz SSMS i wybierz opcję Nowe zapytanie z góry okna. Następnie skopiuj i wykonaj następujące zapytanie, aby znaleźć ścieżkę do bazy danych, którą chcemy przenieść na nową ścieżkę, i zanotuj ścieżkę pokazaną w CurrentLocationkolumnie.

SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID('DATABASE_NAME');

2. Teraz przejdź do tej ścieżki i zanotuj nazwę plików Database_Name.mdf & Database_Name_log.ldf . Następnie wykonaj następujące dwa zapytania, aby przenieść bazę danych do określonej lokalizacji.

ALTER DATABASE DATABASE_NAME MODIFY FILE ( NAME = DATABASE_FILE_NAME , FILENAME = 'NEW_PATH\DATABASE_NAME.mdf');

ALTER DATABASE DATABASE_NAME MODIFY FILE ( NAME = DATABASE_FILE_NAME_log , FILENAME = 'NEW_PATH\DATABASE_FILE_NAME_log.ldf');

3. Teraz zatrzymaj serwer klikając prawym przyciskiem myszy, Server_Namektóry możesz zobaczyć w Object Explorer (po lewej stronie).

   

4. Następnie przenieś oba pliki ze starej ścieżki do nowej ścieżki i ponownie uruchom serwer, klikając prawym przyciskiem myszy nazwę serwera. Potwierdź nową ścieżkę bazy danych, ponownie wykonując zapytanie pierwszego kroku.

Nie jestem pewien, czy jest to najlepszy sposób (chętnie przyjmę wszelkie komentarze, aby powiedzieć mi, jak to nie jest), ale jest to bardzo proste (i szybkie, jeśli masz małą bazę danych):

Najpierw wykonaj kopię zapasową bazy danych do pliku .bak. Następnie przywróć bazę danych z tego samego pliku .bak, wybierając nowe lokalizacje plików .mdf i .ldf w opcjach pliku dla zadania przywracania.

Nie zrobiłbym tego w środowisku produkcyjnym obok okna obsługi, ponieważ nie można uzyskać dostępu do bazy danych podczas przywracania. Inne metody, które widziałem powyżej, miałyby jednak podobne wady. Po zakończeniu zadania przywracania nie musisz usuwać starego pliku. Odbywa się to automatycznie.

Aby uzupełnić istniejące odpowiedzi: Oto skrypt do tworzenia ALTER DATABASE ... MOVE ...instrukcji dla wszystkich baz danych:

SELECT 'ALTER DATABASE ' + QUOTENAME(d.name) + 
       ' MODIFY FILE (name=' + QUOTENAME(f.name, '''') +
       ', filename=' + QUOTENAME(REPLACE(f.physical_name, 'C:\', 'D:\'), '''') +
       ');'
  FROM sys.master_files AS f 
       INNER JOIN sys.databases AS d ON f.database_id = d.database_id
 WHERE d.name <> 'master';

Uwaga:

• Zastąp REPLACE(f.physical_name, 'C:\', 'D:\')dowolną transformacją, którą chcesz wprowadzić do ścieżek plików.

• masterjest zwolniony, ponieważ jego ścieżka jest określana przez opcje uruchamiania programu SQL Server (szczegółowe informacje można znaleźć na przykład w tej odpowiedzi ).