How can we help?

Help via Chat 24h from monday to friday

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.

Important: We recommend backing up your data before starting any of these processes.

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.

This tool will allow only migration of tables & views. Database objects like primary keys and foreign keys need to be transferred manually.

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.

fk-tri.PNG

To start the migration open the program and hitNext.

initial.png

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.

source.png

Now hit Next to configuer the target database.

destination.png

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.

details.png

On the next screen choose the tables and views to be migrated. To migrate the complete database make sure to select all the content.

details2.png

Hit Next, confirm that the option Run immediately is selected, and click on Finish to start the data migration process.

finalconfigs.png

Done! in a few minutes (depending on the size of the database) all your info will be replicated to Umbler platform.

Remember: after the data is migrated, don't forget to re-create the Primary e Foreign Keys. Click here if you haven't exported your Primary Keys and Foreign Keys yet ;)