Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
add comment

You might find that you don't have to drop and recreate your table to get the nullable columns. If the column names are the same, just use ALTER TABLE:

ALTER TABLE Table1 ALTER COLUMN MyCol1 nvarchar(50) null

This changes the column type to allow nulls where previously it was the same column name and data type but was "not null". See http://msdn.microsoft.com/en-us/library/ms190273.aspx for more ALTER TABLE syntax.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.