Sql-server – How to export/backup data from a SQL table, add a nullable column to the table and import/restore the data

backupsql server

I want to do the following…

  1. Backup or export data from my current database tables
  2. Redeploy (drop and create) my database with the same tables with new nullable columns
  3. Restore or import said data back into the database.

Is that possible using MSSQL? If so, how?

Best Answer

From the command prompt:

bcp DBName.dbo.TableName OUT c:\TableName.bcp -N -T -Smydbserver\instance

Then, drop and re-create your tables (I don't think there's any simple way of doing this).

To get your data back,

bcp DBName.dbo.TableName IN c:\TableName.bcp -N -T -Smydbserver\instance

You will need to repeat this for each table. It will give you a binary copy of the data, which is then re-inserted. Just as long as each field is the same length as the old one, you shouldn't have any troubles re-inserting the data on a different schema.

Related Topic