SQL SERVER – Take Database Backup using SSMS – SQL in Sixty Seconds #037 – Video

Whenever I am suggesting something which changes how database works or the existing status of the database, my suggestion along with it is to take the database backup before making such changes. If the changes are in configurations, that can be easily revert but if the changes are such that it will impact the data, I always suggest to take backup. The nature of this blog is such that we have readership from readers with different expertise, some are experts and some are novice.

One of the questions I keep on receiving comments are whenever I give suggestion to take a full database backup is – how to take database backup. It is not possible to explain in each and every blog regarding how to take backup of the database as it will be of no interest to the users who are very much familiar with this concept. To help the beginners who have yet to learn regarding how to take database backup, here is the SQL in Sixty Series episode which explains how to take database backup.

There are three types of the backup – 1) Full Backup 2) Differential Backup and 3) Log Backup. We can take the differential backup as well log backup from the same screen where we take full backup from. I have explained the same also in later parts of the video.

If you are using SQL Server Management Studio – here is the path which will bring you to the screen where you can take various kinds of backup.

Right Click on Database >> Tasks >> Back up… 

Let us see how to take backup in this SQL in Sixty Seconds:

Please note that if your database is large, it will take long time to take backup. We will focus on the various steps to initiate the backup in this video.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video

Developers often face situations when they find their column have duplicate records and they want to delete it. A good developer will never delete any data without observing it and making sure that what is being deleted is the absolutely fine to delete. Before deleting duplicate data, one should select it and see if the data is really duplicate.

In this video we are demonstrating two scripts – 1) selects duplicate records 2) deletes duplicate records.

We are assuming that the table has a unique incremental id. Additionally, we are assuming that in the case of the duplicate records we would like to keep the latest record. If there is really a business need to keep unique records, one should consider to create a unique index on the column. Unique index will prevent users entering duplicate data into the table from the beginning. This should be the best solution. However, deleting duplicate data is also a very valid request. If user realizes that they need to keep only unique records in the column and if they are willing to create unique constraint, the very first requirement of creating a unique constraint is to delete the duplicate records.

Let us see how to connect the values in Sixty Seconds:

Here is the script which is used in the video.

USE tempdb
GO
CREATE TABLE TestTable (ID INT, NameCol VARCHAR(100))
GO
INSERT INTO TestTable (ID, NameCol)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Second'
UNION ALL
SELECT 4, 'Second'
UNION ALL
SELECT 5, 'Second'
UNION ALL
SELECT 6, 'Third'
GO
-- Selecting Data
SELECT *
FROM TestTable
GO
-- Detecting Duplicate
SELECT NameCol, COUNT(*) TotalCount
FROM TestTable
GROUP BY NameCol
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
GO
-- Deleting Duplicate
DELETE
FROM
TestTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TestTable
GROUP BY NameCol)
GO
-- Selecting Data
SELECT *
FROM TestTable
GO
DROP TABLE TestTable
GO

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

http://www.pinaldave.com/bimg/sixty00.jpgConcatenating  string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL Server 2012 has introduced new function CONCAT which concatenates the strings much efficiently.

When we concat values with ‘+’ in SQL Server we have to make sure that values are in string format. However, when we attempt to concat integer we have to convert the integers to a string or else it will throw an error. However, with the newly introduce the function of CONCAT in SQL Server 2012 we do not have to worry about this kind of issue. It concatenates strings and integers without casting or converting them. You can specify various values as a parameter to CONCAT functions and it concatenates them together.

Let us see how to concat the values in Sixty Seconds:

Here is the script which is used in the video.

-- Method 1: Concatenating two strings
SELECT 'FirstName' + ' ' + 'LastName' AS FullName
-- Method 2: Concatenating two Numbers
SELECT CAST(1 AS VARCHAR(10)) + ' ' + CAST(2 AS VARCHAR(10))
-- Method 3: Concatenating values of table columns
SELECT FirstName + ' ' + LastName AS FullName
FROM AdventureWorks2012.Person.Person
-- Method 4: SQL Server 2012 CONCAT function
SELECT CONCAT('FirstName' , ' ' , 'LastName') AS FullName
-- Method 5: SQL Server 2012 CONCAT function
SELECT CONCAT('FirstName' , ' ' , 1) AS FullName

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Auto Recovery File Settings in SSMS – SQL in Sixty Seconds #034 – Video

Every developer once in a while facing an unfortunate situation where they have not yet saved the work and their SQL Server Management Studio crashes. Well, you can minimize the loss by optimizing auto recovery settings. In this video we can see how to set the auto recovery settings.

Go to SSMS >> Tools >> Options >> Environment >> AutoRecover

There are two different settings:

1) Save AutoRecover Information Every Minutes

This option will save the SQL Query file at certain interval. Set this option to minimum value possible to avoid loss. If you have set this value to 5, in the worst possible case, you can loose last 5 minutes of the work.

2) Keep AutoRecover Information for Days

This option will preserve the AutoRecovery information for specified days. Though, I suggest in case of accident open SQL Server Management Studio right away and recover your file. Do not procrastinate this important task for future dates.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

 

SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video

A very common requirement of developers is to format datetime to their specific need. Every geographic location has different need of the date formats. Some countries follow the standard of mm/dd/yy and some countries as dd/mm/yy. The need of developer changes as geographic location changes. In SQL Server there are various functions to aid this requirement. There is function CAST, which developers have been using for a long time as well function CONVERT which is a more enhanced version of CAST. In the latest version of SQL Server 2012 a new function FORMAT is introduced as well.

In this SQL in Sixty Seconds video we cover two different methods to display the datetime in specific format. 1) CONVERT function and 2) FORMAT function.

Let me know what you think of this video. Here is the script which is used in the video:

-- http://blog.SQLAuthority.com
-- SQL Server 2000/2005/2008/2012 onwards
-- Datetime
SELECT CONVERT(VARCHAR(30),GETDATE()) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),10) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),110) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),5) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),105) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS DateConvert;
GO
-- SQL Server 2012 onwards
-- Various format of Datetime
SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS DateConvert;
SELECT FORMAT ( GETDATE(), 'dd mon yyyy HH:m:ss:mmm', 'en-US' ) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS DateConvert;
SELECT FORMAT ( GETDATE(), 'HH:m:ss:mmm', 'en-US' ) AS DateConvert;
GO
-- Specific usage of Format function
SELECT FORMAT(GETDATE(), N'"Current Time is "dddd MMMM dd, yyyy', 'en-US') AS CurrentTimeString;

This video discusses CONVERT and FORMAT in simple manner but the subject is much deeper and there are lots of information to cover along with it. I strongly suggest that you go over related blog posts in next section as there are wealth of knowledge discussed there.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Rename Columnname or Tablename – SQL in Sixty Seconds #032 – Video

We all make mistakes at some point of time and we all change our opinion. There are quite a lot of people in the world who have changed their name after they have grown up. Some corrected their parent’s mistake and some create new mistake. Well, databases are not protected from such incidents. There are many reasons why developers may want to change the name of the column or table after it was initially created. The goal of this video is not to dwell on the reasons but to learn how we can rename the column and table.

Earlier I have written the article on this subject over here: SQL SERVER – How to Rename a Column Name or Table Name. I have revised the same article over here and created this video.

There is one very important point to remember that by changing the column name or table name one creates the possibility of errors in the application the columns and tables are used. When any column or table name is changed, the developer should go through every place in the code base, ad-hoc queries, stored procedures, views and any other place where there are possibility of their usage and change them to the new name. If this is one followed up religiously there are quite a lot of changes that application will stop working due to this name change.  One has to remember that changing column name does not change the name of the indexes, constraints etc and they will continue to reference the old name. Though this will not stop the show but will create visual un-comfort as well confusion in many cases.

Here is my question back to you – have you changed ever column name or table name in production database (after project going live)? If yes, what was the scenario and need of doing it. After all it is just a name.

Let me know what you think of this video. Here is the updated script.

USE tempdb
GO
CREATE TABLE TestTable (ID INT, OldName VARCHAR(20))
GO
INSERT INTO TestTable
VALUES (1, 'First')
GO
-- Check the Tabledata
SELECT *
FROM TestTable
GO
-- Rename the ColumnName
sp_RENAME 'TestTable.OldName', 'NewName', 'Column'
GO
-- Check the Tabledata
SELECT *
FROM TestTable
GO
-- Rename the TableName
sp_RENAME 'TestTable', 'NewTable'
GO
-- Check the Tabledata - Error
SELECT *
FROM TestTable
GO
-- Check the Tabledata - New
SELECT *
FROM NewTable
GO
-- Cleanup
DROP TABLE NewTable
GO

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video

Copy data from one table to another table is one of the most requested questions on forums, Facebook and Twitter. The question has come in many formats and there are places I have seen developers are using cursor instead of this direct method.

Earlier I have written the similar article a few years ago - SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE. The article has been very popular and I have received many interesting and constructive comments. However there were two specific comments keep on ending up on my mailbox.

1) SQL Server AdventureWorks Samples Database does not have table I used in the example
2) If there is a video tutorial of the same example.

After carefully thinking I decided to build a new set of the scripts for the example which are very similar to the old one as well video tutorial of the same. There was no better place than our SQL in Sixty Second Series to cover this interesting small concept.

Let me know what you think of this video. Here is the updated script.

-- Method 1 : INSERT INTO SELECT
USE AdventureWorks2012
GO
----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Person
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO
---------------------------------------------------------
---------------------------------------------------------
-- Method 2 : SELECT INTO
USE AdventureWorks2012
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Person
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)