Thursday, December 20, 2012

SQL Statements Overview – DDL & DML

SQL statements are categorized into two categories: DDL statements & DML statements. DDL is abbreviation for data definition language while DML is abbreviation for data manipulation language. Data definition language statement is used to define the data structure of database, in addition to this; it can modify the structure of the table or other objects like view, stored procedure and many more. Data manipulation language statement is used to retrieve data or work with data in SQL server. Both of these categories contain several statements than I have present in this article.

Data Definition Language (DDL): You can use this statement for create, drop, truncate, and alter data structure in an instance of SQL server database. Some of the popular DDL statements are described below.

Create Statement: This statement is used for creating a new entity; like table, view, database, index, and many more.

CREATE TABLE <table name> ( <attribute name 1> <data type 1>, ...<attribute name n> <data type n>);

Alter Statement: This statement is used for modifying the definition of a an entity; like table, view, database, index, and many more.

ALTER TABLE <table name> ADD CONSTRAINT <constraint name> PRIMARY KEY (<attribute list>);

Drop Statement: You can use this statement to remove an existing entity; like table, view, database, index, and many more.

DROP TABLE <table name>;

Truncate Statement: This statement removes all the rows from the table. It is similar to the DELETE statement with 'Where' clause.

TRUNCATE TABLE <table name>;

Note: In all above examples; I have taken table as an entity.

Data Manipulation Language (DML): Basically this statement is used to work with data in the SQL server database. Some of the popular DML statements are as follows:

Insert Statement: As the name suggests; it is used to insert one or more rows in the table.

INSERT INTO <table name> VALUES (<value 1>, ... <value n>);

Update statement: You can use this statement to change the data from in a table.

UPDATE <table name> SET <attribute> = <expression> WHERE <condition>;

Delete Statement: As I have discussed above; it is similar to the truncate statement but with 'Where' clause. It removes one or more rows from the table.

DELETE FROM <table name> WHERE <condition>;

Wednesday, December 12, 2012

Oh My God! SQL Server Backup is Corrupt too

It is a dark secret that once in a while every database administrator face SQL server backup corruption issue. Some of them talk on this topic publicly & some does not but it is true. I have seen in forums & newsletters where; people talk freely not on SQL data file corruption only but SQL backup file corruption too.

Here is a statement given by Paul Randlal, who is the CEO of

You don't have a backup until you have done a restore

Meaning: The meaning of statement is that if you have SQL server database backup that is corrupt due to any reason, you can’t restore it over original database but you can check the validity of the backup that is going to help you in disaster recovery.

Check integrity of database before backup: It is a very simple & important talk of SQL server DBA. You can do this by running DBCC consistency checks before taking a full backup of your database otherwise you may be backing up a corrupt database.

Check the Integrity of Backup itself: You can do this by using 'WITH CHECKSUM' option along with backup command. Backup command with Checksum option checks the checksum for whole backup steam and save it into the backup. It ensures you that your database backup is not corrupt.

So there are a lot of things that you can do to minimize the change of backup corruption. Now the main point comes in picture, if you don't have followed any of the above suggestions before making a backup of your database and backup is corrupt.

How to Restore Database? Many people are got frustrated or tensed when they face this situation but I will suggest you keep your mind cool. You can do one thing, allow restore to ignore error. There is an option in restore that is known as continue-after-error. It enables you to restore the backup even if corruption is found in the backup stream. After this; your database may go inconsistent state. You can use DBCCCheckDB command to repair SQL database. DBCC CheckDB offers three repair options: repair_fast, repair_rebuild, and repair_allow_data_loss. However, if you are still not able to restore your database then it is the best time to use any 3rd party backup repair program.

Summary: Make sure you have validated the database before backup & backup after you have finished with backup. Also, try to make more than one backup if first backup got corrupted then you will have another option for restore & recovery.