Πώς εξάγουμε / εισάγουμε ολόκληρη βάση δεδομένων με χρήση bulk copy (bcp) στον SQL Server
Πιστοποιήσεις:
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Linux - 1 Ιούλιος 2024
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
Πολύ συχνά θα χρειαστεί να μεταφέρουμε τις εγγραφές από πίνακες από ένα σύστημα σε ένα άλλο. Συνήθως αυτή τη δουλειά την κάνουμε μέσω wizard, υπάρχει όμως και ένα άλλο εργαλείο στον SQL Server το bulk copy program ή αλλιώς bcp.
Το εργαλείο bcp χωρίζεται σε δύο, το bcp out που εξάγει τα δεδομένα σε txt αρχεία που περιέχουν όλες τις εγγραφές* και bcp in που εισάγει txt αρχεία σε πίνακες της βάσης σε επιλεγμένο instance.
*υπάρχει και η δυνατότητα χρήσης της παραμέτρου queryout ώστε να γράψουμε query για να εξαχθούν επιλεγμένες μόνο εγγραφές.
Στο άρθρο θα δούμε ένα query που έχω φτιάξει που μεταφέρει όλους τους πίνακες που περιέχει η βάση. Μας ετοιμάζει τις εντολές σε δύο στήλες . Στη μία για να κάνουμε bcp out από το instance και στην άλλη για bcp in.
Τι πρέπει να προσέξουμε
Επειδή οι πίνακες θα εισάγονται με τυχαία σειρά για να είναι εφικτή η διαδικασία θα πρέπει να προσέξουμε τα foreign key constraint violations. Τα constraints αυτά είναι λογικοί κανόνες που συνδέουν δύο πίνακες μεταξύ τους, η δουλειά τους είναι να διασφαλίσουν ότι σε έναν πίνακα που το primary key του είναι foreign key σε έναν άλλον θα εξακολουθεί να υπάρχει η εγγραφή αυτή.
Σαν παράδειγμα μπορούμε να σκεφτούμε ότι δεν γίνεται να σβήσουμε εγγραφή από πίνακα με χώρες όσο υπάρχουν εγγραφές στον πίνακα διευθύνσεις που έχουν ως foreign key τη χώρα αυτή. Θα πρέπει πρώτα να σβήσουμε ή αλλάξουμε χώρα στις εγγραφές αυτές και μετά να θα μπορούμε να διαγράψουμε την χώρα αυτή.
Για να αποφύγουμε όλα αυτά τα μηνύματα λάθους μπορούμε είτε να κάνουμε disable το constraint στον συγκεκριμένο πίνακα:
ALTER TABLE db_test.dbo.customer NOCHECK CONSTRAINT MyConstraint
είτε να κάνουμε disable τα constraints σε ολόκληρη τη βάση χωρίς να ξεχάσουμε να τα ενεργοποιήσουμε μετά:
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"
Ο κώδικας
Το μόνο που χρειάζεται να κάνουμε τώρα είναι να εκτελέσουμε αυτό το script βάζοντας τις παραμέτρους του username, password για το instance και το path που θα εξαχθούν / εισαχθούν τα αρχεία:
declare @user varchar(50), @pass varchar(50), @pathout varchar(200), @pathin varchar(200) set @user = 'username' set @pass = 'password' set @pathout = 'c:\backups\' set @pathin = 'c:\backups\' SELECT 'bcp ' --bcp + QUOTENAME(DB_NAME())+ '.' + QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.' -- + QUOTENAME(name) + ' out ' +@pathout + REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' + REPLACE(name,' ','') + '.txt -S' + REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','') --server name +' -U'+@user+' -P'+@pass+' ' --username pass +'-n -t[cdel] -r[rdel]' , AS BCP_OUT, 'bcp ' --bcp + QUOTENAME(DB_NAME())+ '.' + QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.' + QUOTENAME(name) + ' in '+@pathin + REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' + REPLACE(name,' ','') + '.txt -S' + REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','') --server name +' -U'+@user+' -P'+@pass+' ' --username pass +'-w -n' +' -e'+@pathin +'logbcpin.txt' +' -t[cdel] -r[rdel]' , AS BCP_IN from sys.tables
Με την εκτέλεση του script θα δούμε δύο κολόνες. Η μια θα έχει το bcp out script για τον κάθε πίνακα που περιέχει η βάση προς εξαγωγή και η άλλη την αντίστοιχη διαδικασία για bcp in ώστε να εισαχθούν.
Για παράδειγμα δεν θα τρέξουμε όλη την στήλη και με τους τρεις πίνακες αλλά μόνο τον πρώτο. Το μόνο που έχουμε να κάνουμε είναι να κάνουμε paste το command σε ένα command prompt με elevated admin rights:
bcp [db_test].[dbo].[customer] out c:\backups\dbo_customer.txt -SSMATZOURANISLP\SQL19 -Usqladmin -Ppasssql -n -t[cdel] -r[rdel]
Σε αυτό το σημείο έχει δημιουργηθεί το αρχείο c:\backups\dbo_customer.txt με όλες τις εγγραφές του πίνακα. Σε περίπτωση που θα το εισάγαμε σε άλλον υπολογιστή θα έπρεπε να αντιγράφει το αρχείο στον αντίστοιχο φάκελο στο άλλο μηχάνημα.
Σε αυτό το παράδειγμα απλά θα το ξανά εισάγω στον ίδιο πίνακα αφού πρώτα αδειάσω όλες τις εγγραφές του:
truncate table db_test.dbo.customer
Τώρα με το bcp in command με τον ίδιο ακριβώς τρόπο θα ξανά εισάγουμε τις εγγραφές.
bcp [db_test].[dbo].[customer] in c:\backups\dbo_customer.txt -SSMATZOURANISLP\SQL19 -Usqladmin -Ppasssql -w -n -ec:\backups\logbcpin.txt -t[cdel] -r[rdel]
Έτοιμο!!!
Αφού τελειώσουμε την διαδικασία δεν ξεχνάμε να ξανά ενεργοποιήσουμε τα constraints:
EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"
