Jak mogę wykonać kopię zapasową zdalnej bazy danych SQL Server na dysku lokalnym?


241

Muszę skopiować bazę danych ze zdalnego serwera na lokalny. Próbowałem użyć programu SQL Server Management Studio, ale tworzy on kopię zapasową tylko na dysku na zdalnym serwerze.

Kilka punktów:

  • Nie mam dostępu do zdalnego serwera w sposób umożliwiający kopiowanie plików;
  • Nie mam dostępu do konfiguracji ścieżki UNC do mojego serwera;

Wszelkie pomysły na to, jak mogę skopiować tę bazę danych? Czy będę musiał używać narzędzi innych firm?


1
Myślę, że jeśli w ogóle nie masz dostępu do struktury katalogów, będziesz musiał podjąć próbę zrobienia tego.
JNK

3
Próbujesz po prostu skopiować bazę danych, a nie wykonać kopię zapasową? Jeśli tak, możesz skorzystać z Kreatora kopiowania bazy danych lub (w SQL Server 2008) użyć opcji „Generuj skrypty” do skryptu schematu i danych. Przydatne mogą być również Redgate SQL Compare i Data Compare.
Martin Smith

1
@MartinSmith Zgodnie z twoją sugestią wygenerowałem skrypt za pomocą Generate and Publish Scriptsopcji. Dostaję wszystkie tabele i schemat, ale nie otrzymałem żadnych danych z tabelami. Jak mogę to naprawić?
jesteś naprawdę przyjacielem

Odpowiedzi:


182

W Microsoft SQL Server Management Studio możesz kliknąć prawym przyciskiem myszy bazę danych, którą chcesz wykonać kopię zapasową, a następnie kliknąć Zadania -> Generuj skrypty.

Wyskakujące okno otwiera kreatora, w którym można ustawić następujące opcje, aby wykonać przyzwoitą kopię zapasową bazy danych, nawet na zdalnym serwerze :

  • Wybierz bazę danych, którą chcesz wykonać kopię zapasową, i kliknij Dalej,
  • W opcjach, które przedstawia ci:
    1. W 2010 r .: w obszarze Opcje tabeli / widoku zmień opcję „Dane skryptu” i „Indeksy skryptu” na wartość Prawda, a następnie kliknij przycisk Dalej,
    2. W 2012 r .: w sekcji „Ogólne” zmień „Typy danych na skrypt” z „Tylko schemat” na „Schemat i dane”
    3. W 2014 r .: opcja skryptu danych jest teraz „ukryta” w kroku „Ustaw opcje skryptów”, należy kliknąć „Zaawansowane” i ustawić „Typy danych do skryptu” na wartość „Schemat i dane”
  • W kolejnych czterech oknach kliknij „wybierz wszystko”, a następnie dalej,
  • Wybierz skrypt do nowego okna zapytania

Gdy to zrobisz, będziesz mieć gotowy skrypt zapasowy przed sobą. Utwórz nową lokalną (lub zdalną) bazę danych i zmień pierwszą instrukcję „USE” w skrypcie, aby użyć nowej bazy danych. Zapisz skrypt w bezpiecznym miejscu i uruchom go na nowej, pustej bazie danych. Powinno to stworzyć (prawie) zduplikowaną lokalną bazę danych, którą można następnie wykonać w dowolny sposób.

Jeśli masz pełny dostęp do zdalnej bazy danych, możesz zaznaczyć opcję „skryptuj wszystkie obiekty” w pierwszym oknie kreatora, a następnie zmień opcję „Baza danych skryptów” na True w następnym oknie. Uważaj jednak, musisz wykonać pełne wyszukiwanie i zamianę nazwy bazy danych w skrypcie na nową bazę danych, której w tym przypadku nie będziesz musiał utworzyć przed uruchomieniem skryptu. Powinno to stworzyć dokładniejszy duplikat, ale czasami nie jest dostępny z powodu ograniczeń uprawnień.


9
W SQL Server Management Studio 2012 nie ma takiej opcji jak „Dane skryptu”, więc w nowszych wersjach wykonaj następujące czynności dla kroku 2: w obszarze „Ogólne” zmień „Typy danych na skrypt” z „Tylko schemat” na „Schemat” i dane ”.
berezovskyi

1
Sprawdziłem to na SQL Server 2008 R2 i działa jak urok. W trosce o wykorzystanie go do tworzenia strategii tworzenia kopii zapasowych potrzebuję dodatkowej pracy, ale myślę, że jest to lepsze niż używanie narzędzi stron trzecich, takich jak RedGate itp. Może jestem paranoją, ale myślę, że większość narzędzi stron trzecich ma dodatkowy kod i dostanie mój kontroluj i ograniczaj prostotę i wyjaśnianie skryptu. Dzięki wielkie.
QMaster

2
@ShaunLuttin Jeśli w drugim kroku (w 2012 r.) Instrukcje INSERT powinny zostać wyświetlone, należy zmienić „Typy danych na skrypt” z „Tylko schematu” na „Schemat i dane”.
Daniel Gill

6
W SSMS 2014 opcja zmiany ze schematu tylko na schemat i dane jest teraz ukryta za „Zaawansowane” przy wyborze miejsca zapisania skryptu.
MushinNoShin,

2
Kiedy próbuję uruchomić skrypt za pomocą tej metody, dostaję błąd ze studia zarządzania serwerem SQL na temat „Niewystarczającej ilości pamięci, aby kontynuować wykonywanie programu. (Mscorelib)”
Rob

51

Po pierwsze, przyznaj pełne uprawnienia kontroli ścieżce lokalnej na twoim komputerze (jak pokazano poniżej) wszystkim. (Lub alternatywnie udzielić uprawnień specjalnie dla konta agenta SQL Server).

Po drugie, wykonaj następujące czynności:

BACKUP DATABASE [dev] TO  DISK = N'\\myMachine\c\dev.bak' WITH COPY_ONLY, INIT;

14
To jest odpowiedź na pytanie. Spowoduje to „utworzenie kopii zapasowej zdalnej bazy danych SQL Server na dysku lokalnym”, a nie „skryptowych tabel i danych”. Ktoś: Proszę zaznaczyć tę odpowiedź jako zaakceptowaną i usunąć błędną odpowiedź Daniela Gilla
Henrik Holmgaard Høyer

choć myślę, że to lepsza odpowiedź, pytanie brzmi: „Jakieś pomysły na to, jak mogę skopiować tę bazę danych?” nie specjalnie kopia zapasowa.
Oliver Townshend

11
Zakłada się, że komputer zdalny ma dostęp do plików komputerów lokalnych, co nie jest przypadkiem ogólnym.
Anders Lindén,

Chciałbym móc cię głosować więcej niż raz. To idealna odpowiedź i coś, co chciałem robić od lat. Tak proste ... tak idealne. Nie rozwiąże wszystkich spraw na świecie, ale działało idealnie w moim przypadku, gdy jestem przeniesiony do komputera, ale nie do komputera z SQL Server.
Wade Hatler,

To też ma dla mnie pracownika. Wystarczy udostępnić folder lokalny tymczasowo ze wszystkim , należy wykonać kopię zapasową, a następnie dzielić wyłączyć. Myślę też, że to powinna być poprawna odpowiedź.
Fred Rogers,

51

Aby skopiować tylko dane i schemat (nie kopiuje procedur przechowywanych, funkcji itp.), Skorzystaj z Kreatora importu i eksportu programu SQL Server i wybierz Nowy ... podczas wybierania docelowej bazy danych.

Kliknij prawym przyciskiem myszy Baza danych> Zadania> Importuj dane.

Wybierz źródło danych

  • Źródło danych : SQL Server Native Client
  • Nazwa serwera : serwer zdalny
  • Uwierzytelnianie :
  • Baza danych : nazwa db

Wybierz miejsce docelowe

  • Źródło danych : SQL Server Native Client
  • Nazwa serwera : serwer lokalny
  • Uwierzytelnianie :
  • Baza danych :New...

Reszta jest prosta.


2
To działało dla mnie znacznie lepiej niż zaakceptowana odpowiedź. Obsługuje relacje między tabelami znacznie czystiej.
MushinNoShin

7
Welp Niestety nie ustawia tożsamości i domyślnych ograniczeń. :(
MushinNoShin

1
Jest to o wiele lepszy sposób niż generowanie skryptów. Czasami skrypty mogą być dość duże. Osobiste preferencje. Bez obrazy :)
Vikas,

Jest to o wiele prostsze i chciałbym przejść do tego, ale jeśli z jakiegoś powodu potrzebujesz kolumn takich jak object_id, create_data, zmodyfikuj datę JAK JEST w sys.tables, pamiętaj tylko, że zostaną zmodyfikowane podczas tworzenia.
Crismogram,

@MushinNoShin możesz najpierw wygenerować skrypty, a następnie importować / eksportować dane, umożliwiając wstawianie tożsamości na etapie mapowania.
HasanG,

29

Nie można utworzyć kopii zapasowej ze zdalnego serwera na dysk lokalny - po prostu nie ma takiej możliwości. I o ile mi wiadomo, nie ma narzędzi innych firm, aby to zrobić.

Wszystko, co możesz zrobić, to utworzyć kopię zapasową na zdalnym serwerze, poprosić kogoś, aby ją skompresował i wysłał do Ciebie.


5
Nie zgadzam się. W wielu okolicznościach można użyć polecenia „Generuj skrypty” MS SQL Server Management Studio, aby utworzyć skrypt, który można uruchomić w celu wygenerowania lokalnej bazy danych, którą można następnie wykonać według własnego uznania. Zobacz moją odpowiedź poniżej lub komentarz Martina Smitha do pytania.
Daniel Gill

19
@Rafid: tak, ale to NIE jest prawdziwa kopia zapasowa - to eksport skryptu / danych .....
marc_s

2
@marc_s Co takiego jest w „prawdziwej kopii zapasowej”, która różni się od wyeksportowanej kopii? Dziennik? Coś jeszcze?
Dronz

31
@Dronz: TAK! Eksport skryptu odtworzy strukturę, a być może nawet dane w tabeli - ale nie może zawierać takich rzeczy, jak dzienniki transakcji i statystyki oraz inne istotne części bazy danych SQL Server.
marc_s

1
@ShaunLuttin, możesz dołączyć dane.
Erwin Rooijakkers,

17

Wiem, że to późna odpowiedź, ale muszę skomentować najczęściej głosowaną odpowiedź, która mówi, aby użyć opcji generowania skryptów w SSMS.

Problem w tym, że ta opcja niekoniecznie generuje skrypt w prawidłowej kolejności wykonywania, ponieważ nie bierze pod uwagę zależności.

W przypadku małych baz danych nie stanowi to problemu, ale w przypadku większych jest to z pewnością spowodowane koniecznością ręcznej zmiany kolejności tego skryptu. Wypróbuj to na bazie 500 obiektów;)

Niestety w tym przypadku jedynym rozwiązaniem są narzędzia innych firm.

Z powodzeniem korzystałem z narzędzi porównawczych ApexSQL (Diff i Data Diff) do podobnych zadań, ale nie można się pomylić z żadnymi innymi już tutaj wymienionymi, szczególnie z Red Gate.


1
nie zgadzam się, dla mnie sql zawsze porządkuje zależności poprawnie, czy masz konkretny przykład, jak odtworzyć wspomniane zachowanie?
piła

istnieje również możliwość wzięcia tego pod uwagę
Gaspa79

13

Możesz wypróbować SQLBackupAndFTP . Stworzy skrypty do utworzenia wszystkich obiektów w bazie danych i instrukcje INSERT dla wszystkich wierszy w tabelach. W dowolnej bazie danych możesz uruchomić ten plik skryptu, a cała baza danych zostanie ponownie utworzona.


4
Oddelegowany. Musiałem zainstalować nową wersję studia zarządzania serwerem SQL i nie mogłem skryptu, jak kiedyś. To narzędzie załatwiło sprawę szybko i bez problemów, nawet bez pełnego dostępu do zdalnego serwera (dodatek appharbor sqlserver)
Daniel Gill

1
Pamiętaj, że to oprogramowanie mocno zużywa zasoby.
Dementyczny

Oprócz buggy i nie do końca pewny, że to działa poprawnie. Czy można połączyć się ze zdalnym serwerem bez problemu przez PHP, ale nie może? Gdy mam odpowiednie ustawienia na samym serwerze. TAK ... ruszam dalej.
Shawn Rebelo

7

1
Podejście to wyjaśnia, w jaki sposób utworzyć skrypt dla bazy danych, utworzyć ją lokalnie, a następnie użyć narzędzi do zarządzania SQL do kopiowania danych między lokalną i zdalną bazą danych.
Drew Noakes,

1
Możesz przełączyć opcję „Dane skryptu” na „Prawda”, aby jednocześnie wykonać skrypt danych.
Daniel Gill

Kopiowanie i tworzenie kopii zapasowych to dwie różne rzeczy.
Anders Lindén

To było idealne, ponieważ muszę pracować na bazie danych 2016 przy użyciu serwera SQL 2012 i nie mogę użyć kopii zapasowej 2016 w 2012 r.
majjam

6

Istnieje 99% rozwiązanie, aby pobrać plik BAK ze zdalnego serwera SQL na lokalny komputer. Opisałem to tam w moim poście http://www.ok.unsode.com/post/2015/06/27/remote-sql-backup-to-local-pc

Ogólnie będzie to wyglądało tak:

  • wykonaj skrypt SQL, aby wygenerować pliki BAK

  • uruchom skrypt sql, aby wstawić każdy plik BAK do tabeli temp z typem pola varbinary i wybierz ten wiersz i pobierz dane

  • powtórz poprzedni krok tyle razy, ile masz plików BAK

  • uruchom skrypt sql, aby usunąć wszystkie zasoby tymczasowe

to wszystko, masz pliki Bak na lokalnym komputerze.


5

Możesz użyć Kopiuj bazę danych ... kliknij prawym przyciskiem myszy zdalną bazę danych ... wybierz zadania i użyj kopiuj bazę danych ... zapyta Cię o serwer źródłowy i docelowy. że twoje źródło jest zdalne, a miejscem docelowym jest lokalna instancja serwera SQL.

to jest takie proste


Kopiowanie bazy danych wymaga uprawnień SysAdmin. To nie jest rozwiązanie dla zwykłych użytkowników bazy danych.
Buggieboy

W dzisiejszym wydaniu nasz zespół programistów ma uprawnienia sysadmin. Więc użyję podejścia Pouyana.
Kody z Hammer

ale muszę to robić codziennie i potrzebuję skryptu, aby to osiągnąć
wyjaśnienie

3

Gang AppHarbor boryka się z tym i opracował tymczasowe rozwiązanie z wykorzystaniem obiektów do zarządzania serwerem SQL i SqlBulkCopy.

Sprawdź ich post na blogu lub przejdź bezpośrednio do kodu .

Przetestowali to tylko z AppHarbor, ale może warto to sprawdzić.


3

Powyższe odpowiedzi są po prostu nieprawidłowe. Skrypt SQL nawet z danymi nie jest kopią zapasową. Kopia zapasowa to plik BAK, który zawiera pełną bazę danych w bieżącej strukturze, w tym indizes.

Oczywiście plik BAK zawierający pełną kopię zapasową wszystkich danych i indeksów ze zdalnej bazy danych SQL Server można pobrać w systemie lokalnym.

Można to zrobić za pomocą oprogramowania komercyjnego, aby bezpośrednio zapisać kopię zapasową pliku BAK na komputerze lokalnym, na przykład Ten plik utworzy kopię zapasową ze zdalnej bazy danych SQL na komputerze lokalnym.


Aby to zrobić, potrzebujesz uprawnień administratora.
LarryBud,

Dobrze. Konto (serwer Windows lub SQL) używane do zdalnego łączenia się z instancją serwera SQL wymaga uprawnień sysadmin do pobierania śmieci z kopii zapasowej i przechowywania ich na komputerze lokalnym. Wspomniane narzędzie FIDA Software szyfruje i kompresuje ruch sieciowy zi do serwera SQL podczas pobierania kopii zapasowych. Gdy już masz plik BAK na swoim komputerze, możesz go przywrócić w dowolnym miejscu.
Matthias

1

Jak powiedział Martin Smith, jeśli nie masz dostępu do maszyny lub systemu plików, będziesz musiał użyć narzędzi innych firm, takich jak Red Gate lub Adept, aby porównać systemy źródłowe i docelowe. Narzędzia Red Gate pozwolą ci kopiować obiekty i schematy ORAZ dane.


1

w ten sam sposób można wykonać kopię zapasową ze zdalnej instancji programu SQL Server na dysk lokalny, pod warunkiem spełnienia następującego warunku:

  1. Masz folder udostępniony na dysku lokalnym.
  2. udostępniony folder jest dostępny z poziomu SQL Server.

Teraz, określając polecenie kopii zapasowej, użyj ścieżki folderu współdzielonego, określając opcję dysku.


A czym różni się twoja odpowiedź od @Vivek?
Martin Schröder

1

po prostu spróbuj tego:

1) Udostępnij folder z pełnym uprawnieniem na swoim komputerze

2) na serwerze SQL: panel sterowania -> narzędzia administracyjne -> usługi -> kliknij prawym przyciskiem myszy wszystkie usługi SQL

na karcie logowania powinien zacząć się od administratora domeny

3) w kreatorze konserwacji serwera SQL umieść lokalizację kopii zapasowej i folder (\ twoja_komputera \ sharedfoldernam)

W naszej firmie wykonałem zdalną kopię zapasową na 8 serwerach SQL Server 2008


1

Jestem zdziwiony, że nikt nie wspomniał o skryptach tworzenia kopii zapasowych oferowanych przez Ola Hallengren których absolutnie nie pozwalają na tworzenie kopii zapasowych DB ze zdalnego serwera do ścieżki UNC w sieci za darmo (Jestem faktycznie używając go jako typ I aby wykonać kopię zapasową bazy danych z serwera deweloperskiego, do którego nie mam dostępu zdalnego poza SSMS, do udziału na moim komputerze deweloperskim). Jest to dostępne od 2008 roku i działa na SQL Server 2005 do 2014 roku.

Musisz upewnić się, że skonfigurowany udział ma wystarczający dostęp: zazwyczaj pozwalam na pełny odczyt / zapis grupie AD „Wszyscy” na czas tworzenia kopii zapasowej, ponieważ jestem zbyt leniwy, aby wymyślić coś bardziej restrykcyjnego, ale to osobisty wybór.

Jest dobrze używany, dobrze udokumentowany i bardzo elastyczny. Zwykle umieszczam procs i tabelę rejestrowania w ich własnej małej bazie danych narzędzi, a następnie odpalam. Pod warunkiem, że wszystko jest w domenie AD i nie jest zdalne w tym sensie, że znajduje się na kolokowanym serwerze lub czymś podobnym, działa to bardzo dobrze.

Przepraszam za dodanie do bardzo starego wątku, ale natknąłem się na to, szukając czegoś innego i uznałem, że jest to wartościowy dodatek dla każdego, kto szuka tego tematu.


1

W przypadku 2019 r. Polecam użycie mssql-scripter, jeśli chcesz mieć rzeczywistą lokalną kopię zapasową. Tak, to skrypty, ale możesz je dostosować, aby zawierały wszystko, co chcesz, w tym wszystkie dane. Napisałem skrypt bash, aby codziennie wykonywać automatyczne kopie zapasowe na maszynie z systemem Linux. Sprawdź moją treść:

https://gist.github.com/tjmoses/45ee6b3046be280c9daa23b0f610f407

0

Mógłbym to zrobić raz ... Aby to zrobić, musisz mieć udział otwarty na zdalnym serwerze. możesz bezpośrednio umieścić kopię zapasową w samym udziale, niż w domyślnej lokalizacji ...

Zwykle administrator bierze kopię zapasową i udostępnia ją nam w folderze udostępnionym. Próbowałem, czy to zadziała, jeśli umieściłem tam kopię zapasową. Zadziałało.


0

Jeśli korzystasz z opcji Generuj skrypty w SSMS, kliknij przycisk Zaawansowane. W opcji „Generuj skrypty dla obiektów zależnych” kliknij Prawda. Kliknięcie tego powoduje, że wszelkie zależności każdego obiektu również zostaną zapisane w odpowiedniej kolejności.


Prawda jest wartością domyślną w SSMS 17.8.1.
Chris Catignani,

0

Niektóre programy do tworzenia kopii zapasowych innych firm umożliwiają ustawienie przesyłania plików z określonymi uprawnieniami sieci. Jest to bardzo przydatne, gdy usługa SQL Server działa na ograniczonym koncie i nie ma wystarczających uprawnień sieciowych. Spróbuj użyć EMS SQL Backup, który rozwiązuje to zadanie.


0

W tym celu używam narzędzi Redgate Backup Pro 7 . możesz utworzyć kopię lustrzaną z pliku kopii zapasowej w kafelku w innej lokalizacji. i może automatycznie skopiować plik kopii zapasowej po utworzeniu w sieci i pamięci hosta.


0

Utwórz lokalny folder współdzielony z uprawnieniami „wszyscy” do odczytu / zapisu

Połącz się z docelową bazą danych, rozpocznij tworzenie kopii zapasowej i wskaż udział, jak pokazano poniżej

\ mymachine \ shared_folder \ mybackup.bak

(Wypróbowany w środowisku domeny Windows)


0

Wiem, że to starszy post, ale dla tego, co jest warte, odkryłem, że „najprostszym” rozwiązaniem jest kliknięcie bazy danych prawym przyciskiem myszy i wybranie „Zadania” -> „Eksportuj warstwę danych”. Możliwe, że ta opcja jest dostępna tylko dlatego, że serwer jest hostowany na platformie Azure (z tego, co pamiętam, pracowałem z platformą Azure w przeszłości, format .bacpac był tam dość powszechny).

Po wykonaniu tej czynności możesz kliknąć prawym przyciskiem myszy listę „Bazy danych” na lokalnym serwerze i użyć aplikacji „Importuj warstwę danych”, aby pobrać dane na komputer lokalny za pomocą pliku .bacpac.

Pamiętaj, że eksport może zająć dużo czasu. Minęło około dwóch godzin, zanim mój skończył eksportować. Jednak import części jest znacznie szybszy.


-1

Jeśli jesteś w sieci lokalnej, możesz udostępnić folder na komputerze lokalnym i użyć go jako folderu docelowego kopii zapasowej.

Przykład:

  • Folder lokalny:

    C:\MySharedFolder -> URL: \\MyMachine\MySharedFolder

  • Zdalny serwer SQL:

    Select your database -> Tasks -> Back Up -> Destination -> Add -> Apply '\\MyMachine\MySharedFolder'

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.