1
Vote

How to export sql data objects in .sql file using bcp?

description

Hi,

I am able to export the data using below command.

bcp "[learn].[dbo].[Account_]" out "c:\SQLAzureMW\BCPData\dbo.Account_.dat" -E -n -C RAW -S XXX.XXX.XXX.XXX -U "XXX" -P "XXXX"

Then I tried to import the same data in different database using below command.
bcp "[test].[dbo].[Account_]" in "c:\SQLAzureMW\BCPData\dbo.Account_.dat" -E -n -C RAW -S XXX.XXX.XXX.XXX -U "XXXX" -P "XXXX"

I am not able to import the data giving below error.

C:\Users\Administrator>bcp "test.dbo.Account_" in "c:\bcp\dbo.Account_.dat" -E -
n -C RAW -b 1000 -a 4096 -q -S XXX.XXX.XXX.XXX -U "XXX" -P "XXXX"
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object nam
e 'test.dbo.Account_'.

C:\Users\Administrator>

The issue is objects are not existed.

Please help me to know, how can i generate abc.sql (data object script) using bcp command.


Thanks in advance.

comments

ghuey wrote Jun 17, 2016 at 2:06 AM

Hi,

I don't believe that you can use BCP to create your table for you. Instead, what you should do is use SQLAzureMW (or tool of your choice) to generate the TSQL to create your schema. Then run that against your target server and then use BCP to upload your data. SQLAzureMW will do all of this for you without you having to worry about doing the BCP commands yourself.

Hope this helps,
George

sachinpatel wrote Jun 17, 2016 at 12:42 PM

Thanks for your prompt response.

It is really great tool. I used it for database migration from Local SQL server to AWS RDS server.
It's working fine.

Now my concern is with backup of database. As of now, I am talking snapshot of RDS volume for backup but suppose I want to take backup of specific database from linux environment and there isn't any windows system in my client's environment so I have only one option to use ODBC driver to access MS SQL Database from Linux system.

I installed ODBC driver and using linux script I am taking backup of SQL data from specific database locally.
Now, when I am trying to import the data then its giving "INVALID OBJECT" error because schema is not present against that table.

Is there any alternative to take schema backup?

Thanks in advance.