0

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?

Improve this question
2
  • Is it possible for you to use truncate table then after restoring backup you can truncate extra tables and eventually you would get free space(not immediately). Plus how can import export wizard miss data at least not happened with me
    – Shanky
    Sep 25, 2014 at 18:16
  • Thanks for your comment. Well the wizard didn't miss data. Some of the columns in my source tables were null and in the target they had data.
    – user3099771
    Sep 25, 2014 at 18:25

2 Answers 2

Reset to default
3

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.

Improve this answer
2
  • +1 crucial point though about the caveats: **freed a lot of space that we know ***we'll never ever need again*****
    – Aaron Bertrand
    Sep 25, 2014 at 18:39
  • @AaronBertrand Agreed 100% and so I linked that excellent answer by Mike Walsh.
    – Kin Shah
    Sep 25, 2014 at 18:45
1

I haven't tried this yet, but I hear Redgate SQL Packager could help with the deployment.

SQL Packager - package database updates as an .exe file

Installing, updating, or distributing your database can be done quickly and easily. SQL Packager also makes it easy to archive your database, and is an excellent solution for making a backup of your database when you don't have SQL Server administration rights.

  • Script and compress schema and data accurately and quickly into a deliverable .exe file
  • Easily roll out database updates across the client base
  • Package any pre-existing SQL script as a .exe, or launch as a C# project Simplify deployments and updates
  • Supports all objects in all versions of SQL Server, including SQL Server 2012
Improve this answer
3
  • 2
    This will cost some extra $$$$ as its a 3rd party software. I will think to go with a 3rd party solution only if I have to do this on a frequent basis. But again, if I am able to do it naively (without using 3rd party tools), I would choose that route.
    – Kin Shah
    Sep 25, 2014 at 18:44
  • Aye, for sure if you can do it efficiently native. But, you may find the cost and time savings over time using a 3rd party tool is worth the cost. If this is a one-time thing or multiple-time thing that's faster than the tool, then it makes sense to do itself. But, just a suggestion. :)
    – Glen Swan
    Sep 25, 2014 at 19:56
  • I've used Redgate software in the past for backups and syncing table structures and it was absolutely worth the money. For a one time backup and restore it't too expensive, but for something that you do on a regular basis it's a real time (and headache) saver!
    – SPRBRN
    Feb 12, 2015 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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