Good afternoon everyone!

Yesterday, I wrote a short blog post covering how to create a back up from your database in order to safeguard yourself from losing your valuable data. Today, I am continuing with this theme and going to discuss how to restore your database from a backed up file. If you haven't done so already, I would suggest reading yesterday's blog post, which can be found here:

http://sql-genius.blogspot.co.uk/2013/07/creating-database-backups.html

Restoring From a Database Back Up 

In order to restore from a back up, right click on your database instance and go to Tasks > Restore...
As you can see below, there are 4 different options that you can restore from:

• Database
• Files and Filegroups
• Transaction Log 
• Page

For the purpose of this example, I am going to do a complete restore from a database back up (this is simply because it is the method I have used most often in my work place). For information on other types of back up, take a look at the link below and this will give you a more informed choice on what kind of restore is most suitable for your needs:

http://msdn.microsoft.com/en-us/library/ms191253.aspx

After clicking on "Database...", you should see the following screen: 

General 

Now you will need to decide where to back up the database from. Again, for the purposes of this tutorial I am going to use the back up created in yesterday's blog post. Therefore, I want to select "Device" and click on the ellipses (...) as seen in the image below:

Hopefully you will remember where you stored your database back up. Always make sure that the file has been named properly and is stored within an accessible folder on an appropriate medium. Note that it's not always a good idea to have a back up on the same device as the main database because if something goes wrong with that server, not only would you have lost the primary instance but you would have lost the back up instance too. If you can't find your file straight away, make sure you have "view all files" on as SQL Server may not pick up the .bak file straight away. Once you have found the back up, click on add:

Once you have made your decisions on this page, always make sure any other options are set up correctly as well.

Files 

Alternatively to restoring the entire database from a back up, you can select specific files to back up from. However for the purpose of this blog post, I can skip this part.

Options 

You can choose different options to achieve different results:

• Overwrite the existing database (WITH REPLACE)
• Preserve the replication settings (WITH KEEP_REPLICATION) 
• Restrict access to the restored database (WITH RESTRICTED_USER) 

(Note that none of the options listed above are actually compulsory and you can simply leave the boxes blank or uncheck any checked boxes).

or 

• RESTORE WITH RECOVERY - (default) - Any uncommitted transactions are rolled back so the database will be in a usable state after the restore. 
• RESTORE WITH NONRECOVERY - Any uncommitted transactions are left in the state they were in before the restore. This means that if you wish to start using the database again after the restore, you will need to cover the database first. 
• RESTORE WITH STANDBY - Any uncommitted transactions are rolled back but the database is left in a read-only mode. 

For the purpose of this example, I want to overwrite the existing database and it's contents and leave the RESTORE WITH RECOVERY default on as well. This is the set up I have used when restoring databases in my working environment:

Once you are satisfied that you have set all of the options correctly, click on OK. The progress bar in the bottom left will turn and there will be a restoration bar along the top of the window as well. 

Once the restore is complete you will see the following message: 

Now, if you select the database and query from it, you will notice that it's been completely restored back to its previous state (when the back up was created).

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.

Good afternoon everyone! I hope you all had a lovely weekend!

Today's short blog post is going to cover how to create a back up from your database. You should always back up any active database on your server because it will safeguard your database (and yourself!) from losing any of your database and contents. 

Once you have created a back up, you can then restore completely (or even partially) to this back up. Sometimes, it's useful to use these back ups as a way of archiving old data. You never know when you may need to refer back to an archived application build and you will need the relevant database to go with it (as you know, databases can grow vastly in a very small period of time, from both a data point of view as well as database structure point of view). 

Creating a Full Database Back Up 

In order to create a back up, right click on your database instance and go to Tasks > Back Up...

You should see the following screen: 

Make sure that where you wish to back your data up to is the right place, and make sure that the file has a suitable name so you understand exactly what database has been backed up and when it was backed up. You can choose between 3 main back up types from the drop down list: 

• Full 
• Differential 
• Transaction Log

However different settings will provide an even wider range of back up types. Please see the link below for more details on them: 

http://msdn.microsoft.com/en-us/library/ms175477.aspx

Once you have made your decisions on this page, always make sure the options are set up correctly as well: 

For this example, I am just going to leave the default options. You can choose to overwrite any existing back up sets and set up new media sets to save your database back up to, however for this example I won't need to do that. 

Once you are ready to go, press ok and you will see this appear in the progress bar: 

If, for whatever reason, you want to stop the back up, click on the "Stop action now" hyperlink. This will cause no negative effects to the database at all. 

Once the back up is complete you will see the following message: 

What Next...

And that really is all there is to it! Note that you can also create timed regular back ups and this example just shows how you can do it manually. Tomorrow I will be discussing how to restore from a database back up so stay tuned for that! 

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.

Good afternoon everyone!

Today we will be discussing the importance of database design and how thinking outside the box from the offset can prevent any major database changes in the long run. Some database changes are inevitable as a system grows and may be fairly easy to implement, however, changing names of columns, names of tables and adding new relationships that interact with pre-existing ones can quickly become a complete nightmare to modify. This is because the objects that you are changing could be referenced all over the place, including within Triggers, Stored Procedures, Views and even in the back-end C#/ASP.NET code. All references would need to be re-pointed to the new names, otherwise Server Errors will be appearing all over the place and could render a system completely broken.

Steps to Creating the Ideal Database Solution 

It may be useful to begin with creating use cases and/or flowcharts in order to work out how customers will interact with the system. Usability of a piece of software is paramount to its success as, if it works but it's not explicitly clear on how an end user can use it, it will be as effective as a solution that doesn't work at all. You should use this opportunity to think of possible on-screen messages that may have to appear, when validation may be required for certain page elements such as username and password entry etc.

The image below shows an example of a flowchart that I have created for a booking system whereby a user can reserve a table at a restaurant:

If the system will have pre-existing data that comes from either a spreadsheet or a redundant system it may be a good idea to start looking at this information to help determine what database tables and fields you will need. If you can already split the data within the spreadsheet into columns then that's fantastic and really helps.

Whenever I design a database, I always hand draw what I think the final design could look like. It doesn't have to be neat at all, just providing it makes sense to you when you come to putting it in a computerised format then it is fine. I also use this opportunity to decide on table names and primary/foreign key fields, which may be easier to do after analysing pre-existing data to determine exactly what attributes I will need. Ensure that you always choose appropriate naming conventions! My blog post containing Tips on How To (Correctly!) Define Your Tables may help you. Here is a quick example of my database drawing:

Next, I am going to transfer my ideas into an Entity Relationship Diagram in Visio. This is where I start to think more about what columns I will need in each table and their data types. As mentioned before in a previous post, it's very important to have one naming convention and to stick to it, plus it's important to use sensible naming conventions that shouldn't require changing in the long run. It would also be a good idea to normalise your database design at this level before it has been created properly in SQL Server (For more on normalisation, read my blog post here):

Now, I can transfer my Visio design into SQL Server. You could create the tables either using SQL code or by using the designer (you should have an idea on how to do both) but for the purposes of this exercise, I have decided to create the database using the code below:

As a database diagram, it would look like this:

And there you have it! The basic design principles in order to create a database. As you can see it isn't too dissimilar from the original designs but that is probably because there wasn't much to the system. The larger the system, the more opportunity there is for errors to sneak in unexpectedly. However if you follow these simple steps, you should be able to create a pretty good database design on your first attempt.

As a Final Word... 

There may, unfortunately, be occasions where you have no choice and have to rename tables and columns. If this is the case, a useful tool called ApexSQL Refactor 2013 (discovered by one of the Senior Developers at Light Speed IT Solutions) may be of use. Follow the link below for more information and to download:

http://www.apexsql.com/sql_tools_refactor.aspx

If anyone has any questions, comments or feedback on any aspect of my blog please don't hesitate to contact me. Thanks for reading!

For the sake of these tutorials, I will be using SQL Server Management Studio on a SQL Server 2012 Server.

The first lesson will be to create Databases, Tables and Fields using SQL code.

Create a Database 

It is possible to create Databases using the user interface and then adding a database name. However, you can quite easily select "New Query" from the toolbar and type in the following:

On clicking "Run", the database will be created and displayed within the Object Explorer panel (commonly found on the left hand side of SQL Server Management Studio)

Creating Tables and Fields 

Next, we will want to create some tables to go inside our newly created database. Remember before we do this, always ensure that you put "USE databaseName" above the CREATE statements so that SQL Server Management Studio knows where to put the new tables (see code below): 

Next , create a table called Customer:

In order to identify between the records, a primary key column will be required. This table will typically store customer details, therefore customer FirstName, LastName, Address, Postcode, ContactNo and EmailAddress would be good places to start when thinking of potential field names. It is also a good idea to think about what data types these fields would take. For a unique key, either an integer (int) or a globally unique identifier (guid) would be ideal. For this tutorial - I suggest using an integer despite it being possible to use both. There will be a later blog post to explain the differences between them so you can decide which best suits your situation.

Now you will need to consider how much space will need to be allocated to each field. For example, a FirstName shouldn't really contain more than 35 characters. Therefore, 35 will be placed in brackets next to the data type in order to signify this. Note that some data types may not allow you to specify an amount of space (such as integer values) as they already have a set amount of space to use. It will also allow you to set the size as "MAX" in the possibility that -- In the case of primary keys, they will need to be set as Identity fields so that they automatically generate a an ID number. You will notice in the code sample below that it is made up similarly to a co-ordinate (1,1). The first number declares the seed (the number that the Primary Key will start at) and the second number declares the increment (the amount in between each primary key as it's inserted into the table). By default, fields are set to allow NULLs if no data has been entered by the user. You would not want this for all of your fields so be careful and think about what fields you want as null able. To declare a field as NOT NULL, all you have to do is place the NOT NULL at the end of the declaration. Note that Primary Keys must always be NOT NULL. 

The code below creates the basic field types and declares the CustomerId as a Primary Key:

And that code will create your first database table! Now, create an Item table in exactly the same way (see code below):

Creating Relationships

We will need to consider relationships between these two tables. You can have different relationships between tables as depicted in the table below:

So looking at our scenario, can a customer buy many items? Yes... Could many customers buy the same item?... Yes! Therefore, this will require a many to many relationship.

In order to create many to many relationship tables in SQL Server Management Studio, a bridge table will need to be made in order to link the 2 by their ID's. This will normally consist of its own unique Primary Key, along with the 2 Primary Key fields from the 2 tables you have just created. These will be declared as Foreign Keys as they do already exist, we are just pulling the values from the other tables. Don't forget to reference the foreign keys so you know the exact table and exact field they are coming from. See the example code below:

Let's take a look at what we've done! 

In order to see what has been created, click onto "Diagrams" and create a database diagram adding the 3 newly created tables into it. Note at this point that you may need to refresh the popup in order for it to display your tables!

Once created, it should look like this:

Seeing as this is an introduction, I am going to keep this post short and to the point (to help beginners who may be reading this blog).

What is a Database?

Let's start right from the very beginning. A database is a container for organised data that is easy to access, easy to store, easy to update and easy to manage. Please note that a database IS NOT a spreadsheet (and the reasons why this is will be discussed in later blog posts).

What is a Table?

A table simply contains data that is made up of columns and rows. Columns are vertical and rows are horizontal.

What is a Field? 

A field is another name for a column within a table. Tables can be made up with many fields with different data types. For example, a field that has an "int" datatype can only store an integer (or number) value between -2,147,483,648 through to 2,147,483,647 for a signed integer and from 0 to 4294967295 for an unsigned integer. No other characters will be allowed and an error would appear if an alternative input was entered.

What is a Record?

A record is another name for a row within a table.

What is DBMS?

A Database Management System (or a DBMS) is a software package that allows a user to create and maintain a database.

What is RDBMS?

A Relational Database Management System (or a RDBMS) is a software package that not only allows you to create and maintain tables of data, but also allows you to manage the relationships between tables.

So what is SQL? 

Structured Query Language (or SQL) is the standard language used to communicate with a database in order to SELECT, INSERT, UPDATE, and DELETE data. Further down the line, example code will be posted to demonstrate how these statements can be executed in order to manipulate result sets.