Wednesday 12 August 2015

Restore a Database in Sql Server 2008 R2

Hello everyone First of all thanks for visit and my blog and thanks for giving a wonderful comments on my post , here is i come with my another post for How to Restore a Database in Sql Server 2008 R2 Using Back Up file (.bak), so let's start with introduction

How to Restore a Database in Sql Server

First open the sql server Management studio and login with specific user and password, after login go to the left pen and expand the instance, after expand the instance view the list of folder are there expand the database folder and select the database you want to restore and Right click on it.

Right click on database >> Tasks >> Restore >> Database...

Open a new window called "Restore Database" window , here is to tab on left pan is "General" "Options" i explain it here with image, first you can select source you want to get backup database file, here i select the Options "From Device" and click on Browse button Right side on text box to display the select backup file window other is by default is set.

Here is open a new window called "Specify Backup", hew you can select the "backup Media to file" because you can backup task is done using backup file and click on "Add" button to add the Backup file with Extension (.bak).

Open new window called "Locate Backup File" to locate your (.bak) file in your system location and select it and click on "OK" Button

Display the Selected file with full location path, make sure the path and file name and the click on "OK" Button to confirm.

Return to main window

Here is display the selected location with file description, Right side of the list is display the Restore check box you can select check to this check box and go to options tab.

options tab list of option with check box display on right pan, configure all the Restore options and Recovery State as per your requirement, here i configure Restore options is first options is check true the option name is "Overwrite the existing database (With Replace)" and Recovery State is first one is "Leave the database Ready to use by Rolling back uncommitted transactions. additional transaction logs cannot be restored.(RESTORE WITH RECOVERY)" , after complete all the step and then click on "OK" button to start Restore data process....

After process completed 100% successfully (Display left bottom corner status is 100%) one message box is display "The Restore of Databasees "DBName" completed successfully" click on "Ok" to close the message and confirm the database table and make sure the changes is restore done.

the second option is to Restore the database using SQL Query

Right click on database >> new query ....

here is the synatax and example of the Restore query

Syntax

RESTORE DATABASE DatabaseName
   FROM DISK = 'file path of Backup file is locate' ;

Example

RESTORE DATABASE EMPDB
   FROM DISK = 'E:\krunal\data\empdb.bak' ;

after write query successfully execute the query to restore database is

started and complete the process to display the message on Output window

is "Query Completed Successfully"

now i complete the my blog for how to restore the database in

sql server 2008 R2, i wish this is helpful for everyone, you can query

regarding this post plesase comment me or send email..

Thanks

Krunal patel

(Software Developer)

My Popular post click on Link to view

Wednesday 5 August 2015

Asp.Net Post for Get Home screen tweets from twitter using API and store into SQL Server Database

Get Home screen tweets from twitter using API and store into SQL Server Database

My first Asp.Net article for the twitter tweets related to display latest less than 200 tweet for home screen in ASP.NET page and store in the database table.

Introduction

i search a lots of things related twitter tweets,i found lots of solution on internet using this information and i devlop a one Asp.net application for Display latest tweets on my page and store the tweets in my sql server database and use Visual studio 2010 withe .Net Framework 4.0

Background

First of sign in to twitter using this link https://apps.twitter.com/ and follow the step and Get the

"ConsumerKey"

"ConsumerSecret"

"TokenKey"

"TokenSecretkey"

After get all the twitter key the create a database in MS SQL Server and create a table

code

first Design the Default.aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
 
<!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head id=Head1 runat="server">
    <title></title>
 
    <style type="text/css">
        #myTitle
        {
            color: Black ;
            font-weight :bolder ;
            font-style :normal ;
        }
        
        #myDiv
        {
            background-color: #FFFFFF;
        }
        
    </style>
 
</head>
<body>
    <form id="form1" runat="server">
    <div id="myDiv" runat="server">
     
    </div>
    </form>
</body>
</html>

now next set the key and connection string in Web.config file

<connectionStrings>
    <!--put your connection string here-->
    <add name="conString" connectionString="Data Source=ServerName/IP;Initial Catalog=dbname;Integrated Security=false;User Id=username;Password=password;Persist Security Info=true; Connect Timeout=1000;Max pool size=25"/>
  </connectionStrings>
  <appSettings>
    <add key="ConsumerKey" value="hMYnAWt4fkZuirxDKvsH2yXe"/> <!--"insert here...";-->
    <add key="ConsumerSecret" value="763lji0wK3hrjSgAdC4Jz2XyfIs4nOK4cOo1BHNsBnTvr2vGU"/><!--"insert here...";-->
    <add key="TokenKey" value="3171598760-1mfuPn7YpzXRqDnj3Jnm6dg7YkYdN9J8qw7rZ"/><!--"insert here...";-->
    <add key="TokenSecret" value="DPjzDM8zy4DUDwtvYSRI4QeCWY9b6AEvP74fySyRc"/><!--"insert here...";-->

  <add key="Twitterizer2.EnableStatisticsCollection" value="false"/>
  </appSettings>

Now write this code in Default.aspx.cs code file

using System;

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Security.Cryptography;
using System.Net;
using System.IO;
using Newtonsoft.Json.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
private SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);

    //public string query = "mca_krunal";
    public string query = "";
    //    public string url = "https://api.twitter.com/1.1/users/search.json" ;
    //public string url = "https://api.twitter.com/1.1/statuses/user_timeline.json"; //Display my tweet
    public string url = "https://api.twitter.com/1.1/statuses/home_timeline.json"; //display home tweet with my tweet

    public Int16 cnt = 200;

    protected void Page_Load(object sender, EventArgs e)
    {
        findUserTwitter(url, query);
    }

public void findUserTwitter(string resource_url, string q)
{

        // oauth application keys
        var oauth_token = ConfigurationManager.AppSettings["TokenKey"]; //"insert here...";
        var oauth_token_secret = ConfigurationManager.AppSettings["TokenSecret"]; //"insert here...";
        var oauth_consumer_key = ConfigurationManager.AppSettings["ConsumerKey"];// = "insert here...";
        var oauth_consumer_secret = ConfigurationManager.AppSettings["ConsumerSecret"];// = "insert here...";

        // oauth implementation details
        var oauth_version = "1.0";
        var oauth_signature_method = "HMAC-SHA1";

        // unique request details
        var oauth_nonce = Convert.ToBase64String(new ASCIIEncoding().GetBytes(DateTime.Now.Ticks.ToString()));
        //var oauth_nonce = "8314576e2324d3c9a55bf3f021fd2965";
        var timeSpan = DateTime.UtcNow
            - new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc);
        var oauth_timestamp = Convert.ToInt64(timeSpan.TotalSeconds).ToString();
        //var oauth_timestamp = "1429954519";

        // create oauth signature
        var baseFormat = "count={7}&oauth_consumer_key={0}&oauth_nonce={1}&oauth_signature_method={2}" +
        "&oauth_timestamp={3}&oauth_token={4}&oauth_version={5}&screen_name={6}";
        //var baseFormat = "oauth_consumer_key={0}&oauth_nonce={1}&oauth_signature_method={2}" +
        //"&oauth_timestamp={3}&oauth_token={4}&oauth_version={5}&screen_name={6}";

               var baseString = string.Format(baseFormat,
                                    oauth_consumer_key,
                                    oauth_nonce,
                                    oauth_signature_method,
                                    oauth_timestamp,
                                    oauth_token,
                                    oauth_version,
                                    Uri.EscapeDataString(query),
                                    cnt.ToString()
                                    );

baseString = string.Concat("GET&", Uri.EscapeDataString(url), "&", Uri.EscapeDataString(baseString));

var compositeKey = string.Concat(Uri.EscapeDataString(oauth_consumer_secret),
"&", Uri.EscapeDataString(oauth_token_secret));

        string oauth_signature;
        using (HMACSHA1 hasher = new HMACSHA1(ASCIIEncoding.ASCII.GetBytes(compositeKey)))
        {
        oauth_signature = Convert.ToBase64String(
        hasher.ComputeHash(ASCIIEncoding.ASCII.GetBytes(baseString)));
        }

       // create the request header
        var headerFormat = "OAuth oauth_nonce=\"{0}\", oauth_signature_method=\"{1}\", " +
        "oauth_timestamp=\"{2}\", oauth_consumer_key=\"{3}\", " +
        "oauth_token=\"{4}\", oauth_signature=\"{5}\", " +
        "oauth_version=\"{6}\"";

        var authHeader = string.Format(headerFormat,
        Uri.EscapeDataString(oauth_nonce),
        Uri.EscapeDataString(oauth_signature_method),
        Uri.EscapeDataString(oauth_timestamp),
        Uri.EscapeDataString(oauth_consumer_key),
        Uri.EscapeDataString(oauth_token),
        Uri.EscapeDataString(oauth_signature),
        Uri.EscapeDataString(oauth_version)
        );

ServicePointManager.Expect100Continue = false;

        // make the request
        var postBody = "screen_name=" + Uri.EscapeDataString(query) + "&count=" + cnt.ToString();//
        //var postBody = "screen_name=" + Uri.EscapeDataString(query) ;//
        url += "?" + postBody;
        HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
        request.Headers.Add("Authorization", authHeader);
        request.Method = "GET";
        request.ContentType = "application/x-www-form-urlencoded";
        var response = (HttpWebResponse)request.GetResponse();
        var reader = new StreamReader(response.GetResponseStream());
        var objText = reader.ReadToEnd();

        myDiv.InnerHtml = objText;/**/

        string html = "";
        try
        {
            JArray jsonDat = JArray.Parse(objText);

html = html + "Total Tweet Cout :" + jsonDat.Count().ToString() + " <hr>";
 for (int x = 0; x < jsonDat.Count(); x++)
 {
 html = html + "ID : " + jsonDat[x]["id"].ToString() + " ";
 html = html + "Tweet : " + jsonDat[x]["text"].ToString() + " ";
 html = html + "Created On : " + jsonDat[x]["created_at"].ToString() + " <hr>";

InsertTweetIntoDB(jsonDat[x]["id"].ToString(), jsonDat[x]["text"].ToString(), jsonDat[x]["created_at"].ToString());

             }
            myDiv.InnerHtml = html;
        }
        catch (Exception twit_error)
        {
            myDiv.InnerHtml = html + twit_error.ToString();
        }
    }

    //Firest create table into in your db name"Table_Tweet"
    public void InsertTweetIntoDB(string ID, string TweetTXT, String Created_AT)
    {
        con.Open();

        //Query to insert images path and name into database
        SqlCommand cmd = new SqlCommand("Insert into Table_Tweet(ID, TweetTXT, Created_AT) values(@ID, @TweetTXT, @Created_AT)", con);
        //Passing parameters to query
        cmd.Parameters.AddWithValue("@ID", ID);
        cmd.Parameters.AddWithValue("@TweetTXT", TweetTXT);
        cmd.Parameters.AddWithValue("@Created_AT", Created_AT);

        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        //Close dbconnection
        con.Close();
    }

    public void UpdateTweetIntoDB(string ID, string TweetTXT, String Created_AT)
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("update Table_Tweet set ID=" + ID + ", TweetTXT='" + TweetTXT + "', Created_AT='" + Created_AT , con);
        cmd.ExecuteNonQuery();
        con.Close();
    }

}

Interest

i have learn lots of new things from this demo and thank all the guys who help me for this assignment,i hope this is helpful for needy people...

Thanks

My Popular post click on Link to view

Create a Database Backup in Sql Server 2008 R2

hello everyone first of all thank you so much for the post comment and share my post with everyone,here i come with the new post Create a Database Backup in Sql Server 2008 R2, in this post i briefly explain the description of the creating backup and store in particular location.

Create a Database Backup in Sql Server

Starting with open the sql server management studio and login with the specific you user you want to use and the expand the database and select the database and right click on the database and navigate the "Task" and select the option "Back up..."

after completion this step the open the new window for backup configuration see like this..

- General configuration

- Options Configuration

in this window here you can create or configure the backup for your database name "EMPDB" all the by default configuration is set,to see on above picture, you can configure all the option as per your needs, after configure all the option you can select or click on "Add" button to add the location you want to store the database backup file.

- add the location you want to store database backup file

in this window you can first select the file name radio button options and then click on browse button to right side of the file name text box tor browse the local path of your system see on the next step, also second option is Backup Drive you have any Backup drive so you can select via drop down list text box.

in this step you can specify the selected folder path bottom of the window and files type and last name of file you want to create backup then click on ok button to return on the select file name window here is the display the whole path with file name and file extension.click on ok button to return the main configuration window with display the path and file name.

select the check box left side of the path and then click on the OK button to view the progress of database backup on the window right bottom corner and display the message back up..created successfully.

Using Sql Query to create database Backup file

Open a new query window

Syntax

BACKUP DATABASE [Database file Path] TO disk = 'Path to store backup file'

Example

BACKUP DATABASE [E:\krunal\data\EMPDB.mdf] TO disk = 'E:\Krunal\data\backup\empdb.bak'

check the path you select the folder open file is create with extension of ".bak" here is i haver create backup file called "empdb.bak" is stored in ms sql server default Backup folder.

Also you can crate the Auto backup Schedule for your database, this facility provide in sql server 2008 R2 , i explain it on my up coming post.

here now i complete this post,i wish this post is help to learn everyone, any query or confusion regarding my post please send me mail or comment.

Thanks

Kruna Patel
(Software Developer)

:-) :-)

My Popular post click on Link to view

Tuesday 4 August 2015

Create a New Stored Procedure in Sql Server Step by Step

Hello ever one i come with my new post for sql server users needs in this post, i have described about how to Create a New Stored Procedure in Sql Server Step by Step, so let's start with introduction of Stored Procedure.

Stored Procedure

Definition

in sql server Database Management System (DBMS), stored procedure is a precompiled set of Structured Query Language (SQL) statements (or code) that can be shared by a number of Diffrent programs. view More About stored procedure on Microsoft Click to Visit MSDN Site

first you need to open sql server management studio login using user name and password and open the database, Expand the database and select and expnad the "Programmability" folder and select the sub folder "Stored Procedure"

Right click on Stored Procedure >> select sub menu "New Stored Procedure"

open the syntax code with stored procedure in the Right pan, here is the default syntax of Stored Procedure.

Syntax

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

GO

Now you can write your own sql query using the syntax and create your own procedure

Example

CREATE PROCEDURE Sp_Emp

BEGIN

SET NOCOUNT ON;

SELECT * FROM dbo.Table_Emp

END

GO

after write successfully all the sql statement with the perfect syntax then compile or execute the stored procedure

- Right click and select Execute
- click to execute button on tool bar

it successfully created message is display in the output window,and see the programmability folder the new procedure is stored in this database folder, see all the list of stored procedure is created in the current database

now execute your stored procedure using the following syntax for for execute statement.

Syntax

EXECUTE <STORED PROCEDURE NAME>

OR

EXEC <STORED PROCEDURE NAME>

this statement write in the "New Query" window and execute it.

Example

EXECUTE Sp_Emp

OR

EXEC Sp_Emp

i complete this post for now, i hop this post is helpful for under stand about the stored procedure, any query regarding this post or any content please comment me or email me.

Thanks

Krunal patel
Software Developer

:-) :-)

My Popular post click on Link to view

Saturday 1 August 2015

Create a New Database User In Sql Server 2008 R2

Wel-come again to all my blog reader,firs of all thanks for wonderful comments and reply and likes for my blog, i come with the new Post regarding sql sever new login or user creation,you can also refer thin link to view my another previous blog from here.

Create a New Database User

Start with Sql Server 2008 R2, open sql server management studio and login using default user "Administrator" or "sa" login successfully on the left pan display the list of folder, you can select the folder "Security" and expand the folder to display sub folder the select the "Login" felder.

Right click on folder logins>> select item "new Logins..."

after select new login option display the new window call "login-new",in this window contain the configuration settings for new user, first you can set the Login name for user and the select he authentication mode in this window display authentication mode with two radio button first one is

"Windows authentication" second one is "Sql server authentication" you can choose what type of permission give you the login user, you can select he secod option "Sql server authentication" then you need to password compulsory,after given password you can configure other options as per requirement. see the following image.....

in this window left pan display the sub page for configure so you can select it one by one and configure as per your needs.

is start with second options first is already configure and explain, second option is "Server Role"in this option you can give the role of server user will act.

after set-up " Serve Role" next options is "User Mapping" also you can set up in this step the user will access which database option on this user login.

after configure "User Mapping " you can go to "Securables" and configure it as per your needs.

after complete "Securables" go to the last step "Status" it's showing in image...

make sure in this page the permission is defult select with "Grant" and Login option is "Enabled",it is not checked the you can't login for the created user, so make sure this things and configure all the options properly.

i complete now for this post i hop every one who are the freshers on sql server 2008 R2 for help to learn how to create new user and configure the user, i come with another post next time ,now good day for learn.

Thanks

Krunal Patel

(software developer)

:-) :-)

My Popular post click on Link to view