Timeout expired with SqlBulkCopy

I'm using SqlBulkCopy to restore tables from xml backups. One of the table backup is ~200MB large and has a lot of records.

I'm having error:

Timeout expired.
The timeout period elapsed prior to c开发者_StackOverflow社区ompletion of the operation or the server
is not responding.

You probably need to increase the timeout. Try increasing the value of sqlBulkCopy.BulkCopyTimeout from the default which is 30 seconds.

There are two ways to fix this error:

• Increase Timeout by default it is 30 second and 0 means infinite.

     sqlBulkCopy.BulkCopyTimeout = {time in seconds}
  

• Decrease BatchSize by default it try to insert all rows in one batch

      sqlBulkCopy.BatchSize  = {no of rows you want to insert at once}
  

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.batchsize.aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.bulkcopytimeout.aspx

Change the CommandTimeout property of your SqlCommand object.

The Connect Timeout attribute of a connection string determines how long a SqlConnection Object runs before it stops attempting to connect to a server.

Also change the SqlBulkCopy.BulkCopyTimeout Property.