SQL Server: Importing/Exporting a database
This guide is divided into two different methods of migration:
Migration via script: This process will convert all of your original data into a script that afterwards needs to be executed in the target database to re-create the original database data on the target database.
Migration via Import and Export Wizard: Ideal for transferring data between live databases. Specially useful for migrations where both databases are running on different providers, and also for cases where the migration via scripts wasn't successful.
Instalation of the SQL Server Management Studio, is mandatory for this procedures.
Migration via script
Open up Management Studio and connect to your source database. Click here if you need help connecting to your SQL Server database hosted at Umbler.
After selecting the database right click and choose Tasks - Generate Scripts.
You will be redirected to the script generation wizard. Click on Next.
Now select the resources to be exported and hit Next.
Select the destination of the script file in your local machine and click on Advanced to choose the type of data to be exported.
Next step is to check all the configurations of the script, if everything is correct select Next.
A file similar to the image should be presented:
To import the script created previously fire up Management Studio, connect to the target database and open the .sql file created using the first steps and click on !Execute to import the tables & data.
Done! the SQL script will run and the database re-created at Umbler :)
Migration via Import and Export Wizard
The SQL Server Import and Export Wizard lets you migrate online databases. It normally comes as a SQL Server Management Studio component.
Exporting Primary Keys
To export the Primary Keys, connect to your source database via SQL Server Management Studio and execute the following query:
SELECT DISTINCT 'ALTER TABLE ' + colinfo.table_name + ' WITH CHECK ADD CONSTRAINT [' + colinfo.constraint_name + '] PRIMARY KEY CLUSTERED (' + (SELECT Stuff((SELECT ',' + column_name FROM information_schema.key_column_usage WHERE constraint_name = colinfo.constraint_name FOR xml path('')), 1, 1, '')) + ')' FROM information_schema.key_column_usage colinfo INNER JOIN information_schema.table_constraints tblC ON tblC.constraint_name = colinfo.constraint_name AND tblC.constraint_schema = colinfo.constraint_schema WHERE tblC.constraint_type = 'PRIMARY KEY'
This query will save the scripts that will help you re-create the Primary Keys on the target database. Once all data has been migrated using the wizard, just run this script and the PK's will be re-created :)
Exporting the Foreign Keys
This process is analog to the previous one. Connect to your source database via SQL Server Management Studio and execute the following query:
SELECT DISTINCT 'ALTER TABLE ' + ReferencingConstraint.table_name + ' ADD CONSTRAINT [' + FK.constraint_name + '] FOREIGN KEY (' + (SELECT Stuff((SELECT ',' + column_name FROM information_schema.key_column_usage colInfo WHERE colInfo.constraint_name = FK.constraint_name FOR xml path('')), 1, 1, '')) + ') REFERENCES ' + PK_cons.table_name + ' (' + (SELECT Stuff((SELECT ',' + column_name FROM information_schema.key_column_usage colInfo WHERE colInfo.constraint_name = FK.unique_constraint_name FOR xml path('')), 1, 1, '')) + ')' + ' ON DELETE ' + fk.update_rule + ' ON UPDATE ' + FK.delete_rule FROM information_schema.referential_constraints AS FK INNER JOIN information_schema.key_column_usage AS ReferencingConstraint ON FK.constraint_schema = ReferencingConstraint.constraint_schema AND FK.constraint_name = ReferencingConstraint.constraint_name INNER JOIN information_schema.key_column_usage AS PK_cons ON FK.constraint_schema = ReferencingConstraint.constraint_schema AND FK.unique_constraint_name = PK_cons.CONSTRAINT_NAME
This query will save the scripts that will help you re-create the Foreign Keys on the target database. Once all data has been migrated using the wizard, just run this script and the FK's will be re-created :)
The scripts will be shown on the Results tab of Management Studio.
To start the migration open the program and hitNext.
In Data Source choose SQL Server Native Client 11.0, and now choose the Server name & port of your origin database.
On Authentication, select Use SQL Server Authentication, and inform your user & password for the database.
Click on Refresh, and select the name of the origin database from the populated dropbox.
Now hit Next to configuer the target database.
The target database configuration is analog to the source database configuration. Select SQL Server Native Cliente 11.0 as Destination, and then inform the Server name & port.
On Authentication, choose Use SQL Server Authentication, and inform o user & password.
Now click on Refresh, and select the name of the target database from the populated dropbox.
Click on Next, select Copy data from one or more tables or views and hit Next again.
On the next screen choose the tables and views to be migrated. To migrate the complete database make sure to select all the content.
Hit Next, confirm that the option Run immediately is selected, and click on Finish to start the data migration process.
Done! in a few minutes (depending on the size of the database) all your info will be replicated to Umbler platform.