June 26, 2012 at 5:43 am
Hi
I am needing to export some data from a backup into our live DB due to some issues. Currently I am thinking the best thing would be to BCP export the data and import it. However I am unsure on the description of the following:
-n (native format): The bcp utility retains the database native data types when bulk copying the data to the data file. Microsoft recommends that you use this format to bulk copy data between instances of SQL Server. However, you should use this format option only when the data file should not support extended or double-byte character set (DBCS) characters.
I have created a format file based on the table I will be importing to and it has produced it contains the following datatypes:
SQLTINYINT
SQLCHAR
SQLSMALLINT
SQLMONEY
Am I able to export using the native format without any issues?
Also just as a side note I believe I will need to truncate the table I am exporting into and override the primary key in the command, and am also thinking about scripting off the indexes and dropping those to.
Comments welcome
Thanks
June 26, 2012 at 7:39 am
Kwisatz78 (6/26/2012)
HiI am needing to export some data from a backup into our live DB due to some issues. Currently I am thinking the best thing would be to BCP export the data and import it. However I am unsure on the description of the following:
-n (native format): The bcp utility retains the database native data types when bulk copying the data to the data file. Microsoft recommends that you use this format to bulk copy data between instances of SQL Server. However, you should use this format option only when the data file should not support extended or double-byte character set (DBCS) characters.
I have created a format file based on the table I will be importing to and it has produced it contains the following datatypes:
SQLTINYINT
SQLCHAR
SQLSMALLINT
SQLMONEY
Am I able to export using the native format without any issues?
Are you are storing DBCS characters in your SQLCHAR column? If you are using a DBCS you would know, so if you are not sure then do not worry about it. Using a native format with bcp is a bit quicker than using a character format ( -c ) but can only be done when transferring data between two SQL Servers, which is your scenario. You should be fine using native. Also, since the two schemas (source and destination) are the same then you should not need a format file. You can simply provide a delimiter that is not likely to appear in your data.
Out from your backup DB:
bcp.exe BackupDatabaseName.dbo.TableName out C:\dbo.TableName.dat -n -T -S .\SQL2008R2 -t "!~~~!" -r "!###!"
And into your live DB:
bcp.exe LiveDatabaseName.dbo.TableName in C:\dbo.TableName.dat -n -T -S .\SQL2008R2 -t "!~~~!" -r "!###!"
Kwisatz78 (6/26/2012)
Also just as a side note I believe I will need to truncate the table I am exporting into and override the primary key in the command, and am also thinking about scripting off the indexes and dropping those to.
What did you have in mind when you said override the primary key in the command?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply