Sql-server – Best Way to Migrate 100 tables To New Database

database-designsql server

I need to migrate 100 out of 150 tables (data and structure) and everything else (sprocs, views etc.) to a new empty SQL Server database.

I have been using the Import/Export wizard and it has been causing me headaches. First, I scripted out and created all the tables (because the import wizard will not create constraints) then I imported the data. Some of the data did not match up so I used Toad data sync to sync it from the source. I have pretty much got everything fixed but there has to be a better way to do this and here are my thought. I would love to get some feedback on the pros and cons of each method:

1.Restore Backup of database to target database and delete the tables I do not need

This would be the fastest method but I wonder will SQL Server utilize the space from the deleted tables? Or is there a way to make it?

2.Restore Backup to Target database and create a new file group. Then, move all the table clustered indexes over to the new file group and delete the old one.

Are there any issues with doing this? If I just moved the sys and users tables to the new file would that be enough? Anything else need to be copied over?

Best Answer

I would recommend backup and restore method.

If you are on sql server 2008R2 and up, then standard edition will allow you to compress the backup, so that you can transfer it to the new server relatively faster than using an uncompressed backup.

Once you restore the backup to the new server, just drop the tables / SPs / Views / Triggers, etc that you don't need.

After you drop the tables, just do a one time shrink (with Some Caveats) of your database to release space back to the OS. Note for when shrinking use DBCC SHRINKFILE (not DBCC SHRINKDATABASE)

Note: It would also be a good idea to "truncate" the tables (as @shanky pointed out) - if the tables are not having any FK relationship to other tables.

Related Question