How To Open Bak File İn Sql Server Management Studio?
If you have a database already made and you intend to use it in your Microsoft SQL Server Management Studio (SSMS) program, the process is very simple: to use a file with the .bak extension. A file with a .bak extension provides a lot of convenience to the software developers. Look at the extension files you see in the photo below.
First, the Microsoft SQL Server Management Studio program opens. Click on the “Object Explorer” panel once. You will see the command “Database” on the incoming screen. Right-click on this command. Click on the “Restore Database” command once again. The “Select Backup Device” window appears on the screen.
On the screen: Next to the “Specify the backup media and its location for your operation” command, select Backup Media type: “File” and select the “Look in this file” file and click Add.
We see the look file that we selected in the Database section and the name of our database is marked. Before you finish the process, click OK in the box below Restore and click OK.
The next section is about functions and operators. We will see the most frequently used ones. Let’s do an overview. When we discussed the WHERE clause in the earlier lectures, we’ve already discussed logical operators AND,OR, NOT, and comparison operators listed here – equal, less than, greater than and so on.
Or the next slide we will also discuss the LIKE operator. Mathematical operators – plus, minus, multiplication,division – no surprises here.For the character data types, as we discussed before, the concatenation operator is written as two vertical bars.
Concatenation combines two values into one value. And then some functions. SQL language has many functions. Here we’ll talk about the most frequently used ones. Details on the next slides.
First, the functions for the numeric data types. The ABS function returns absolute value. For the positive numbers, it returns them as they are. For negative numbers, it removes the minus sign, so they become positive. On this slide we have a SELECT without any form clause. This SELECT does not get rows from a table. It only returns one row, with the literals you provide. Next function is ROUND.
It returns the closest whole number. The number 2.8 is rounded up to 3. The number 2.3 is rounded down, to number 2. The number 2.5 is in the middle, by agreement it is rounded up, to number 3. Some more functions to get whole numbers. The TRUNC function removes the fractional part. So TRUNC from 2.8 is 2, not 3 as the ROUND would do.
And two more functions – FLOOR and CEILING.
The FLOOR returns the the whole number which is less than a given value. For positive numbers, It’s the same as TRUNC. However, for the negative numbers the results are different. In this example, removing fractional part from -2.8 results in the number -2, but the closest whole number which is less of than -2.8 is -3.
What are the Microsoft SQL database functions?
A similar thing for the CEILING function. It returns a whole number larger then you a given value. For 2.8 it’s 3. And for -2.8 it’s -2. And finally, the TRUNC can do one more thing. If you give it a second argument, it can remove only part of the fractional part, up to the given number of digits. In our example, the the value 2 in the second argument means this “keep two digits after the decimal point, remove anything else”.
So we get 2.84.All right. So we learned some functions for the numeric data types.The next topic is functions for character data type. First let’s talk about LIKE operator. This operator takes a character value, and compares it with the template. In the template, there are two special symbols: percentage, and underscore.
The first one, percentage,means any number of characters, including 0 characters. And the second one, underscore, means any character, but exactly one character.
In our example, the condition is: name like ‘percentage of percentage’. It means that we search for the rows with a name which contains the word “of” surrounded by the blanks. And the name can have any umber of characters before and after that ” of “.
In our example, we get two rows whose name matches the pattern: “Master of Puppets”, And “A Kind of Magic”. The next function is SUBSTRING. It returns as substring of a string. In the first example, we take the phrase “this is a book”, and get a substring of it, starting at position 11, with 4 characters in length.
So it gets the word “book”. In the second example, we take a substring starting at position 22. Since our string is shorter, we do not find a substring. So the result is an empty string. Notice that we did not get a NULL value in this case, we get an empty value.
The second function is POSITION. It searches for a substring inside a string. And if it finds one, then it returns the starting point – the position of a substring. In our case, the word “book” is found in the phrase “this is a book”, and it is found starting at 11th position.
If the substring is not found, as in our second example here, then the function returns 0. Again, notice that that is not NULL, it’s a zero. And finally, the CHARACTER_LENGTH. This function returns a number of characters in a given string. A few more useful functions. The function TRIM removes some characters on the left and the right of a string.
By default it removes blanks, as in the first example. But you can also provide which characters to remove, as in the second example. By default, it will remove characters both from the beginning and from the end of the string. You can also remove characters only from the beginning, or only from the end.
To do this, you can write either the LEADING or TRAILING, as shown in the third and fourth example or this slide. And finally, the functions LOWER and UPPER.
They change the given the values either to the lower case, by a LOWER function, or to the upper case by the UPPER function. All right. So we learned some functions for the character data types. Now let’s talk about some functions for date, time and timestamps. First of all, let’s convert a character data type to the date data type.
In our example, 2018-12-31 is a character value. If we want to create a date out of it, we need to provide a format – where the year, month and day are specified in the given string.
We do this by specifying the format: year, then month, then day. In the second example, the date is provided in another layout. We specify another format here, so that’s the TO_DATE function will work correctly. Same for the TIMESTAMP data type. Here we give a format for the date and for the time. For time we have abbreviations for hours, minutes and seconds.
There are also some other formatting options. Here we cover only the most frequently used ones. The next function is TO_CHAR – it’s the other way around, get a date value, and convert it to a character data type. The first argument is the date, and the second argument says how we want the string to look like. In the first example – year, then month, and then the day. The second example is day – month – year.
Notice that in the second example, the first argument is already is the date data type, and the standard literal for the data data type is provided like this: year, then month, then day. Here we take this literal, and convert it to a character data type with a given format. Sometimes you may want to get the current date, current time and current timestamp.
These functions are provided here. And finally, one more function – EXTRACT – returns different parts of the date or time. EXTRACT year from a given date – this function returns a year of that date. Same for a month and a day. The same function is applicable to time and timestamp. There you can also extract hour, minutes and seconds. All right. So we learned some useful functions and the operators for different data types.
Hi, in this lesson we will learn Create Local Database Sql Server Management Studio 2014.
Today we’re, going to be talking about Microsoft SQL Server management studio 2014. We’re going to start off by setting up a database instance and working with local DB. So to do this we’re going to do two things we’re going to start off by loading up the SQL Server 2014 management studio and we’re also we’re also going to run a command prompt and I like to run that as an administrator.
In this case you don’t really have to but it’s probably just a good idea just to make sure we have everything ready to go ok so at this point if we’re loading this up we’re going to see a screen that looks like this it’s asking for a server name and we have our windows authentication methods set up here.
Typically you don’t necessarily know,what your server name is there’s several local DB names so in order to just . I’m going to I’m going to work off the premise that we just can’t find it so we’re going to start off at the beginning and we’re going to get started just by creating a new database instance.
How do I create a local database inside of Microsoft SQL Server 2014?
I’m going to call that local DB so to do this we type at the command prompt we type SQL local DB ok and when we do this we’re just by itself it comes up with a bunch of help instructions ok so at this point I can create a database instance I can delete that can start stop share unshare list all of the local DB instances andthat I can print information about an instance get the versions of local be DBS and tracing on and off so let’s start off by just asking for some information here let’s get let’s ask for info and we’ll just do it like that info.
So currently we have a few different versions of local DB already on the system here we have a couple that I’ve created for a class that each we have mssql local DB projects b12 projects B. 13 + V 11 and b12 know the projects typically ships with Visual Studio the V 11 mostly comes with SQL Studio Express and then 12 comes with our 2014 version of SQL Server management studio to the best of my knowledge that may not all be perfect but it’s close enough that’s the general rendition of it okay so if I start off here.
I am going to again let’s assume that none of these things work here and we’re going to get started off fresh because we want to use a local DB name that we can use and we can remember really easily let’s just call it we’re going to call it local DB demo okay. Oh and what I’m also going to do here let’s just do one more thing we’re going to check the versions okay oops I spelled it wrong I use the versions there we go okay there are three versions installed on my system right now .
I have a 2012 2014 2016 2016 is usually the one that that is used in Visual Studio 2016 as well as 2015 what I’m going to do because I’m using SQL management studio 2014 I’m going to use this version right here while I do this so SQL local DB create I could have used the short form C as well if I wanted to I call that local DB demo close quote and I’m going to put in this version number twelve dot 0.25 69.0 that and it just takes a minute to propagate that server done okay now if we go over here into SQL Server management studio.
I can type that dead node E demo DB or local. DB demo and just load it up connect. So here we have it we have no. Databases setup yet we’re going to do. That in our next session so guys don’t forget to like and share and we’ll catch. You on the next tutorial.