How to backup and restore database on Microsoft SQL Server 2005

Introduction

This post will give you a brief introduction of SQL Server backup. As we know, SQL Server backup software is springing up nowadays. Take EaseUS Todo Backup Advanced Server as an example, it’s an all-in-one SQL server backup software for MS SQL Server 2000/2005/2008, exchange server and server 2000/2003/2008, and provides proven database backup solution for Server administrators.

But in the example below, I will show a step-by-step database backup from SQL Server 2005 and restore the database to another SQL Server 2005 Express Edition using Microsoft SQL Server Management Studio.

Step-by-step

Backup a database.

Now I will backup AdventureWorks database on BKKSQL2005 which runs Microsoft SQL Server 2005 to a file.

1. Connect to source server. Open Microsoft SQL Server Management Studio and connect to BKKSQL2005.
2. Right-click on the AdventureWorks database. Select Tasks -> Backup…
   
3. On Back Up Database window, you can configure about backup information. If you’re not familiar these configurations, you can leave default values. Here are some short descriptions.
   1. Database – a database that you want to backup.
   2. Backup type – you can select 2 options: Full and Differential. If this is the first time you backup the database, you must select Full.
   3. Name – Name of this backup, you can name anything as you want.
   4. Destination – the file that will be backup to. You can leave as default. Default will backup to “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
   5. Click OK to proceed backup.
   
4. Wait for a while and you’ll see a pop-up message when backup is finished.
   
5. Browse to the destination, you’ll see a backup file (.bak format) which you can copy to other server for restore in the next step. Default backup directory is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
   

Restore the database.

Next, I will restore the AdventureWorks database from a file that I’ve created above to BK01BIZ001 which runs Microsoft SQL Server Express Edition.

1. Copy the backup file from source server to destination server. I’ve copied into the same directory as source server.
   
2. Connect to destination server. Open Microsoft SQL Server Management Studio Express and connect to BK01BIZ001.
3. Right-click on Databases. Select Restore Database…
   
4. Restore Database window appears. On Source for restore, select From device and click [...] buttton to browse file.
5. On Specify Backup, ensure that Backup media is “File” and click Add.
   
6. On Locate Backup File, select the backup file. This is the backup file that was created in Backup a database section and was copied to this server. Click OK. OK.
   
7. Back to Restore Database window.
   1. On Destination for restore, select “AdventureWorks”.
      Note: If you haven’t added the backup file on Source before (step 4-6), you won’t see the database name on Destination.
   2. On Source for restore, check the box in front of the backup name (in Restore column).
   3. Click OK.
   
8. Wait until restore finish and there’ll be a pop-up message notify.
   
9. Now you’ll see the restored database on the destination SQL Server.
   

SQL Authority News – Download SQL Server Data Type Conversion Chart

December 9, 2012 by pinaldave

Datatypes are very important concepts of SQL Server and there are quite often need to convert them from one datatypes to another datatype. I have seen that deveoper often get confused when they have to convert the datatype. There are two important concept when it is about datatype conversion.

Implicit Conversion: Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function.
Explicit Conversions: Explicit conversions are those conversions that require the CAST or CONVERT function to be specified.

What it means is that if you are trying to convert value from datetime2 to time or from tinyint to int, SQL Server will automatically convert (implicit conversation) for you. However, if you are attempting to convert timestamp to smalldatetime or datetime to int you will need to explicitely convert them using either CAST or CONVERT function as well appropriate parameters.

Let us see a quick example of Implict Conversion and Explict Conversion.

Implicit Conversion:

Explicit Conversion:

You can see from above example that how we need both of the types of conversion in different situation. There are so many different datatypes and it is humanly impossible to know which datatype require implicit and which require explicit conversion. Additionally there are cases when the conversion is not possible as well.

Microsoft have published a chart where the grid displays various conversion possibilities as well a quick guide.

Download SQL Server Data Type Conversion Chart

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

SQL SERVER – Delete Duplicate Records – Rows

March 1, 2007 by pinaldave

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table inexample is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Watch the view to see the above concept in action:

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

SQL SERVER – 2008 – Introduction to Table-Valued Parameters with Example

August 31, 2008 by pinaldave

Table-Valued Parameters is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow steps shown below:

1. Create a table type and define the table structure
2. Declare a stored procedure that has a parameter of table type.
3. Declare a table type variable and reference the table type.
4. Using the INSERT statement and occupy the variable.
5. We can now pass the variable to the procedure.

For Example,

Let’s create a Department Table and pass the table variable to insert data using procedure. In our example we will create Department table and afterward we will query it and see that all the content of table value parameter is inserted into it.

Department:

CREATE TABLE Department(DepartmentID INT PRIMARY KEY,DepartmentName VARCHAR(30)
)GO

1. Create a TABLE TYPE and define the table structure:

CREATE TYPE DeptType AS TABLE(DeptId INT, DeptName VARCHAR(30)
);GO

2. Declare a STORED PROCEDURE that has a parameter of table type:

CREATE PROCEDURE InsertDepartment@InsertDept_TVP DeptType READONLY
AS
INSERT INTO Department(DepartmentID,DepartmentName)SELECT * FROM @InsertDept_TVP;

GO

Important points  to remember :

-  Table-valued parameters must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

-  You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

3. Declare a table type variable and reference the table type.

DECLARE @DepartmentTVP AS DeptType;

4. Using the INSERT statement and occupy the variable.

INSERT INTO @DepartmentTVP(DeptId,DeptName)VALUES (1,'Accounts'),
(2,'Purchase'),
(3,'Software'),
(4,'Stores'),
(5,'Maarketing');

5. We can now pass the variable to the procedure and Execute.

EXEC InsertDepartment @DepartmentTVP;GO

Let’s see if the Data are inserted in the Department Table

Conclusion:

Table-Valued Parameters is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table or passing so many parameters. It helps in using complex business logic in single routine. They reduce Round Trips to the server making the performance better.

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

What is CHECK Constraint?

A CHECK constraint is used to limit the values that can be 


placed in a column. The check constraints are used to 


enforce domain integrity.

(Read more here http://bit.ly/sql-interview)

SQL SERVER – Stored Procedure Optimization Tips – Best Practices

February 16, 2010 by pinaldave

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

• Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcNameAS
SET NOCOUNT ON;--Procedure code hereSELECT column1 FROM dbo.TblTable1-- Reset SET NOCOUNT to OFFSET NOCOUNT OFF;GO

• Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like

SELECT * FROM dbo.MyTable -- Preferred method
-- Instead ofSELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:EXEC dbo.MyProc -- Preferred method
--Instead ofEXEC MyProc -- Avoid this method

• Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

• Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjectsWHERE name = 'MyTable' AND type = 'U')

• Use the sp_executesql stored procedure instead of the EXECUTE statement.
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)DECLARE @Age INT
SET @Age = 25SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' +CONVERT(VARCHAR(3),@Age)EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

• Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
• Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
• Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

BEGIN TRY--Your t-sql code goes hereEND TRYBEGIN CATCH--Your error handling code goes hereEND CATCH

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

SQL SERVER – Stored Procedure and Transactions

June 2, 2010 by pinaldave

I just overheard the following statement – “I do not use Transactions in SQL as I use Stored Procedure“.

I just realized that there are so many misconceptions about this subject. Transactions has nothing to do with Stored Procedures. Let me demonstrate that with a simple example.

USE tempdb
GO-- Create 3 Test TablesCREATE TABLE TABLE1 (ID INT);CREATE TABLE TABLE2 (ID INT);CREATE TABLE TABLE3 (ID INT);GO-- Create SPCREATE PROCEDURE TestSPAS
INSERT INTO TABLE1 (ID)VALUES (1)INSERT INTO TABLE2 (ID)VALUES ('a')INSERT INTO TABLE3 (ID)VALUES (3)GO-- Execute SP
-- SP will error outEXEC TestSP
GO-- Check the Values in TableSELECT *FROM TABLE1;SELECT *FROM TABLE2;SELECT *FROM TABLE3;GO

Now, the main point is: If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors. Well, that does not happen in this case, which proves that Stored Procedure does not only provide just the transactional feature to a batch of T-SQL.

Let’s see the result very quickly.

It is very clear that there were entries in table1 which are not shown in the subsequent tables. If SP was transactional in terms of T-SQL Query Batches, there would be no entries in any of the tables. If you want to use Transactions with Stored Procedure, wrap the code around with BEGIN TRAN and COMMIT TRAN.

The example is as following.
CREATE PROCEDURE TestSPTranAS
BEGIN TRAN
INSERT INTO TABLE1 (ID)VALUES (11)INSERT INTO TABLE2 (ID)VALUES ('b')INSERT INTO TABLE3 (ID)VALUES (33)COMMITGO-- Execute SPEXEC TestSPTran
GO-- Check the Values in TablesSELECT *FROM TABLE1;SELECT *FROM TABLE2;SELECT *FROM TABLE3;GO-- Clean upDROP TABLE Table1DROP TABLE Table2DROP TABLE Table3
GO

In this case, there will be no entries in any part of the table. What is your opinion about this blog post? Please leave your comments about it here.

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

SQL SERVER – Effect of SET NOCOUNT on @@ROWCOUNT

December 2, 2011 by pinaldave

Today I had very interesting experience when I was presenting on SQL Server. While I was presenting the session when I ran query SQL Server Management Studio returned message like (8 row(s) affected) and (2 row(s) affected) etc. After a while at one point, I started to prove usage of @@ROWCOUNT function.

As I was in hurry, I quickly wrote very similar to following example on my screen. (The real query is much complected, but for the purpose of this post I am simplifing it here.

DECLARE @table TABLE(col1 INT IDENTITY(1,1), col2 INT)INSERT INTO @table(col2) VALUES (2932),(142),(352),(342)SET NOCOUNT ON
SELECT * FROM @tableSELECT @@rowcountSET NOCOUNT OFF

Before I execute the script nearly 10 hands went up in audience. This was bit surprise to me as I do not know why they all got alerted. I assumed that there should be something wrong with either project, screen or my display. I quickly asked to one person what is wrong, he pointed out that I will not get right results in @@ROWCOUNT as I am using immediately before SET NOCOUNT ON. When I asked the reason they told me that because any statement when ran right before @@ROWCOUNT resets its value.

Let us stop here for a second – do YOU think same way as well?

If yes, run above code and check your result in following image.

If you believed the same I guess we learned something new. I have no problem confessing I learn something new every single time, I learn something new. However, I was bit surprised with this common myth of SET NOCOUNT ON and @@ROWCOUNT.

Are there any other common myths – if yes, do share here with your comment. If I think it is interesting, I will publish it this blog with due credit and you can win my new SQL Server Interview Questions and Answers book as well.

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