Task 16. BCP utility

Bulk Copy Program is a tool used for copying data between servers, especially when you don't have a Business Intelligence Development Studio installed on the server from where you want to took the data.
You can find more about other methods of doing this here.

The task was to copy the databases size records from one server to another one in order to process the data. To obtain the databases size from one server I used the following query:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
where database_id >4 --skip system databases
GO

or you can use:

with fs

as (
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

The steps are:
1. Create a .bat file to run the bcp command using one of the mentioned queries.
2. Go to D:\ and create a New Text Document named run_bcp.bat .
3.Open your .bat file using Notepad/Notepad++ and write down the following commands:

rem D:\bcp_outputQuery.csv
bcp "SELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files where database_id > 4" queryout "D:\bcp_outputQuery.csv" -c -t"," -r"\n" -S <<Server IP>> -T

4.Using a Command Prompt window go to your .bat file and run it using  .\run_bcp.bat.  It will start copying the data in your CSV file.
5. Copy the CSV file on your server and export data using an SSIS package ( from .csv file to a table)


If you can connect remotely to the server where the data needs to arrive then it's more easy. You create a login on the server where data comes from with full rights and then using SSIS you'll be able to connect to the server.