250+ SQL Queries Interview Question Answers

Posted by DBATAG on February 28, 2012 0 comments

SQL Interview Queries Question / Questions Answers

This is a never ending topic and there is no limit to questions that all depends on profile and interviewer.

I am trying my level best to divide questionnaire based on Experience in Query Writing skills

5+ Years of Experience (Expert)
2-5 Years of Experience (Intermediate)
1-2 Years of Experience (Beginner)
Fresher to 1 years of Experience (Fresher)

SQL Queries Interview Questions for Experts (5-10 Years of experience)

Suppose, you are build a application like FACEBOOK and as a business you need to suggest Friend by displaying Friends of Friend’s. So you need to build a query that should return resultset which should have Friend Friend name. Just for reference, this is how friend table looks like,

create table Friend
(
ProfileID uniqueidentifier,
Name varchar(50),
Friend_Name varchar(50)
)
insert Friend values (NEWid(),'RAM', 'Shyam')
insert Friend values ('FFCB96AD-0F16-4A77-B634-3CE1F697A3D1','RAM', 'Tony')
insert Friend values ('FFCB96AD-0F16-4A77-B634-3CE1F697A3D1','RAM', 'Vibha')

insert Friend values (NEWid(),'SHYAM', 'RAM')
insert Friend values ('42A6A0EC-8EE5-4611-92C7-A23B0970B463','SHYAM', 'SAM')
insert Friend values ('42A6A0EC-8EE5-4611-92C7-A23B0970B463','SHYAM', 'Vibha')
insert Friend values ('42A6A0EC-8EE5-4611-92C7-A23B0970B463','SHYAM', 'John')

insert Friend values (NEWid(),'VIBHA', 'RAM')
insert Friend values ('AC40284F-4E54-495A-BF62-9701474C44C0','VIBHA', 'SHYAM')
insert Friend values ('AC40284F-4E54-495A-BF62-9701474C44C0','VIBHA', 'George')
insert Friend values ('AC40284F-4E54-495A-BF62-9701474C44C0','VIBHA', 'TOM')

insert Friend values (NEWid(),'TOM', 'RAM')
insert Friend values ('DE86E5EC-9748-47A0-936E-0BB6BCBCA1A0','TOM', 'DNATAG')
insert Friend values ('DE86E5EC-9748-47A0-936E-0BB6BCBCA1A0','TOM', 'Reddy')

Solution

-- Query Solution 
declare @user varchar(50)
set @user = 'RAM'
SELECT  f1.name,
        f2.friend_name as friend_of_friend
FROM    friend f1,
        friend f2
WHERE   f1.name = @user
AND     f1.friend_name = f2.name
AND     NOT EXISTS
        (SELECT 1 FROM friend f3
         WHERE f3.name = f1.name
         AND   f3.friend_name = f2.friend_name) and f2.friend_name  <>@user

What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

What are the steps you will take to improve performance of a poor performing query?

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are:

SET SHOWPLAN_ALL ON,
SET SHOWPLAN_TEXT ON,
SET STATISTICS IO ON,
SQL Server Profiler,
Windows NT /2000 Performance monitor,
Graphical execution plan in Query Analyzer.

You are being you being assigned a task to move 5 million rows from one server to another using T-SQL with a linked-server. What will you consider to avoid transaction log fill up at destination server?

Will prefer to use SET ROWCOUNT and a while loop to commit data in batches.

What is the optimal Disk configuration for a database server and what RAID configurations would you use if budget is not a constraint?

RAID 1 for the OS / Applications
RAID 1 for the page file
RAID 10 for the Data file (or RAID 5 for few writes)
RAID 1 (or 10) for the transaction log

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.

What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

Read up the following topics in SQL Server books online: Understanding and avoiding blocking, Coding efficient transactions.

What are statistics, under what circumstances they go out of date, how do you update them?

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

If there is significant change in the key values in the index
If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
Database is upgraded from a previous version

Look up SQL Server books online for the following commands:

UPDATE STATISTICS,

STATS_DATE,

DBCC SHOW_STATISTICS,

CREATE STATISTICS,

DROP STATISTICS,

sp_autostats,

sp_createstats,

sp_updatestats

Write SQL query to find the products which have continuous increase in sales every year considering the following Schema and tell which optimized query?

Table Structure

CREATE TABLE PRODUCTS
(
       PRODUCT_ID     INTEGER,
       PRODUCT_NAME   VARCHAR(30)
);
CREATE TABLE SALES
(
       SALE_ID        INTEGER,
       PRODUCT_ID     INTEGER,
       YEAR           INTEGER,
       Quantity       INTEGER,
       PRICE          INTEGER
);

This table, contains the following rows,

Solution

SELECT PRODUCT_NAME
FROM
(
SELECT P.PRODUCT_NAME,
       S.QUANTITY -
       LEAD(S.QUANTITY,1,0) OVER (
                            PARTITION BY P.PRODUCT_ID
                            ORDER BY S.YEAR DESC
                            ) QUAN_DIFF
FROM   PRODUCTS P,
       SALES S
WHERE  P.PRODUCT_ID = S.PRODUCT_ID
)A
GROUP BY PRODUCT_NAME
HAVING MIN(QUAN_DIFF) >= 0;

» Read more…

Deadlock Analysis – READ Deadlock from SQL ERRORLOG

Posted by DBATAG on February 24, 2012 0 comments

How to read DEADLOCK Information in SQL Server ERRORLOG ?

In our previous posts, we learned how to trace dead lock information in SQL Server Errorlog. Once the this information is logged inside SQL Server Logs, then how can we use that information for deadlock analysis.

In case you want to build your test scenario for DEADLOCK, then refer my article, DEADLOCK example with SQL Table and Script.

One we enable trace for DEADLOCKS, then on every event of deadlock, a detailed information about deadlock is being locked in SQL Server ERRORLOG.

In our case, (refer my previous Article)following error message can be seen in SQL Server error log related to deadlock. I am highlighting some of the key information

2012-02-23 22:18:09.12 spid6s      Deadlock encountered …. Printing deadlock information
2012-02-23 22:18:09.12 spid6s      Wait-for graph
2012-02-23 22:18:09.13 spid6s
2012-02-23 22:18:09.13 spid6s      Node:1

2012-02-23 22:18:09.18 spid6s      RID: 2:1:184:7                 CleanCnt:2 Mode:X Flags: 0×3
2012-02-23 22:18:09.18 spid6s       Grant List 0:
2012-02-23 22:18:09.18 spid6s         Owner:0x000000008017CE40 Mode: X        Flg:0×40 Ref:0 Life:02000000 SPID:57 ECID:0 XactLockInfo: 0x0000000084E79440
2012-02-23 22:18:09.19 spid6s         SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 6
2012-02-23 22:18:09.19 spid6s         Input Buf: Language Event:
– Step 3 update Sales Person Table and update the Sales Region for SalesPersonID 4
– Session 1 (Query Window 1) — Statement 3

BEGIN TRAN
update SalesPerson set Region=’Beijing’ where SalesPersonID = 4;

2012-02-23 22:18:09.19 spid6s      Requested by:
2012-02-23 22:18:09.19 spid6s        ResType:LockOwner Stype:’OR’Xdes:0x0000000084E5F950 Mode: U SPID:53 BatchID:0 ECID:0 TaskProxy:(0x0000000084EC2540) Value:0x85005c80 Cost:(0/120)
2012-02-23 22:18:09.19 spid6s
2012-02-23 22:18:09.19 spid6s     Node:2

2012-02-23 22:18:09.19 spid6s      RID: 2:1:157:3                 CleanCnt:2 Mode:X Flags: 0×3
2012-02-23 22:18:09.19 spid6s       Grant List 0:
2012-02-23 22:18:09.19 spid6s         Owner:0x000000008017BF40 Mode: X        Flg:0×40 Ref:0 Life:02000000 SPID:53 ECID:0 XactLockInfo: 0x0000000084E5F990
2012-02-23 22:18:09.19 spid6s        SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 6
2012-02-23 22:18:09.19 spid6s         Input Buf: Language Event:
– Step 4 update Orders Table and update the Sales OrderDate for Order 108
– Session 2 (Query Window 2) — Statement 4

BEGIN TRAN
update Orders set OrderDate=’2011-11-11 11:11:11.000′ where OrderID = 108;
2012-02-23 22:18:09.19 spid6s      Requested by:
2012-02-23 22:18:09.19 spid6s        ResType:LockOwner Stype:’OR’Xdes:0x0000000084E79400 Mode: U SPID:57 BatchID:0 ECID:0 TaskProxy:(0x0000000084DC2540) Value:0x8017bec0 Cost:(0/196)
2012-02-23 22:18:09.34 spid6s
2012-02-23 22:18:09.34 spid6s      Victim Resource Owner:
2012-02-23 22:18:09.34 spid6s       ResType:LockOwner Stype:’OR’Xdes:0x0000000084E5F950 Mode: U SPID:53 BatchID:0 ECID:0 TaskProxy:(0x0000000084EC2540) Value:0x85005c80 Cost:(0/120)
2012-02-23 22:18:09.36 spid23s     deadlock-list
2012-02-23 22:18:09.36 spid23s      deadlock victim=process4d3048
2012-02-23 22:18:09.36 spid23s       process-list
2012-02-23 22:18:09.37 spid23s       process id=process4d3048 taskpriority=0 logused=120 waitresource=RID: 2:1:184:7 waittime=459 ownerId=356117 transactionname=user_transaction lasttranstarted=2012-02-23T22:17:56.510 XDES=0x84e5f950 lockMode=U schedulerid=3 kpid=10368 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=3 lastbatchstarted=2012-02-23T22:18:08.860 lastbatchcompleted=2012-02-23T22:17:56.590 clientapp=Microsoft SQL Server Management Studio – Query hostname=DBATAG hostpid=968 loginname=DBATAG isolationlevel=read committed (2) xactid=356117 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2012-02-23 22:18:09.37 spid23s         executionStack
2012-02-23 22:18:09.37 spid23s          frame procname=adhoc line=6 stmtstart=58 sqlhandle=0x02000000e49d262c4e7a570ab8a24babad5eb2842e2e869c
2012-02-23 22:18:09.37 spid23s     UPDATE [Orders] set [OrderDate] = @1 WHERE [OrderID]=@2
2012-02-23 22:18:09.37 spid23s          frame procname=adhoc line=6 stmtstart=276 sqlhandle=0x0200000022f3a72386c7e56f1e90e8cd21461721a3e0a67a
2012-02-23 22:18:09.37 spid23s     update Orders set OrderDate=’2011-11-11 11:11:11.000′ where OrderID = 108;
2012-02-23 22:18:09.37 spid23s         inputbuf
2012-02-23 22:18:09.37 spid23s     — Step 4 update Orders Table and update the Sales OrderDate for Order 108
2012-02-23 22:18:09.37 spid23s     — Session 2 (Query Window 2) — Statement 4
2012-02-23 22:18:09.37 spid23s     BEGIN TRAN
2012-02-23 22:18:09.37 spid23s     update Orders set OrderDate=’2011-11-11 11:11:11.000′ where OrderID = 108;
2012-02-23 22:18:09.37 spid23s        process id=process43f048 taskpriority=0 logused=196 waitresource=RID: 2:1:157:3 waittime=7212 ownerId=353074 transactionname=user_transaction lasttranstarted=2012-02-23T22:17:18.967 XDES=0x84e79400 lockMode=U schedulerid=1 kpid=6448 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=4 lastbatchstarted=2012-02-23T22:18:02.093 lastbatchcompleted=2012-02-23T22:17:49.167 clientapp=Microsoft SQL Server Management Studio – Query hostname=DBATAG hostpid=968 loginname=DBATAG isolationlevel=read committed (2) xactid=353074 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2012-02-23 22:18:09.37 spid23s         executionStack
2012-02-23 22:18:09.37 spid23s          frame procname=adhoc line=6 stmtstart=58 sqlhandle=0x020000005d7ef62e1c25a6069814688d37c8893e368f37ac
2012-02-23 22:18:09.37 spid23s     UPDATE [SalesPerson] set [Region] = @1 WHERE [SalesPersonID]=@2
2012-02-23 22:18:09.37 spid23s          frame procname=adhoc line=6 stmtstart=294 sqlhandle=0x02000000b9aa5a33ee4c7af1c2eeb2cd8572c35f2638e203
2012-02-23 22:18:09.37 spid23s     update SalesPerson set Region=’Beijing’ where SalesPersonID = 4;
2012-02-23 22:18:09.37 spid23s         inputbuf
2012-02-23 22:18:09.37 spid23s     — Step 3 update Sales Person Table and update the Sales Region for SalesPersonID 4
2012-02-23 22:18:09.37 spid23s     — Session 1 (Query Window 1) — Statement 3
2012-02-23 22:18:09.37 spid23s     BEGIN TRAN
2012-02-23 22:18:09.37 spid23s     update SalesPerson set Region=’Beijing’ where SalesPersonID = 4;
2012-02-23 22:18:09.37 spid23s       resource-list
2012-02-23 22:18:09.37 spid23s        ridlock fileid=1 pageid=184 dbid=2 objectname=tempdb.dbo.Orders id=lock8012e600 mode=X associatedObjectId=1224979099300986880
2012-02-23 22:18:09.37 spid23s         owner-list
2012-02-23 22:18:09.37 spid23s          owner id=process43f048 mode=X
2012-02-23 22:18:09.37 spid23s         waiter-list
2012-02-23 22:18:09.37 spid23s          waiter id=process4d3048 mode=U requestType=wait
2012-02-23 22:18:09.37 spid23s       ridlock fileid=1 pageid=157 dbid=2 objectname=tempdb.dbo.SalesPerson id=lock826ba480 mode=X associatedObjectId=1297036693379874816
2012-02-23 22:18:09.37 spid23s         owner-list
2012-02-23 22:18:09.37 spid23s          owner id=process4d3048 mode=X
2012-02-23 22:18:09.37 spid23s         waiter-list
2012-02-23 22:18:09.37 spid23s          waiter id=process43f048 mode=U requestType=wait

Interpretation from above mentioned Error Information

SPID 53 is running an update statement “UPDATE [Orders] set [OrderDate] = @1 WHERE [OrderID]=@2″. It holds an U lock on ridlock fileid=1 pageid=184 dbid=2 objectname=tempdb.dbo.Orders id=lock8012e600 mode=X associatedObjectId=1224979099300986880. This lock is blocking SPID 57, which is waiting to acquire an X lock on that key resource.
SQL Server has chosen SPID 53 as the deadlock victim, it’s batch will cancelled and it’s transaction will be rolled back.

To make you more clear, graphically, I enabled the profiler and capture the Deadlock graph, which given me this information.

If I would have enabled the profiler to capture Deadlock Graph then this what I am going to get

This summaries, how can we READ Deadlock from SQL ERRORLOG for Deadlock Analysis.

Example of Deadlock | SQL Server Deadlock Script

Posted by DBATAG on February 23, 2012 0 comments

IS there a way I can stimulate a deadlock in SQL Server, so that I can use that as an example of deadlock

Let’s take an example, where we will result / cause a dead lock to SQL Serve?

To stimulate the Deadlock, let’s create two tables which we are going to use as an example of Deadlock

STEP 1 – Create Sample Tables which we will use Deadlock Example

-- Sample Table, which will track individual Orders entered by 
-- Various Sales Persons
create table Orders
(
OrderID int,
OrderDate datetime,
Amount money,
SalesPersonID int
)
go
-- Sample Table, which will track all individual Sales Persons details
create table SalesPerson
(
SalesPersonID int,
Name varchar(100),
Region varchar(100)
);
-- Insert Date in SalesPerson
insert SalesPerson Values (1,'Dave Lawlor', 'California');
insert SalesPerson Values (2,'Mark F', 'Atlanta');
insert SalesPerson Values (3,'Vivek Pundit', 'India');
insert SalesPerson Values (4,'Chi Li Hu', 'China');
insert SalesPerson Values (5,'Mat Ross', 'London');

-- Insert Date in Orders Table
insert Orders values (101,'2011-01-01', 343,1)
insert Orders values (102,'2011-02-01', 35,2)
insert Orders values (103,'2011-03-01', 33,3)
insert Orders values (104,'2011-04-01', 445,1)
insert Orders values (105,'2011-05-01', 865,3)
insert Orders values (106,'2011-06-01', 23,5)
insert Orders values (107,'2011-07-01', 673,1)
insert Orders values (108,'2011-01-01', 343,1)
insert Orders values (109,'2011-02-01', 135,2)
insert Orders values (110,'2011-03-01', 373,3)
-- select Table Data
select * from Orders;
select * from SalesPerson;

STEP 2 – Open TWO new Query Windows where we will run 4 update statements

1 – Execute the following Query on Query Windows 1

-- Step 1 update a Order Table and update the Sales Person Id for Order 108
-- Session 1 (Query Window 1) -- Statement 1

BEGIN TRAN
update Orders set SalesPersonID=4 where OrderID = 108;

2 – Execute the following Query on Query Windows 2

-- Step 2 update Sales Person Table and update the Sales Person Name for SalesPersonID 4
-- Session 2 (Query Window 2) -- Statement 2

BEGIN TRAN
update SalesPerson set Name='Li Hu Chu' where SalesPersonID = 4;

3 – Execute the following Query on Query Windows 1

-- Step 3 update Sales Person Table and update the Sales Region for SalesPersonID 4
-- Session 1 (Query Window 1) -- Statement 3

BEGIN TRAN
update SalesPerson set Region='Beijing' where SalesPersonID = 4;

4 – Execute the following Query on Query Windows 2

-- Step 4 update Orders Table and update the Sales OrderDate for Order 108
-- Session 2 (Query Window 2) -- Statement 4

BEGIN TRAN
update Orders set OrderDate='2011-11-11 11:11:11.000' where OrderID = 108;

OUTPUT at STEP 4

Once you execute the 4th statement, you will get the following error message

Msg 1205, Level 13, State 45, Line 5

Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

NOTE : Currently this DEAD LOCK Error is displayed at session level, I mean to say either at query window or at user front end. But this information in not get logged either in SQL Server Error Logs or Event Viewer by default.

As a preventive. we should keep an active eye on deadlocks and take necessary actions to avoid DEADLOCK issues.

But if these events are not getting logged anywhere, there how we are going to check these?, So in next topic I am going to discuss, how to track DEADLOCK information, so that these can be analyzed.

Please check My previous article, "How to Keep track of DEADLOCK in SQL Server", where we learn how to trace DEADLOCK in SQL Server

How to backup SQL table ?

Posted by DBATAG on February 22, 2012 0 comments

Backup SQL table, have you ever tried to backup a single SQL table inside a database? Let’s see How to backup SQL table | SQL Table Backup Restore

DOES SQL Server supports table level backups ?

Backup Types are dependent on SQL Server Recovery Model. Every recovery model lets you back up whole or partial SQL Server database or individual files or filegroups of the database. Table-level backup cannot be created, there is no such option. BUT there is a workaround for this

Taking backup of SQL Server table possible in SQL Server. There are various alternative ways to backup a table in sql SQL Server

BCP (BULK COPY PROGRAM)
Generate Table Script with data
Make a copy of table using SELECT INTO
SAVE Table Data Directly in a Flat file
Export Data using SSIS to any destination

Let’s see how we can use these methods to take table backup in sql server

To make it more clear, let’s take example, we want to backup SQL table named "Person.Contact", which resides in SQL Server AdventureWorks sample database, which has 19972 records and table size is 6888 KB

Method 1 – Backup sql table using BCP (BULK COPY PROGRAM)

To backup a SQL table named "Person.Contact", which resides in SQL Server AdventureWorks, we need to execute following script, which

-- SQL Table Backup
-- Developed by DBATAG, www.DBATAG.com
DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = 'AdventureWorks.Person.Contact' --  Table Name which you want to backup
SET @file = 'C:\MSSQL\Backup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) --  Replace C:\MSSQL\Backup\ to destination dir where you want to place table data backup
+ '.dat'
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

OUTPUT

» Read more…

SQL 2008 Restore Database

Posted by DBATAG on February 20, 2012 0 comments

STEP by STEP GUIDE for SQL 2008 Restore Database from a FULL BACKUP | Differential backup | Transaction Log Backup and Point in Time Restore

For better understanding, let’s assume, we have following backup files, where we have FULL backups , Differential backups and Transaction LOG backups.

DAY	Date	Type Of Backup
Sunday	2012-02-12	Full Database Backup
Monday	2012-02-13	Transaction Log Backup
Tuesday	2012-02-14	Transaction Log Backup
Wednesday	2012-02-15	Differential Database Backup
Thursday	2012-02-16	Transaction Log Backup
Friday	2012-02-17	Transaction Log Backup
Saturday	2012-02-18	Transaction Log Backup
Sunday	2012-02-19	Full Database Backup

as shown below

where BACKUP is schedule to run on close of business day which after 12:00 midnight.

To make it more visualize and clear understanding, I have created a test table named Backup_TEST and inserted a record on daily basis, which means

record 1 was inserted 2012-02-11 during day and this data was backup up on 2012-02-12 12:07 AM
record 2 was inserted 2012-02-12 during day and this data was backup up on 2012-02-13 12:07 AM and so on…

Let’s PREFORM Restore Database for SQL Server 2008 environment considering various CASES

NOTE – These instructions and STEP remain same across all SQL Server environments including SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012

CASE 1 - RESTORE database in SQL 2008 from a FULL Database Backup

In this case, let’s assume that we want to restore a database back to state which was on 2012-02-12. Thus in this case, we need to perform a single restore that is full backup database backup which was created on 2012-02-12 12:07 AM and backup media file name was “CRM-12Feb2012.BAK”

STEP by STEP GUIDE to RESTORE SQL Database from CRM-12Feb2012.BAK file

Connect to SQL Server Instance using SQL Server Management Studio
Right click on database >> Select Restore Database
In database Restore Database Property Page
- Enter Database NAME, this could be same as previous or different (Restore Database AS ”)
- Locate a backup file
- Click Options, in case you want to move database files to some other location, in my case, I am not changing this.
- Click OK, this will restore a database for you

In case you, want to schedule this restore during off hours, please click SCRIPT Button, which is the TOP, this will create a TSQL Statement for this restore operation.

Instead of GUI, we can use the following TSQL Script to complete this Restore Operation.

RESTORE DATABASE [CRM] FROM  DISK = N'C:\MSSQL\Backup\CRM-12Feb2012.BAK'
WITH  FILE = 1,
NOUNLOAD,  STATS = 1 -- this will show you current status in %
GO

Script OUTPUT

Verification of Restore Data – Once the Restore Database operation is completed from a Full backup following data is returned in select statement, only one record, which is expected.

This is HOW we restore sql database from a full database backup.

CASE 2 - RESTORE Differential backup

» Read more…

SQL Server Hardware Requirements

Posted by DBATAG on February 19, 2012 0 comments

Minimum SQL Server Hardware Requirements

SQL Server	Operating System	CPU Processor	Memory RAM	Storage HDD
SQL Server 2012	Windows Vista SP 2 Windows 7 SP1 Windows Server 2008 SP1	1.0 GHz	512 MB	811 MB
SQL Server 2008 R2	Windows Vista SP 2 Windows XP SP2 Windows Server 2003 SP2	1.0 GHz	512 MB	711 MB
SQL Server 2008	Windows Server 2000 Windows XP SP2	1.0 GHz	256 MB	280 MB
SQL Server 2005	Windows Server 2000 Windows XP SP2	600 MHz	192 MB	280 MB
SQL Server 2000	Windows NT Workstation 4.0 Windows 98 Windows Me	166 MHz	128 MB	270 MB

Minimum SQL Server Software Requirements

SQL Server	Software Requirement
SQL Server 2012	.NET Framework 4 Microsoft Internet Explorer 7 Windows Power Shell Windows Installer
SQL Server 2008 R2	.NET Framework 3.5 SP1 Microsoft Internet Explorer 6 SP1 Windows Installer 4.5
SQL Server 2008	.NET Framework 3.5 SP1 Microsoft Internet Explorer 6 SP1
SQL Server 2005	Microsoft Internet Explorer 6.0 SP1 Microsoft .NET Framework 2.0
SQL Server 2000	Microsoft Internet Explorer 5.0

SQL Server Recommended Hardware

What should be the recommended Hardware Configuration for a new SQL Server ?

If you are looking for buying a new server for your application, make sure you have answers to the following questionnaire to make you have captured all requirement,while planning for a new Server.

Physical SQL Server Hardware Configuration Planning Questionnaire