Move…That…Database
Situation: Upgraded servers, so we needed to move database tables, views and stored procedures from a SQL Server 2005 machine to a SQL Server 2008 machine. We wanted to go through each table, view and stored procedure one by one in order to only move objects that are still in use. We needed a way to do that as accurately and quickly as possible.
Solution: We made use of the SQL Server Script Wizard. The Script Wizard generated the CREATE scripts for each object. We were able to either cut/paste and run the scripts we needed on the new server, or delete the scripts if we didn’t need them. This made the process seamless and straight-forward.
Details:
Accessing the Script Wizard
In SQL Server Management Studio right-click on a database. Go to Tasks > Generate Scripts… This will open up a wizard dialog box.
Using the Script Wizard
The first screen allows you to select the database you want to use. On this screen you can also fast-track to the finish by checking “Script all objects in the selected database”. This will create scripts for all Roles, Users, Schemas, Tables, Views and Stored Procedures. We won’t check that in this article, but that useful option is there if that is what you are trying to accomplish.
Choose Script Options
After you select your database and click Next you will come to an Options screen. We were able to use the default settings here. But there are some useful options to note.
- Generate scripts for dependent objects(Default: False) – Use this to ensure dependent objects are also scripted for the objects that you select
- Script Create(Default: True) – Cornerstone of this wizard. This enables creation of scripts for the objects you select.
- Script Logins(Default: False) – Generates logins for the selected database
- Script Object-Level Permissions(Default: False) – ***This is an important option*** Set this to True in order to generate the permission scripts (GRANT EXECUTE ON) if you are generating stored procedure scripts.
- Script Data(Default: False) – Set to True if you want to script out INSERT statements for all data in selected tables.
- Script Foreign Keys(Default: True) – Scripts foreign keys for tables selected
- Script Primary Keys(Default: True) – Scripts primary keys for tables selected
- Script Triggers(Default: False) – Scripts any triggers that are related to tables selected
Choose Object Types
This is where you select what object types you want to generate scripts for (Database roles, Schema, Stored procedures, Tables, Users, Views).
Following this you will be taken through screens for each object type you selected. Here is where you select the specific objects whose scripts will be generated.
Output Option
Here you can either Script to a file, Script to Clipboard or Script to a New Query Window. We scripted to a query window and that worked great.
Finished
Go to Finish and the wizard will generate the scripts that you selected.
Notes
The constraints and permission assignments are all located in the last section of the generated scripts. This is to ensure that the objects have been created before trying to assign permissions to them.
MSDN Article: How to: Generate a Script
In Conclusion
This wizard is a powerful, yet simple tool that makes data migration more manageable. Trying to do this task object by object would be very cumbersome and impractical.
If you are in need of any advice or have a data migration project that you need help with please feel free to contact us here at Force 5.
