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
