Most of the internet relies upon databases to store information, and retrieve said information when delivering digital services. A prime example would be the storage of contact information such as email addresses, phone numbers, and home addresses in ecommerce. In this case, these details need to be categorised and stored so that businesses can update users on their order status (via email or phone), and during the logistics phase when delivering to a home address.

SQL Server Management Studio Express 2014 is a popular management tool used to administer Microsoft SQL Databases and the servers that host them. Microsoft SQL databases are a form of relational database management system (RDBMS) and are the most commonly used database type on the internet.

We will be covering how to use SQL Server Management Studio Express 2014 concerning importing existing data for use on a new database hosting server.

Pre-requisites for using SQL Server Management Studio Express 2014

You will need the following utilities and services before continuing with this guide:

• You will need a Microsoft SQL Server database, which you can configure on the Fasthosts cloud by following this guide.
• The name of the database that you will be importing data to, along with the IP address. This can be found within the Fasthosts Control Panel.
• The database will need a user account with Owner status, and you will need the username and password to log into this account.
• A back-up copy of the database you wish to import. While MSSQL does not have a standard file extension for backups, many users decide upon .bak or a generic Windows backup file.
• The SQL Server Management Studio Express 2014 software package, which can be downloaded from this Microsoft page. You will also find Install Instructions just below the download link on this page, which you should follow before continuing.

Importing databases to Studio Express 2014

A native method for importing databases into Studio Express 2014 is via the Import & Export Wizard. This allows the user to migrate a database in its entirety, or import specific database objects from a source to a destination database.
To do this, first, you will need to open SQL Server Management Studio Express 2014.

Now, you will need to connect to the MSSQL database using the database name and IP address, as mentioned in the pre-requisites section of this guide.

If using Windows Authentication, you can connect directly to a local database via your Windows User Account. You will need to RDP to the SQL Server and use a remote desktop for this option.

Once the database is loaded, and you have logged in and authenticated with an Owner account, select the destination database in SQL Server Management Studio Express 2014. The database will be located on the left-hand side of this window.

Importing with SQL Server Import and Export Wizard

To use this method, right-click on the destination database. Then, navigate to Tasks and select Import Data.

This will open the SQL Server Import and Export Wizard, and ask you to Choose a Data Source. The source will be the database backup that you created, which could be a .bak file, a .ACCDB file, .SQL file, or another related file extension. You will have created this file when creating a backup in your previous SQL database software.

To open a backup file, you need to select Flat File Source in the Data Source selection box.

To locate this backup, click Browse… and navigate to the file you wish to import. If you cannot see your backup file, we recommend changing the Windows Explorer file type to All Files (.), so you can view all items within your backup directory. Then press OK.

NOTE - In our example, we are importing a .SQL file with dummy data, which we created using the Generate Data website. We recommend learning the import process with dummy files before attempting to import any critical data so that you understand how data imports work in SQL Studio Express 2014.

The Import and Export Wizard will automatically determine file properties and the format to use during import. If you wish to change these properties, you must first determine the structure of your data. Delimited formats are typically used with Comma-separated values (.csv) file, or Tab-separated values (.tsv) files. They have columns separated by delimiters (which are specified in the Columns page in the Wizard). Fixed width is used when the data fields have a fixed character length. With the Code Page, it should default to 1252, which is the default Windows character encoding method for Latin alphabet languages (English, French, German, etc.).

When asked to specify the characters that limit the source file, you can choose this for the Row and Column Delimiter. By default, this will be set as {LF}, or a line feed for the Row Delimiter. The Column Delimiter will set to Comma {,} meaning each column is delimited by a comma.

You will also see a preview of rows 2-26 in the source database you are importing. These rows should be similarly formatted to the previous database from which you are importing data.

Now we need to choose a Destination Database. Our database is natively hosted on our virtual machine, so we chose SQL Server Native Client 11.0. This will automatically use the local SQL server. Next, click the Database drop-down arrow and select your destination database, which in our case was ReportServer$TESTSQL.

Under Authentication, use Windows Authentication if you are logged in as an Owner Administrator. Otherwise, select Use SQL Server Authentication and input the login details as detailed in the pre-requisite section of this guide.

Click Next. Make a note of the destination dbo name for reference later, and click Next again in the Select Source Tables and Views section of the wizard.

Under Save and Run Package, you can choose to save a SQL Server Integration Services (SSIS) package. This is used to merge data from heterogeneous data sources into SQL Server. We chose not to do this in our example. We also chose to Run Immediately. Click Finish, and the data will import.

We can now see dbo.TestSQL (or the name you specified) under the Tables tab in our database. This means the import was successful!

Start hosting with Fasthosts

Database administration may be complicated, but server hosting with Fasthosts is easy! Our Dedicated and Bare Metal servers benefit from NVMe SSD technology, and 10GB/s connectivity; perfect for latency intensive workloads like SQL database hosting.

Get started with 50% off for the first three months of your server package (limited-time offer). Contact sales on 0808 1686 777, or via email at sales@fasthosts.co.uk to find out more!