Sql-server – bcp database migration

bcpmigrationsql server

I want one server to migrate the database to another server. On another server database already exists, then ist all the tables, but they are empty and I want to fill. For the filling, I want to use bcp – database migration. In command line, I wrote the following:

bcp [dbo].[aspnet_Profile] in "c:\SQLAzureMW\BCPData\dbo.aspnet_Profile.dat" -E -n -b 10000 -a 16384 -S servername.database.windows.net -U username@servername -P "password" -d databasename!

The name of the user and the server name passvord written correctly.

When I run I get this error:
enter image description here
error = Packet size change not supported by server, default used.

Then I fixed packet size to the size of a default. I removed the "-a".

bcp [dbo].[aspnet_Profile] in "c:\SQLAzureMW\BCPData\dbo.aspnet_Profile.dat" -E -n -b 10000 -S servername.database.windows.net -U username@servername -P "password" -d databasename!

Then I got the following error:
enter image description here

Who can tell what I should do, or is there a good way to move the database?
I also used the SQL Azure Migration Wizard,but it don't help me.

Best Answer

I found the reason that did not work, and really was not password correct because I used the password to these characters who divided password to pieces, and of that reason but it was not correct. I used to password ",", "$" and other characters.

Related Solutions

Sql-server – Optimising BCP performance for BLOB data

This is not a direct answer to your question, but there are some articles that you would benefit from reading them (in case you didn't find them first :-) ). They are about loading lots of data using bcp/bulk copy. I've read them all and I didn't find anything detailed regarding KILOBYTES_PER_BATCH, they're all using ROWS_PER_BATCH, but I'm sure you will find other useful information.

Load 1TB in less than 1 hour (from SQL CAT team) - list of advices from here (quote):
Run as many load processes as you have available CPUs. If you have 32 CPUs, run 32 parallel loads. If you have 8 CPUs, run 8 parallel loads.
If you have control over the creation of your input files, make them of a size that is evenly divisible by the number of load threads you want to run in parallel. Also make sure all records belong to one partition if you want to use the switch partition strategy.
Use BULK insert instead of BCP if you are running the process on the SQL Server machine.
Use table partitioning to gain another 8-10%, but only if your input files are GUARANTEED to match your partitioning function, meaning that all records in one file must be in the same partition.
Use TABLOCK to avoid row at a time locking.
Use ROWS PER BATCH = 2500, or something near this if you are importing multiple streams into one table.
Top 10 Best Practices for Building a Large Scale Relational Data Warehouse (from SQL CAT team) - advices (quote):
Use SIMPLE or BULK LOGGED recovery model during the initial data load.
Create the partitioned fact table with the Clustered index.
Create non-indexed staging tables for each partition, and separate source data files for populating each partition.
Populate the staging tables in parallel (Use multiple BULK INSERT, BCP or SSIS tasks)
Build a clustered index on each staging table, then create appropriate CHECK constraints.
SWITCH all partitions into the partitioned table.
Build non-clustered indexes on the partitioned table.
The Data Loading Performance Guide (from SQL CAT team)
Loading Bulk Data into a Partitioned Table - SQL Server Best Practices Article (Technet article)
SQL Server 2000 Incremental Bulk Load Case Study (Technet article)
Lessons Learned and Findings from a Large Fast-Track POC (from SQL CAT team)
Performance Tuning Tips for SQL Server BCP (By Brad McGehee)
Performance Impact: Finding the Most Optimal Batch Size (By Linchi Shea)

and the obvious MSDN references:

In my personal experience, I succeeded to make a fast data load using parallel load and testing with several batch sizes. I guess that only personal testing will suit you. Hopefully you will find some good advices in the references.

Sql-server – SELECT Data Set Using Stored Procedure with BCP Error

You have CR\LF in shell invocation, a no-no. Make it a single line:

DECLARE @SQL VARCHAR(8000); 
SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" '+ 
               'queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_20121120.txt" '+
               '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; 
EXEC xp_cmdshell @SQL;
GO

Best Answer

Related Solutions

Sql-server – Optimising BCP performance for BLOB data

Sql-server – SELECT Data Set Using Stored Procedure with BCP Error

Related Question