0

I am trying to migrate specific tables entire dataset from my DEV machine to our staging machine. (Has all the basic starting data for this feature).

Essentially what I want to do is akin to the backup/restore operation for databases, but I only want to do this for a select couple of tables in my database.

Is this possible? I've searched long and hard for MSSQL -> MSSQL Table Migration or export/import and I've come up with not a lot.

Improve this question
1

There is no native object level restore. However Quest Software, and RedGate both have products that can do object level restore. (I know that Quest does, I'm like 99% sure about RedGate.)

Improve this answer
  • Ah, there's some 3rd party software to do it. I'll look into that. – Aren B Feb 18 '11 at 18:06
1

Generally what you will have to do is generate scripts for the objects using Management Studio (or Enterprise Manager/Query Analyzer for 2000 and earlier), run these to create the objects on the staging server, and then use the Import/Export Wizard in Integration Services (or DTS for 2000 and earlier) to actually transfer the data to the destination server.

You could probably also use on-demand snapshot replication to push the objects to the target server, though making schema changes after that would require dropping the publication, or at the very least removing the objects from it.

Improve this answer
  • Seems like a pretty interesting feature gap. I know it's not what we do often in production, but in development I find it frequent to copy selective data around. – Aren B Feb 18 '11 at 1:09
  • I'm guessing their line of thinking is that if you're doing backup/restore, you want full consistency for your database and/or filegroup, and you won't get that with a single table backup. Situations involving testing are pretty easily served by starting and rolling back a transaction, a quick SELECT INTO to copy the table, or using ETL like SSIS to migrate the data elsewhere. – db2 Feb 21 '11 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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