How to recover deleted data from SQL Server

Recover DELETED DATA FROM SQL Server

SQL Server Portal

In all my years of working SQL server, one of the most commonly asked questions has always been “How can we recover deleted record?”

Now, it is very easy to recover deleted data from your SQL server 2005 or above.(Note: This script can recover following data types & compatible with CS collation).

• image
• text
• uniqueidentifier
• tinyint
• smallint
• int
• smalldatetime
• real
• money
• datetime
• float
• sql_variant
• ntext
• bit
• decimal
• numeric
• smallmoney
• bigint
• varbinary
• varchar
• binary
• char
• timestamp
• nvarchar
• nchar
• xml
• sysname

Let me explain this issue demonstrating simple example.

Now, you need to create this procedure to recover your deleted data

Explanation:

How does it work? Let’s go through it step by step. The process requires seven easy steps:

Step-1:

We need to get the deleted records from sql server. By using the standard SQL Server function fn_blog, we can easily get all transaction log (Including deleted data. But…

View original post 866 more words

Advertisement

How to recover modified records from SQL Server without Backup

Recover Updated Data from SQL Server without BackUp

SQL Server Portal

In my previous post “How to recover deleted records from SQL Server”, I received requests to “develop a mechanism that can recover the modified records from SQL Server”

Now, it is very easy to recover modified data from your SQL server 2005 or above.(Note: This script can recover following data types & compatible with CS collation).

• tinyint
• smallint
• int
• bigint
• bit
• char
• varchar
• nchar
• nvarchar
• datetime
• smalldatetime
• money
• smallmoney
• decimal
• numeric
• real
• float
• binary
• varbinary
• uniqueidentifier

Let me explain this issue demonstrating simple example.

Now, you need to create this procedure to recover your modified data

Estimated execution time for this stored procedure is 1-3 minutes.

How does it work is explained in Article -2.

Appreciate your feedback on my posts. Please do comment.

View original post

User Restriction in SQL Server

User Restriction in SQL Server

Let’s see how we can restrict users to access only specific objects in Sql Server.

Let’s Create a Login

1. Connect to the SQL Server as SA user.
2. Goto Security–>Login–>New Login

3. In the “General” Tab, Enter the Login Name. Select SQL Server Authentication
4. Enter Password and Confirm Password.
5. Uncheck “Enforce Password Policy”
6. Click OK

7. Now we have Created a Login.

Next step is now we are going to Map this Login to a database user.

Goto the Database where you want to give access to the created Login.\

1. Expand the Database. Select Security–>New–>User.

2. Enter the Username. It is not necessary to have same as that of login name. But just to avoid confusion, I am
creating the username as same as Login name.
3. In the Login Name, Click on the browse button, and select the login that you have created just now.

4. Goto Securables tab.
5. Click on Search
6. Select “All Object of the Types”

7. Select “Tables”.

8. Click OK.
9. Select the table that you want to give the access.
10. In the below tab, Tick the “GRANT” option for “SELECT”.
This means, you are providing the user to view the records in the particular table.
[In the same window, you can select whichever tables that you want to provide access and also specify access levels like “select”, “Insert”, “update” and “delete”]

11. If you want to restrict the user even to the column level, Click on “Column Properties” and Select the Columns that you want to
give the access and give OK.

12. Click OK on the database user window.

Now we have done with the creating database user.
In short, we have created a user who have only access to one column named “C1” in the table “dbo.a” in the database “AdventureWorks2008R2”.

Now lets check this user access that we have created now.
Disconnect the SQL Server connection in the object explorer. Connect to the Sql Server using the login we have created just now.

Try to access some other database that the user doesn’t have access to.
It should throw error message as “The Database is not accessible”.

Expand the database “AdventureWorks2008R2” in object explorer. Expand “Tables”.
As you see, the only table dbo.a is listed out. because the user have access only to that particular table.

Run the Query “Select * from dbo.a”

You will get error message as
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column ‘c2’ of the object ‘a”, database ‘AdventureWorks2008R2’, schema ‘dbo’.
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column ‘c3’ of the object ‘a”, database ‘AdventureWorks2008R2’, schema ‘dbo’.

If you run “Select c1 from dbo.A” then it will work. because we did a column level restriction for this user.

Also If you try to insert, update, or delete, then also you should get a permission denied error because we have not given permission for the user for those statements.

Cascade Delete in SQL Server

Recently I came up with a scenario to delete the records from a table.
I thought its simple. I just wrote a simple delete statement like

DELETE TABLE TableA;

But it throws me an error message as

The DELETE statement conflicted with the REFERENCE constraint "FK__TableA1__AID__2D47B39A". 
The conflict occurred in database "AdventureWorks2008R2", table "dbo.TableA1", column 'AID'

So what should I do? I have to delete the associated records from the child tables before attempting a Delete on the parent table.

So I just deleted the records from Child table as,

DELETE TABLE TableA1;

Now again I attempted to delete the records from the parent table.

DELETE TABLE TableA;

Again got the same error message.

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__TableA2__AID__2F2FFC0C". 
The conflict occurred in database "AdventureWorks2008R2", table "dbo.TableA2", column 'AID'.
The statement has been terminated.

This time on a different Table – TableA2

So you can view the dependencies on a table by using the Post mentioned HERE
and you can manually write the delete statements for the Child tables as per the relation.

If it has 3-4 Child tables, then it is okay to write the delete scripts manually.

What if you have a table associate with more than 10 child tables.
It involves lot of manual work to delete one by one to delete the final data from the parent table.

So lets see how to simplify this using a Stored Procedure.

I just started searching whether anyone have simplified these kind of tasks and came across lot of solutions.

One interesting thread in StackOverFlow is HERE

So Before Going to run the SP, first create the sample tables and records for reproducing this scenario.

CREATE Table TableA
(
ID int,
Name varchar(20),
Primary Key(ID)
)

Create Table TableA1
(
ID int,
Name Varchar(20),
AID int Foreign Key references TableA(ID)
)

Create Table TableA2
(
ID int,
Name varchar(20),
AID int Foreign key references TableA(ID)
)

INSERT into TableA values(1, 'A')
INSERT into TableA values(2, 'B')
INSERT into TableA values(3, 'C')

Insert into TableA1 VALUES (1, 'A', 1)
Insert into TableA1 VALUES (2, 'A', 2)
Insert into TableA1 VALUES (3, 'A', 3)

Insert into TableA2 VALUES (1, 'A', 1)
Insert into TableA2 VALUES (2, 'A', 2)
Insert into TableA2 VALUES (3, 'A', 3)

Now Create the required Functions and procedures by running the following script.

IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL
    DROP FUNCTION dbo.udfGetFullQualName;
GO

CREATE FUNCTION dbo.udfGetFullQualName
(@ObjectId INT)
RETURNS VARCHAR (300)
AS
BEGIN
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON
    DECLARE @schema_id AS BIGINT;
    SELECT @schema_id = schema_id
    FROM   sys.tables
    WHERE  object_id = @ObjectId;
    RETURN '[' + SCHEMA_NAME(@schema_id) + '].[' + OBJECT_NAME(@ObjectId) + ']';
END
GO

--============ Supporting Function dbo.udfGetOnJoinClause
IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL
    DROP FUNCTION dbo.udfGetOnJoinClause;
GO

CREATE FUNCTION dbo.udfGetOnJoinClause
(@fkNameId INT)
RETURNS VARCHAR (1000)
AS
BEGIN
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON
    DECLARE @OnClauseTemplate AS VARCHAR (1000);
    SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND ';
    DECLARE @str AS VARCHAR (1000);
    SET @str = '';
    SELECT @str = @str + REPLACE(REPLACE(REPLACE(REPLACE(@OnClauseTemplate, '<@pTable>', OBJECT_NAME(rkeyid)), '<@pCol>', COL_NAME(rkeyid, rkey)), '<@cTable>', OBJECT_NAME(fkeyid)), '<@cCol>', COL_NAME(fkeyid, fkey))
    FROM   dbo.sysforeignkeys AS fk
    WHERE  fk.constid = @fkNameId; --OBJECT_ID('FK_ProductArrearsMe_ProductArrears')
    RETURN LEFT(@str, LEN(@str) - LEN(' AND '));
END
GO

--=========== CASECADE DELETE STORED PROCEDURE dbo.uspCascadeDelete
IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL
    DROP PROCEDURE dbo.uspCascadeDelete;

GO
CREATE PROCEDURE dbo.uspCascadeDelete
@ParentTableId VARCHAR (300), @WhereClause VARCHAR (2000), @ExecuteDelete CHAR (1)='Y', --'N' IF YOU NEED DELETE SCRIPT
@FromClause VARCHAR (8000)='', @Level INT=0 -- TABLE NAME OR OBJECT (TABLE) ID (Production.Location) WHERE CLAUSE (Location.LocationID = 7) 'Y' IF WANT TO DELETE DIRECTLY FROM SP,  IF LEVEL 0, THEN KEEP DEFAULT
AS 
SET NOCOUNT ON;
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON

DECLARE @DebugMsg AS VARCHAR (4000), 
@DebugIndent AS VARCHAR (50);
SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> ';
IF ISNUMERIC(@ParentTableId) = 0
    BEGIN 
        IF CHARINDEX('.', @ParentTableId) = 0
            SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']');
        ELSE
            SET @ParentTableId = OBJECT_ID(@ParentTableId);
    END
IF @Level = 0
    BEGIN
        PRINT @DebugIndent + ' **************************************************************************';
        PRINT @DebugIndent + ' *** Cascade delete ALL data from ' + dbo.udfGetFullQualName(@ParentTableId);
        IF @ExecuteDelete = 'Y'
            PRINT @DebugIndent + ' *** @ExecuteDelete = Y *** deleting data...';
        ELSE
            PRINT @DebugIndent + ' *** Cut and paste output into another window and execute ***';
    END
DECLARE @CRLF AS CHAR (2);
SET @CRLF = CHAR(13) + CHAR(10);
DECLARE @strSQL AS VARCHAR (4000);
IF @Level = 0
    SET @strSQL = 'SET NOCOUNT ON' + @CRLF;
ELSE
    SET @strSQL = '';
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + '''';
IF @ExecuteDelete = 'Y'
    EXECUTE (@strSQL);
ELSE
    PRINT @strSQL;
DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
    FOR SELECT DISTINCT constid AS fkNameId, -- constraint name
                        fkeyid AS cTableId
        FROM   dbo.sysforeignkeys AS fk
        WHERE  fk.rkeyid <> fk.fkeyid -- DO NOT HANDLE self referencing tables!!!
               AND fk.rkeyid = @ParentTableId;
OPEN curs_children;
DECLARE @fkNameId AS INT, 
@cTableId AS INT, 
@cColId AS INT, 
@pTableId AS INT, 
@pColId AS INT;
FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId; --, @cColId, @pTableId, @pColId
DECLARE @strFromClause AS VARCHAR (1000);
DECLARE @nLevel AS INT;
IF @Level = 0
    BEGIN
        SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId);
    END
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @strFromClause = @FromClause + @CRLF + '      INNER JOIN ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + '       ON ' + dbo.udfGetOnJoinClause(@fkNameId);
        SET @nLevel = @Level + 1;
        EXECUTE dbo.uspCascadeDelete @ParentTableId = @cTableId, @WhereClause = @WhereClause, @ExecuteDelete = @ExecuteDelete, @FromClause = @strFromClause, @Level = @nLevel;
        SET @strSQL = 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + @strFromClause + @CRLF + 'WHERE   ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + '     Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
        FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId;
    --, @cColId, @pTableId, @pColId
    END
IF @Level = 0
    BEGIN
        SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE''' + @CRLF;
        SET @strSQL = @strSQL + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
    END
CLOSE curs_children;
DEALLOCATE curs_children;
GO

----------###########################################################################---------------------------
----------###########################################################################---------------------------
----------###########################################################################---------------------------
IF OBJECT_ID('dbo.CascadeDelete') IS NOT NULL
  DROP Procedure dbo.CascadeDelete;
GO
----------###########################################################################---------------------------
----------###########################################################################---------------------------
----------###########################################################################---------------------------

Now the required Functions and Stored Procedures has been created and we are ready to go.

Now to delete the data from the parent table without any condition,
Run the following script

EXEC uspCascadeDelete 'TableA', '1=1'

You can also use condition in your delete statements
The following delete statements can be easily re written using

DELETE FROM TABLEA WHERE ID = 1

as

EXEC uspCascadeDelete 'TableA', 'ID=1'

Finding Out Object Dependencies in SQL Server

Lets see how to find the object Dependencies in SQL Server using SSMS.
We have a simple inbuilt option in SSMS to view the object dependencies in SQL Server.
Before that, Why we need to find out the object dependencies in SQL Server.

Suppose we have a table called Table A and if we are going to delete some rows
out of that table, then we may be having the foreign key constraint associated with Child Tables.
It will throw you an error related to foreign key constraint and you cant be able to proceed further.

For Example I am using AdventureWorks database and I am trying to delete the data from the Person Table.

DELETE FROM [AdventureWorks2008R2].[Person].[Person]

You will be getting the following error from the Sql Server

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_EmailAddress_Person_BusinessEntityID". 
The conflict occurred in database "AdventureWorks2008R2", table "Person.EmailAddress", column 'BusinessEntityID'.
The statement has been terminated.

Now from this message it is clear that there is foreign key reference with table Person.EmailAddress.

So you can’t be able to delete data from Person.Person without deleting the data from Person.EmailAddress
But now we are not sure that how many objects [tables/views] has a dependency on the table Person.Person

So to find out this, we have simple provision in Sql Server.

Open the Object Explorer in SSMS.
Go to the Database that you want and select the Table you want to view dependencies.
Right Click on that Table [Person].[Person] and Select “View Dependencies”

Now this will open a window which will show you the Objects that depends on the table Person.Person.

As you see the Tables BusinessEntityContact, Email Address, Employee and IUPerson Trigger and some other Views and Tables depends on the object “Person”.

So you can’t be able to delete the rows in Person without Deleting the data in the dependent Objects.
Now Select the Option, “Objects on which [Person] Depends”

This will list out the Objects that “Person” depends on.

SQL RANKING FUNCTIONS

Lets see some of the Ranking functions in SQL.

 

RANK()

Returns the rank of each row within the result set

USE AdventureWorks2008R2;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER
    (ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;

The Result Set for the above Query will be,

ProductID	Name	LocationID	Quantity	Rank
495	Paint – Blue	3	49	1
494	Paint – Silver	3	49	1
493	Paint – Red	3	41	3
496	Paint – Yellow	3	30	5
492	Paint – Black	3	17	8
492	Paint – Black	4	14	9
494	Paint – Silver	4	12	10
496	Paint – Yellow	4	25	6
493	Paint – Red	4	24	7
495	Paint – Blue	4	35	4

If two or more rows tie for a rank, each tied rows receives the same rank.

As you see the first two rows have the same quantity. So the Rank is also Same.
But the third row got the rank as 3. It means, the Rank function will skip the Rank 2,
if two rows got the same rank.
Therefore, the RANK function does not always return consecutive integers

For all the Ranking functions, you can always use Partition By to segregate the result set.
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied.

For Example, in the above query, if we apply Partition by

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;

The result set will be

 

ProductID	Name	LocationID	Quantity	Rank
494	Paint – Silver	3	49	1
495	Paint – Blue	3	49	1
493	Paint – Red	3	41	3
496	Paint – Yellow	3	30	4
492	Paint – Black	3	17	5
495	Paint – Blue	4	35	1
496	Paint – Yellow	4	25	2
493	Paint – Red	4	24	3
492	Paint – Black	4	14	4
494	Paint – Silver	4	12	5

 

As you see clearly from the result set that the result set has been partitioned based on the location.
Also the Ranking has been applied twice, as there are two location ID here.

DENSERANK()

Dense Rank is same as the Rank function, except that it will return consecutive integers for ranking.

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,DENSE_RANK() OVER 
    (ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4

The Result set will be,

 

ProductID	Name	LocationID	Quantity	Rank
495	Paint – Blue	3	49	1
494	Paint – Silver	3	49	1
493	Paint – Red	3	41	2
495	Paint – Blue	4	35	3
496	Paint – Yellow	3	30	4
496	Paint – Yellow	4	25	5
493	Paint – Red	4	24	6
492	Paint – Black	3	17	7
492	Paint – Black	4	14	8
494	Paint – Silver	4	12	9

 

If you apply Partition By on the Location ID,

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,DENSE_RANK() OVER 
    (Partition by i.locationId ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4

the result set will be

 

ProductID	Name	LocationID	Quantity	Rank
494	Paint – Silver	3	49	1
495	Paint – Blue	3	49	1
493	Paint – Red	3	41	2
496	Paint – Yellow	3	30	3
492	Paint – Black	3	17	4
495	Paint – Blue	4	35	1
496	Paint – Yellow	4	25	2
493	Paint – Red	4	24	3
492	Paint – Black	4	14	4
494	Paint – Silver	4	12	5

 

As you see clearly from the result set that the result set has been partitioned based on the location.
Also the Ranking has been applied twice, as there are two location ID here.

ROW_NUMBER()
Row Number is same as the DenSe_Rank except For rows that have duplicate values,numbers are arbitarily assigned.

For the below query,

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,ROW_NUMBER() OVER 
    (ORDER BY i.Quantity DESC) AS RowNumber
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4

The Result Set will be

 

ProductID	Name	LocationID	Quantity	RowNumber
495	Paint – Blue	3	49	1
494	Paint – Silver	3	49	2
493	Paint – Red	3	41	3
495	Paint – Blue	4	35	4
496	Paint – Yellow	3	30	5
496	Paint – Yellow	4	25	6
493	Paint – Red	4	24	7
492	Paint – Black	3	17	8
492	Paint – Black	4	14	9
494	Paint – Silver	4	12	10

 

As you see the First two rows have the same quantity. So both should be ranked same.

But Row Number function arbitarily assigned a Rank for the second row.

If we do PARTITION By on the above Query

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,ROW_NUMBER() OVER 
    (Partition by i.locationId ORDER BY i.Quantity DESC) AS RowNumber
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4

The result set will be

 

ProductID	Name	LocationID	Quantity	RowNumber
494	Paint – Silver	3	49	1
495	Paint – Blue	3	49	2
493	Paint – Red	3	41	3
496	Paint – Yellow	3	30	4
492	Paint – Black	3	17	5
495	Paint – Blue	4	35	1
496	Paint – Yellow	4	25	2
493	Paint – Red	4	24	3
492	Paint – Black	4	14	4
494	Paint – Silver	4	12	5

 

NTILE()
Displays the rows in an ordered partition into a specified number of groups.
The Groups are Consecutive Integers starting at 1.

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,NTILE(2) OVER 
    (ORDER BY i.Quantity DESC) AS RowNumber
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4

The above query will give the following result set.

 

ProductID	Name	LocationID	Quantity	RowNumber
495	Paint – Blue	3	49	1
494	Paint – Silver	3	49	1
493	Paint – Red	3	41	1
495	Paint – Blue	4	35	1
496	Paint – Yellow	3	30	1
496	Paint – Yellow	4	25	2
493	Paint – Red	4	24	2
492	Paint – Black	3	17	2
492	Paint – Black	4	14	2
494	Paint – Silver	4	12	2

 

As we used NTILE(2), it divides the result set into 2 groups.
And it assigned rank for the 1st group and rank 2 for the second group.

If we used NTILE(3) in the above query, it will divide the result set into three groups.

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,NTILE(3) OVER 
    (ORDER BY i.Quantity DESC) AS RowNumber
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4

The Result set will be

 

ProductID	Name	LocationID	Quantity	RowNumber
495	Paint – Blue	3	49	1
494	Paint – Silver	3	49	1
493	Paint – Red	3	41	1
495	Paint – Blue	4	35	1
496	Paint – Yellow	3	30	2
496	Paint – Yellow	4	25	2
493	Paint – Red	4	24	2
492	Paint – Black	3	17	3
492	Paint – Black	4	14	3
494	Paint – Silver	4	12	3

 

Since the total no. of rows is 10, and we are not able to divide it equally by 3,
The NTILE function assigned 1 for first four rows and 2 for next 3 rows and 3 for final 3 rows.

If you apply Partition in the NTILE function,

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,NTILE(2) OVER 
    (Partition by i.locationId ORDER BY i.Quantity DESC) AS RowNumber
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4

The result set will be

 

ProductID	Name	LocationID	Quantity	RowNumber
494	Paint – Silver	3	49	1
495	Paint – Blue	3	49	1
493	Paint – Red	3	41	1
496	Paint – Yellow	3	30	2
492	Paint – Black	3	17	2
495	Paint – Blue	4	35	1
496	Paint – Yellow	4	25	1
493	Paint – Red	4	24	1
492	Paint – Black	4	14	2
494	Paint – Silver	4	12	2

 

SQL Server Schema Compare

Recently when I need a tool to compare the schema between two databases, I came across a interesting tool called SQL ADMIN STUDIO

SQL Admin Studio is a powerful SQL Management Tool (for FREE).
We can browse the database schema, design Tables, Triggers, Views, Stored Procedures, Functions and more.
And it works with Microsoft SQL Server 2000, MSDE, 2005, Express, 2008, 2008R2 2012, SQL Azure, MySQL 5.0 and Microsoft Access.

Some of the Features are,

Export Data
SQL Admin Studio provides functions to simply export your data to CSV File, Excel File, XML File and SQL Script file.
All this is available from the mini-toolbar on the results window. The result form any SQL Query can be exported into these file types in a matter of seconds.

Import Data
SQL Admin Studio can import data across Servers this allows you to make simple backups of remote databases over the internet by simply importing the data.
Importing Microsoft Access Databases into SQL Server or just creating a copy of an existing database.

Compare SQL Database Schema
SQL Admin Studio can compare 2 SQL Server database schemas to locate the differences and can even script the differences
without having to drop all objects and re-create the database from a create script.

Compare SQL Database Data
Simply generate SQL Data Change scripts by comparing the database between databases.
Changes can then be executed directly within a transaction or scripted to be applied manually later.

For more details Visit here – http://www.simego.com/Products/Sql-Admin-Studio

You may need Microsoft Sql Server Native Client to be installed in you machine. For this if you have 64-Bit,
Download and isntall it from
http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli_x64.msi
For 32 Bit download and install it from
http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi

Compare SQL Database Schema

Open SQL Admin Studio and Connect to the SQL Server.

Goto Tools->Compare->Schema

Select Database A and Database B the two databases that you want to compare schema.

I am going to Compare AdventureWorks2005 database with AdventureWorks2008R2 database.
Click on the Compare Button.

You can see the results as below.

The table [person].[contact] is missing from the database AdventureWorks2008R2
Click on the Table Link and It will open the Window like below.

Data Modeler for SQL Server

“Data Modeler” is a free tool to create Microsoft SQL Server databases.

If you are trying to design a databse correctly and this is one of the good database design GUI.

Some of the popular features of Data Modeler are

Real-time model validation
DDL regeneration
Table data synchronization
Drag ‘n’ drop foreign key creation
Automatic query script creation
Automatic relationship line placement
Object-level DDL previews
Bulk undo/redo
Schema support
Sub-models
Data dictionary
Update/delete foreign key automation
Customizable fonts and colors

For more details, Please refer http://sourceforge.net/projects/sqldatamodeler/

You can download it from here – http://sourceforge.net/projects/sqldatamodeler/files/latest/download

Backup All the Database in Sql Server

Backup All the Database in Sql Server

DECLARE @DataBaseName VARCHAR(MAX)
DECLARE @FileName VARCHAR(MAX)
DECLARE @FileDate VARCHAR(20)
DECLARE @Path VARCHAR(MAX)

--Set the Path Where You want to take the DataBase BackUp
SET @Path='D:\SQL DB\BACKUP\'
--Get the Date to append it in Backup File name
SET @FileDate=REPLACE((SELECT CONVERT(VARCHAR(20),GETDATE(),101)),'/','_')

DECLARE DataBaseCursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
--Exclude the System Database from Backing up
WHERE name NOT IN ('master','tempdb','model','msdb')

OPEN DataBaseCursor
FETCH NEXT FROM DataBaseCursor into @DataBaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName= @Path + @DataBaseName + '_' + @FileDate + '.BAK'
--BackUp task happening here
BACKUP DATABASE @DataBaseName TO DISK = @FileName

FETCH NEXT FROM DataBaseCursor into @DataBaseName
END

CLOSE DataBaseCursor
DEALLOCATE DataBaseCursor

Automate/Schedule the Database Restore

Already in this Post, we have seen how to Automate/Schedule the Database Backup
Today we are going to see how to automate the database restore task.

1. Open the SQL Server Management Studio. Connect to the database server. I have connected to localhost database server.
2. Expand the “Management” Option. Under that You can able to see an option called “Maintenance Plans”
3. Right Click on the Maintenance Plan and Select “New Maintenance Plan”

4. Enter the Name for the Maintenance Plan. I have given name as “AutoRestore-AdventureWorksDB” and Give Ok.
This will navigate you to the design page of that plan.

5. Enter Description as “To Restore the Database on a daily basis”
6. For Scheduling this job, Click on the Calendar Like icon on the SubPlan Schedule Column.

7. This will open the “Job Schedule Properties” window. Select the “Frequency Occurs” as “Daily”. Leave remaining things as it is.
8. If you are restoring a database in your local machine, give the time as “12:00:00 PM” or the time you want.
When the job runs during that time, the machine/server should not be shut down/switched off.

9. Give Ok. Now Click on the Toolbox tab in the “Design” Page and Double Click “Execute T-SQL Statement Task”

10. This will create the “Execute T-SQL Statement Task” in the Design page.

11. Double Click on the “Execute T-SQL Statement Task” in the Design Page and this will open the “Execute T-SQL Statement Task” window.

12. Enter the following Query in the window and give OK.

RESTORE DATABASE [AdventureWorks2008R2]
FROM  
DISK = N'D:\SQL DB\BACKUP\AdventureWorks2008R2.bak' 
WITH  FILE = 1,  
MOVE N'AdventureWorks2008R2_Data' TO N'D:\SQL DB\DATA\AdventureWorks2008R2_Data.mdf',  
MOVE N'AdventureWorks2008R2_Log' TO N'D:\SQL DB\DATA\AdventureWorks2008R2_Log.LDF',  
NOUNLOAD,  
STATS = 10
GO

13. Save Selected Items. That’s it. You have automated the restore database task.

Optionally you can create one more “Execute T-SQL Statement Task” and you may delete the database using the following query.

USE [master]
GO
ALTER Database [AdventureWorks2008R2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'AdventureWorks2008R2')
DROP DATABASE [AdventureWorks2008R2]
GO