My company purchased part of another company. Now they have a large SQL Server 20TB database that is supposed to be moved to our data center. The problem is that only part of the database is being moved because the rest was not part of the merger.

For instance, part numbers and stuff are moving but only parts that are part of the merger deal. The rest is still considered the other companies property.

Is this possible?

I am a new Jr DBA and I mostly focus on Oracle not SQL Server.

  • do you have a maintenance window? If so is it a long one? Does the data need to be kept in sync afterwards or it's a 1 time dump and forget it? – Ali Razeghi Sep 29 '16 at 16:36
  • One time dump. The data will be moved over somehow and put in a new database in our datacenter. This Database will be used by the employees that came over with the merge. – JrDBAAustinTX Sep 29 '16 at 17:03
  • How much data are we talking about? Snapshot replication or a backup/restore and delete might work. I'd say probably snapshot but then you'd need to install replication on the server first if it isn't already. So if that's the case, a backup/restore might be better. If it isn't a ton of data data compare is good. – Ali Razeghi Sep 29 '16 at 17:35
  • I hopefully will find that out in about 1 hour at the meeting. – JrDBAAustinTX Sep 29 '16 at 18:18
  • How would you solve it in Oracle? We might be able to help you with the technical transition to SQL Server, but we don't know your business requirements. Partial data import is certainly possible in SQL Server. – Tom V Sep 29 '16 at 18:24

If the objects you want to move are part of a separate filegroup, then you can do a filegroup backup and restore. This is a little tricky - with SQL Server, you can do a piecemeal restore of the primary filegroup, plus other filegroups, and the objects are then online. However, this won't work if the database wasn't designed for this from the start (by separating objects into filegroups.)

If the objects are relatively small, and if all objects are in a filegroup other than the primary, AND if you were allowed to make changes to the database, then you could create a new filegroup, move the objects into it, and then restore just that filegroup. However, you still have to restore the primary filegroup first, so this only works if there's not much in the primary filegroup.

If those aren't the case, you could set up a new database in the other company's data center, and copy the objects in that you'd like to transfer over. You could do this with simple insert statements, or by setting up replication into the new database.

None of what I've said above is easy - but hopefully it plants a few seeds on other ways you could pull it off.

  • Depending on the size of the data BCP out to SQL Native files, zip the files, then BCP in to a new database in your environment may work also. (Tables will have to be scripted out & sent along with the BCP files to be created in the new DB.) – Kenneth Fisher Sep 29 '16 at 17:09
  • It doesn't sound like an object level operation, more like some records of all objects. My guess is this will require some extract creation and loading into new objects in a new database in the new datacenter. – Steve Mangiameli Sep 29 '16 at 20:46

Basically no, you will need to get the schema scripted out and create a new database from it. You will then need to pull our the data that your company now owns and transfer it to your new databases, this can be done with SSIS, BCP and many other methods.

P.S. I'm sure someone will mention filegroup backups and partial restores etc but it's incredibly unlikely the databases has been setup with that in mind in this instance.

  • Thanks for the quick comment. I have a meeting today with some folks so I will now not sound like total a idiot. Not to mention them wanting us to come up with 20TB in VM's with space for backups, and how to move a 20TB if it comes something like that. – JrDBAAustinTX Sep 29 '16 at 15:56

Some options are DB Replication (Snapshot or Transactional) that works on the table level so you'd be replicating the tables themselves and not the others. Snapshot is easier and great for a 1 time push. Transactional Replication is good if it needs to stay in sync going fwd. You might not have replication installed on that machine so you can install it on a different machine if you don't want to modify the prod one, and setup the snapshots on there then copy them out.

I'm not sure how big the tables are but the new version of SQL Data compare by RedGate is faster and you might be able to pull it off that way if you don't want to mess with replication.

You could backup the whole DB, restore, drop the tables you don't need and shrink, then reindex. This will take a while and waste computer resources but could be the easiest and if the computer isn't doing anything else it isn't a big deal.

There's other options but that depends on your maintenance window, distance between servers, how to get data across, network reliability, etc.

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.