SQL Server - kopiuj procedury składowane z jednej bazy danych do drugiej


86

Jestem nowy w SQL i tym, co musiałem zrobić, było połączenie 2 baz danych .mdf w jedną. Zrobiłem to za pomocą Menedżera SQL Server 2008 - Zadania> Import / Eksport tabel. Tabele i widoki zostały pomyślnie skopiowane, ale w nowej bazie danych nie ma procedur składowanych. Czy istnieje jakiś sposób, aby to zrobić?


1
Jeśli chcesz skopiować je programowo, zacznij tutaj: stackoverflow.com/a/6124487/138938
Jon Crowell

Odpowiedzi:


137
  • Kliknij prawym przyciskiem myszy bazę danych
  • Zadania
  • Generuj skrypty
  • Wybierz obiekty, które chcesz skryptować
  • Skrypt do pliku
  • Uruchom wygenerowane skrypty w docelowej bazie danych

Cześć, dzięki za szybką odpowiedź. Czy możesz wyjaśnić, jak używać skryptu w docelowej bazie danych. Jestem nowy w tym.
Dąb

1
@BarryKaye A co, jeśli ma 30-40 procedur składowanych? Czy klikanie prawym przyciskiem nie byłoby trochę powolne?
rvphx

@Oak Otwórz wygenerowany plik skryptu w SQL Management Studio. Zmień połączenie na nową bazę danych. Zmień wiersz na samej górze pliku, w którym jest napisane „Użyj DatabaseName” do bazy danych i wykonaj.
Jaimal Chohan

Łał. Teraz pomyślałem, że byłem jedynym, któremu podobało się podejście oparte na GUI !!
rvphx

10
@RajivVarma - to zadanie wykonujesz raz dla bazy danych - nie dla każdego SP! Jeśli zaznaczysz górne pole wyboru obok „Procedury składowane”, zaznaczy je wszystkie razem - 1 kliknięcie.
Barry Kaye

19

Ten kod kopiuje wszystkie procedury składowane z bazy danych Master do docelowej bazy danych. Możesz skopiować tylko te procedury, które lubisz, filtrując zapytanie według nazwy procedury.

@sql jest zdefiniowane jako nvarchar (max), @Name jest docelową bazą danych

DECLARE c CURSOR FOR 
   SELECT Definition
   FROM [ResiDazeMaster].[sys].[procedures] p
   INNER JOIN [ResiDazeMaster].sys.sql_modules m ON p.object_id = m.object_id

OPEN c

FETCH NEXT FROM c INTO @sql

WHILE @@FETCH_STATUS = 0 
BEGIN
   SET @sql = REPLACE(@sql,'''','''''')
   SET @sql = 'USE [' + @Name + ']; EXEC(''' + @sql + ''')'

   EXEC(@sql)

   FETCH NEXT FROM c INTO @sql
END             

CLOSE c
DEALLOCATE c

Dzięki! ... W komentarzach, ale nie zadeklarowanych w kodzie są @sql& @Name:DECLARE @sql NVARCHAR(MAX); DECLARE @Name NVARCHAR(32);
datalifenyc

Czy istnieje sposób, aby zrobić to samo na różnych serwerach? Z serwera A do serwera B?
Rajaram1991

5

Spóźniona, ale zawiera więcej szczegółów, które mogą być przydatne…

Oto lista rzeczy, które możesz zrobić z zaletami i wadami

Generuj skrypty za pomocą SSMS

  • Plusy: niezwykle łatwy w użyciu i domyślnie obsługiwany
  • Wady: skrypty mogą nie mieć prawidłowej kolejności wykonywania i mogą wystąpić błędy, jeśli procedura składowana już istnieje w dodatkowej bazie danych. Upewnij się, że przejrzysz skrypt przed wykonaniem.

Narzędzia innych firm

  • Zalety: narzędzia takie jak ApexSQL Diff (tego używam, ale jest wiele innych, takich jak narzędzia Red Gate lub Dev Art) porównują dwie bazy danych jednym kliknięciem i generują skrypt, który można natychmiast wykonać
  • Wady: nie są one bezpłatne (większość dostawców ma jednak w pełni funkcjonalną wersję próbną)

Widoki systemowe

  • Zalety: Możesz łatwo sprawdzić, które procedury składowane istnieją na serwerze pomocniczym i wygenerować tylko te, których nie masz.
  • Wady: wymaga nieco większej znajomości języka SQL

Oto jak uzyskać listę wszystkich procedur w jakiejś bazie danych, które nie istnieją w innej bazie danych

select *
from DB1.sys.procedures P
where P.name not in 
 (select name from DB2.sys.procedures P2)

5

Pierwotnie znalazłem ten post, szukając rozwiązania do kopiowania procedur składowanych z mojej zdalnej produkcyjnej bazy danych do mojej lokalnej deweloperskiej bazy danych. Po pomyślnym zastosowaniu podejścia sugerowanego w tym wątku zdałem sobie sprawę, że stawałem się coraz bardziej leniwy (lub zaradny, w zależności od tego, co wolisz) i chciałem, aby zostało to zautomatyzowane. Natrafiłem na ten link , który okazał się bardzo pomocny (dziękuję vincpa) i rozszerzyłem go, w wyniku czego powstał następujący plik (schema_backup.ps1):

$server             = "servername"
$database           = "databaseName"
$output_path        = "D:\prod_schema_backup"
$login = "username"
$password = "password"

$schema             = "dbo"
$table_path         = "$output_path\table\"
$storedProcs_path   = "$output_path\stp\"
$views_path         = "$output_path\view\"
$udfs_path          = "$output_path\udf\"
$textCatalog_path   = "$output_path\fulltextcat\"
$udtts_path         = "$output_path\udtt\"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")  | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")  | out-null
$srvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$srvConn.ServerInstance = $server
$srvConn.LoginSecure = $false
$srvConn.Login = $login
$srvConn.Password = $password
$srv        = New-Object Microsoft.SqlServer.Management.SMO.Server($srvConn)
$db         = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$tbl        = New-Object ("Microsoft.SqlServer.Management.SMO.Table")
$scripter   = New-Object Microsoft.SqlServer.Management.SMO.Scripter($srvConn)

# Get the database and table objects
$db = $srv.Databases[$database]

$tbl            = $db.tables | Where-object { $_.schema -eq $schema  -and -not $_.IsSystemObject } 
$storedProcs    = $db.StoredProcedures | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } 
$views          = $db.Views | Where-object { $_.schema -eq $schema } 
$udfs           = $db.UserDefinedFunctions | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } 
$catlog         = $db.FullTextCatalogs
$udtts          = $db.UserDefinedTableTypes | Where-object { $_.schema -eq $schema } 

# Set scripter options to ensure only data is scripted
$scripter.Options.ScriptSchema  = $true;
$scripter.Options.ScriptData    = $false;

#Exclude GOs after every line
$scripter.Options.NoCommandTerminator   = $false;
$scripter.Options.ToFileOnly            = $true
$scripter.Options.AllowSystemObjects    = $false
$scripter.Options.Permissions           = $true
$scripter.Options.DriAllConstraints     = $true
$scripter.Options.SchemaQualify         = $true
$scripter.Options.AnsiFile              = $true

$scripter.Options.SchemaQualifyForeignKeysReferences = $true

$scripter.Options.Indexes               = $true
$scripter.Options.DriIndexes            = $true
$scripter.Options.DriClustered          = $true
$scripter.Options.DriNonClustered       = $true
$scripter.Options.NonClusteredIndexes   = $true
$scripter.Options.ClusteredIndexes      = $true
$scripter.Options.FullTextIndexes       = $true

$scripter.Options.EnforceScriptingOptions   = $true

function CopyObjectsToFiles($objects, $outDir) {
    #clear out before 
    Remove-Item $outDir* -Force -Recurse
    if (-not (Test-Path $outDir)) {
        [System.IO.Directory]::CreateDirectory($outDir)
    }   

    foreach ($o in $objects) { 

        if ($o -ne $null) {

            $schemaPrefix = ""

            if ($o.Schema -ne $null -and $o.Schema -ne "") {
                $schemaPrefix = $o.Schema + "."
            }

            #removed the next line so I can use the filename to drop the stored proc 
            #on the destination and recreate it
            #$scripter.Options.FileName = $outDir + $schemaPrefix + $o.Name + ".sql"
            $scripter.Options.FileName = $outDir + $schemaPrefix + $o.Name
            Write-Host "Writing " $scripter.Options.FileName
            $scripter.EnumScript($o)
        }
    }
}

# Output the scripts
CopyObjectsToFiles $tbl $table_path
CopyObjectsToFiles $storedProcs $storedProcs_path
CopyObjectsToFiles $views $views_path
CopyObjectsToFiles $catlog $textCatalog_path
CopyObjectsToFiles $udtts $udtts_path
CopyObjectsToFiles $udfs $udfs_path

Write-Host "Finished at" (Get-Date)
$srv.ConnectionContext.Disconnect()

Mam plik .bat, który to nazywa i jest wywoływany z Harmonogramu zadań. Po wywołaniu pliku Powershell mam:

for /f %f in ('dir /b d:\prod_schema_backup\stp\') do sqlcmd /S localhost /d dest_db /Q "DROP PROCEDURE %f"

Ta linia przejdzie przez katalog i porzuci procedury, które ma odtworzyć. Gdyby to nie było środowisko programistyczne, nie chciałbym programowo porzucać procedur w ten sposób. Następnie zmieniam nazwy wszystkich plików procedur składowanych na .sql:

powershell Dir d:\prod_schema_backup\stp\ | Rename-Item -NewName { $_.name + ".sql" }

A potem biegnij:

for /f %f in ('dir /b d:\prod_schema_backup\stp\') do sqlcmd /S localhost /d dest_db /E /i "%f".sql

I to iteruje przez wszystkie pliki .sql i odtwarza procedury składowane. Mam nadzieję, że jakakolwiek część tego okaże się komuś pomocna.


Podoba mi się to. Muszę napisać proces archiwizowania fragmentów bazy danych produkcji rok po roku. Nie chcę mieć zawieszonych plików SQL, które prawdopodobnie nie zostaną zaktualizowane w miarę rozwoju schematu, więc dostosowuję to, aby utworzyć pustą bazę danych na podstawie celu bez pośredniego kroku zapisywania plików na dysk (więcej posprzątać). Myślę, że jest to prawdopodobnie najlepsza i najbardziej przydatna odpowiedź na to pytanie, kudos sir!
Steve Pettifer

3

Możesz użyć funkcji „Generuj skrypty ...” programu SSMS, aby utworzyć skrypt, który chcesz przesłać. Kliknij prawym przyciskiem myszy źródłową bazę danych w programie SSMS, wybierz opcję „Generuj skrypty ...” i postępuj zgodnie z instrukcjami kreatora. Następnie uruchom wynikowy skrypt, który będzie teraz zawierał instrukcje tworzenia procedury składowanej.


3

posługiwać się

select * from sys.procedures

pokazać wszystkie swoje procedury;

sp_helptext @objname = 'Procedure_name'

aby uzyskać kod

i swoją kreatywność, aby stworzyć coś, co zapętli je wszystkie i wygeneruje kod eksportu :)


3

Możesz wygenerować skrypt z przechowywanych procesów, jak przedstawiono w innych odpowiedziach. Po wygenerowaniu skryptu można go użyć sqlcmddo wykonania na docelowej bazie danych, takiej jak

sqlcmd -S <server name> -U <user name> -d <DB name> -i <script file> -o <output log file> 

0

W Mgmt Studio kliknij prawym przyciskiem myszy oryginalną bazę danych, a następnie Zadania, a następnie Generuj skrypty ... - postępuj zgodnie z instrukcjami kreatora.


0

SELECT definicja + char (13) + 'GO' FROM MyDatabase.sys.sql_modules s INNER JOIN MyDatabase.sys.procedures p ON [s]. [Object_id] = [p]. [Object_id] WHERE p.name LIKE 'Coś% '"queryout" c: \ SP_scripts.sql -S MyInstance -T -t -w

pobierz sp i wykonaj go


To bardzo fajne rozwiązanie, ale 1) należy zwrócić uwagę, że potrzebny jest tekst lub plik wyjściowy (nie wyświetlaj wyników w siatce, bo stracisz znaki EOL) i 2) wydaje się, że jest limit 8k do wyświetlania tekstu w SQL Server Management Studio.
DAB

0

Inną opcją jest transfer procedur składowanych przy użyciu usług SQL Server Integration Services (SSIS) . Istnieje zadanie o nazwie Zadanie transferu obiektów SQL Server . Możesz użyć zadania, aby przenieść następujące elementy:

  • Tabele
  • Wyświetlenia
  • Procedury składowane
  • Funkcje zdefiniowane przez użytkownika
  • Domyślne
  • Typy danych zdefiniowane przez użytkownika
  • Funkcje partycji
  • Schematy partycji
  • Schematy
  • Zespoły
  • Agregaty zdefiniowane przez użytkownika
  • Typy zdefiniowane przez użytkownika
  • Kolekcja schematów XML

Jest to graficzny samouczek dotyczący zadania Transfer SQL Server Objects.

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.