Network transfer of SQL database

With the tool ‘Import and Export data’  you can copy the contents of a SQL database to another PC in a user friendly way. Here are some tips to make sure your security settings are correct, and the database is the same on the destination system.

Installed on PC:

• SQL Server 2008 Express R2

Network settings

• Enable TCP/IP as protocol for both SQL servers: open the SQL server configuration manager, select SQL server network configuration: protocols for SQLExpress. And enable the setting ‘TCP/IP”.

Security

• Enable remote access (on desitionation SQL server)

Database contents

The wizard for the import and export tool works pretty straighforward (if security settings are ok). However, there are some important transfer settings. You can find them under the button “Edit mapping”’.

• Enable ‘enable identity insert’. If you do not check this option, the identity (autoincrement functionality for values) is lost, and e.g. your INSERT sql commando’s do not work anymore, because your identity column is not allowed to be NULL. When you expected the column to be autoincremental, this propably did not give a value for that in your INSERT statement.