BCP

Po tą tajemniczą nazwą skrywa się narzędzie Bulk Copy Program utility (w skrócie BCP). Służy ono do kopiowania danych pomiędzy instancją bazy danych SQL Server i płaskimi plikami. Programu można używać z poziomu SQL przy pomocy funkcji CMDSHELL lub z poziomu windowsowej linii poleceń CMD.

Więcej o tym narzędziu można znaleźć na stronie Microsoftu
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

Ja dziś pokaże jak przy pomocy tego programu można w prosty sposób wykonać backup pojedynczej tabeli.

Na początek musimy mieć włączoną w naszej instancji funkcję XP_CMDSHELL. Jeśli nie mamy włączonej to wywołanie BCP zwróci błąd:

Msg 15281, Level 16, State 1, Procedure master..xp_cmdshell, Line 1 [Batch Start Line 0]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

Uruchamiamy XP_CMDSHELL

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Jeśli już ją uruchomiliśmy możemy przystąpić do tworzenia kopii naszej tabeli. W naszym przykładzie będzie to tabela PERSON z naszej bazy_testowej

USE [baza_testowa]
GO
SELECT [imie],[nazwisko],[wiek] FROM [dbo].[person]
GO

imie       nazwisko             wiek
---------- -------------------- -----------
Jan        Nowak                65
Julia      Pawlak               14
Adam       Kowalski             51
Katarzyna  Adamska              42
Anna       Duda                 66

(5 rows affected)

Uruchamiamy BCP z query SQL

DECLARE
@table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = '[baza_testowa].[dbo].[person]'
SET @file = 'G:\SQLBackup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '.bcp'
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

gdzie @table to nasza tabela której chcemy zrobić kopię.

output
-----------------------------------------------------------------
NULL
Starting copy...
NULL
5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (5000.00 rows per sec.)
NULL

(7 rows affected)

Na dysku w folderze G:\SQLBACKUP utworzył się plik [baza_testowa].[dbo].[person]_20190220.bcp. Nie powinno się używać dysku C gdyż Windows ma problemy z uprawnieniami do plików na tym dysku (trzeba czasami mieć prawa administratora do ich odczytu)

Możemy to samo polecenie wykonać prościej z linii poleceń.

bcp [baza_testowa].[dbo].[person] OUT G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220.bcp -c -T

W powyższym poleceniu wynik działania jest wyświetlony na ekranie. Można go też uruchomić w trybie cichym gdzie zarówno występujące błędy jak i wynik działania są zapisywane do plików tekstowych Error_out.log i Output_out.log

bcp [baza_testowa].[dbo].[person] OUT G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220_2.bcp -m 1 -n -e G:\SQLBackup\Error_out.log -o G:\SQLBackup\Output_out.log -S -T

Zawartość pliku Output_out.log

Starting copy...

5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 62     Average : (80.65 rows per sec.)

Jeśli chcemy wykorzystać uwierzytelnianie w bazie danych wydajemy polecenie

bcp [baza_testowa].[dbo].[person] out G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220_3.bcp -c -U -S

Pora odzyskać tabelę z naszego backupu. Jako że polecenie BCP spowoduje odczyt danych z pliku i zapis ich do tabeli aby odzyskać tabelę najpierw należy ją wyczyścić z danych

TRUNCATE TABLE [baza_testowa].[dbo].[person_bcp]

Commands completed successfully.

Sprawdzamy zawartość tabeli

SELECT [imie],[nazwisko],[wiek]
FROM [baza_testowa].[dbo].[person]

imie       nazwisko             wiek
---------- -------------------- -----------

(0 rows affected)

Tabela jest wyczyszczona. Następnie wczytujemy dane z pliku (z linii poleceń)

bcp [baza_testowa].[dbo].[person] IN G:\SQLBackup\[baza_testowa].[dbo].[person]_20190220.bcp -c -T

lub z query w Management Studio

DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = '[baza_testowa].[dbo].[person]'
SET @file = 'G:\SQLBackup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '.bcp'
SET @cmd = 'bcp ' + @table + ' in ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

output
-------------------------------------------------------------------
NULL
Starting copy...
NULL
5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (5000.00 rows per sec.)
NULL

(7 rows affected)

Sprawdzamy naszą tabelę

SELECT [imie],[nazwisko],[wiek]
FROM [baza_testowa].[dbo].[person]

imie       nazwisko             wiek
---------- -------------------- -----------
Jan        Nowak                65
Julia      Pawlak               14
Adam       Kowalski             51
Katarzyna  Adamska              42
Anna       Duda                 66

(5 rows affected)

Tabela odzyskana

Za pomocą BCP możemy wykonać backup pojedynczej kolumny

bcp "SELECT nazwisko FROM [baza_testowa].[dbo].[person] WITH (NOLOCK)" queryout g:\BCP\nazwisko_[baza_testowa].[dbo].[person].bcp -c -T

pojedynczych wierszy

bcp "SELECT * from [baza_testowa].[dbo].[person] WHERE imie = 'Adam'" queryout G:\BCP\Adam_[baza_testowa].[dbo].[person].bcp -d WideWorldImporters -c -T

lub zbackupować wynik zapytania

bcp "SELECT imie,nazwisko,wiek FROM [baza_testowa].[dbo].[person] ORDER BY wiek" queryout G:\BCP\People.txt -t, -c -T

Możemy też zdefiniować typ pliku backupu

REM non-XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\BCP\StockItemTransactions_c.fmt -c -T

REM non-XML native format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\BCP\StockItemTransactions_n.fmt -n -T

REM XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\BCP\StockItemTransactions_c.xml -x -c -T

Ten wpis został opublikowany w kategorii Microsoft SQL. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz