Customer Support | MS SQL Quick Start Guide


Contents

Introduction

Before you start

Connect to your database

Setting your user permissions

Importing an existing Database

Back up your Microsoft SQL database

Back up your database content

Backing up stored procedures and functions

Importing Microsoft SQL database content

Introduction

SQL databases provide many benefits to the web designer, allowing you to dynamically update your web pages, collect and maintain customer data and allowing customers to contribute to your website with content of their own. In addition many software applications, such as blogs, forums and content management systems require a database to store their information.

SQL stands for Structured Query Language, which is a standard interactive programming language used for many popular databases.

We offer two types of database: MySQL and Microsoft SQL Server. Most popular web applications require one of these database types. You can find out which database type you need on your chosen application's website.

MS SQL is a database designed by Microsoft to be compatible with standard SQL commands.

Before you start

Microsoft developed SQL Server Management Studio to help you manage your database easily and quickly. Some of the tasks described within this guide require the installation of this software on your own computer.

Through the Graphical User Interface (GUI) you are able to create tables, schema, objects and users. You can also set permissions and run scripts directly on your database.

Microsoft SQL Server Management Studio is available as a free download at:

Connect to your database

Step 1

Open SQL Server Management Studio.

Step 2

Enter your database IP address in the text box marked Server name.

Step 3

Select Use SQL Server authentication from the list, enter your database username and password in the text boxes provided, and then click Connect.

Step 4

Your database can be found in the list titled Object Explorer.

Setting your user permissions

It is good practice to create additional users with limited permissions to connect to your database for day to day use.

In the following example we have already created a new user, which our website will use to connect to our database with. For this reason, we will limit the access this user will have to our database.

Step 1

Open SQL Server Management Studio.

Step 2

Enter your database IP address in the text box marked Server name.

Step 3

Select Use SQL Server authentication from the list, enter your database owner (DBO) username and password in the text boxes provided, and then click Connect.

Step 4

In the left hand Object explorer window, open your database, and then double click on the user you want to set permissions on.

Step 5

A new window will open. To change the role of the user across the entire database select Membership and then tick the boxes appropriate to the new role of your user.

In this example we will give our website user the db_datareader role. So they can read all the data in the database, but are unable to make changes.

There are 9 different roles available, these being:

Role / Permissions
db_owner / Can perform any activity in the database.
db_accessadmin / Can add or remove users or SQL Server users in the database.
db_datareader / Can see any data from all user tables in the database.
db_datawriter / Can add, delete, or change data in all user tables.
db_ddladmin / Can make any data definition language commands in the database.
db_securityadmin / Can manage statement and object permissions in the database.
db_backupoperator / Can back up the database.
db_denydatareader / Can deny permission to select data in the database.
db_denydatawriter / Can deny permission to change data in the database.

Now we may want to enable this user to make changes to a particular table within the database.

Step 6

To set custom permissions to specific areas of your database, click Securables in the top left corner of the screen.

Step 7

Click the Search button to find the objects within your database to set permissions for. In this example we will set the permissions on two tables within the database.

Step 8

Select Specific objects then click OK.

Step 9

Click Object Types… select the objects that you want to edit and click OK.

Step 10

Enter the object names in the text box provided, or click Browse to choose your objects from a list. When you have selected all the tables and objects you want to modify the users’ permissions for, click OK.

Step 11

You should now be back on the Securables window. Select the object you want to modify and use the check boxes in the lower pane to set the permissions for your user.

Importing an existing Database

If you already have an existing database, either with another hosting company, or on a development server on your own network, you can import your existing database using Microsoft SQL Server Management Studio. The software will allow you to import data directly from another SQL database, a Microsoft Access database, a Microsoft Excel spreadsheet, or a flat text file.

This example will show you how to transfer the data from another Microsoft SQL database.

Step 1

Open SQL Server Management Studio.

Step 2

Connect to your database using the database owner username and password (see the section Connect to your database earlier in this guide for assistance).

Step 3

Right click on your database and select TasksImport Data from the popup menu.

Step 4

The Import and Export Wizard will open, click Next.

Step 5

Select SQL Server Native Client 11.0 from the list, and enter the database server and connection details required to connect to the source database. Once you have entered these, select the source Database from the list, and then click Next.

Step 6

Enter the details of your MSSQL database, as follows:

  • Data source: Select SQL Server Native Client 10.0 from the drop down menu.
  • Server name: Enter the IP address of your MSSQL database.
  • Authentication: Select Use SQL Server Authentication and enter your database username and password. This is the same username and password you chose when you created your database.
  • Database: Select your database from the drop down menu.

When you've entered the information, click Next.

Step 7

Select the option Copy data from one or more tables or views and click Next.

Step 8

Select the tables you wish to import, and then click Next.

Step 9

Click Next, and thenclick Finish to import your data.

You will receive confirmation that your data has been imported to your database.

Back up your Microsoft SQL database

It is highly recommend that you make regular backups of your database, and this process is easier than ever using SQL Server Management Studio.

Back up your database content

Step 1

Open SQL Server Management Studio.

Step 2

Connect to your database using the database owner username and password (see the section Connect to your database earlier in this guide for assistance).

Step 3

Right click on your database and select Tasks, then Export Data from the popup menus.

Step 4

The Import/Export wizard will open, click Next.

Step 5

In the form provided, enter the following details:

  • Data source: Select SQL Server Native Client 10.0 from the drop down menu.
  • Server Name: Enter the IP address of your database server.
  • Authentication: Select Use SQL Server Authentication, and then enter your database username and password. This is the username and password you chose when you created your database.
  • Database: Enter the name of your database from the drop down menu.

Click Next once you have entered all the required information.

Step 6

You will be asked where to export the database contents to. Select Flat File Destination from the drop down menu and choose a filename and location for your backup.

In this example we have also changed the Locale to English (United Kingdom) and selected the option marked Column names in the first data row.

Click Next when you are done.

Step 7

Select Copy data from one or more tables or views and click Next.

Step 8

Finally, choose which tables or views you want to export and select how you would like the file to be formatted, and click Next.

Step 9

Click Next, and then clickFinish to export your file. You will receive confirmation that your database content has been backed up.

Backing up stored procedures and functions

Step 1

Open SQL Server Management Studio.

Step 2

Connect to your database using the database owner username and password (see the section Connect to your database earlier in this guide for assistance).

Step 3

Navigate to Tasks in the Object Explorer, and then click Generate scripts.

Step 4

Choose Select specific database objects and the Stored Procedures.

Step 5

Choose a location to save your stored procedure. You can leave all other options as the default.

Step 6

Review your selections and click Next.

Step 7

After waiting a few moments, you will see SQL Management Studio obtain a list of objects from your database, prepare the stored procedures and save them to your preferred location.

Importing Microsoft SQL database content

Step 1

Open SQL Server Management Studio.

Step 2

Connect to your database using the database owner username and password (see the section Connect to your database earlier in this guide for assistance).

Step 3

Right click on your database and select TasksImport Data from the popup menu.

Step 4

The SQL Server Import and Export Wizard will open.

Step 5

Choose a data source for the data you wish to import from the Data source list.

Step 6

Define the formatting of your data source. You can use this window to experiment with the formatting. When the table looks correct, click Next.

Step 7

Enter the details of your MSSQL database, as follows. Click Next when you have entered all the required information.

  • Destination: Select SQL Server Native Client 10.0 from the drop down menu.
  • Server name: Enter the IP address of your MSSQL database.
  • Authentication: Select Use SQL Server Authentication and enter your database username and password. This is the same username and password you chose when you created your database.

  • Database: Select your database from the drop down menu.

Step 8

Select the tables you wish to importand then click Next.

Step 9

Click Next, and then click Finish to import your data.

The import process may take a few minutes, and you will receive a confirmation that the data has been successfully imported.

Page 1