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?