Asp.Net4u: March 2011

Monday, March 21, 2011

SQL Query Results as HTML

The following stored procedure allows you to output query results to either a HTML file or text without the need to use SSIS, DTS packages or BCP. This stored procedure can be used to replace sp_makewebtask which is disabled by default in SQL Server 2005.

Rather than explain the script itself, I'll show you several examples of what it can do for you.

Download SaveTableAsHTML (Size 9 KB).

Installation:

The script creates a stored procedure called SaveTableAsHTML in any desired database. You can place it in specific databases, otherwise, if it will be commonly used by various processes, then you can place it in the master database.
Depending on what option you choose for the parameter @DBUltra, you may need to enable the following...
If @DBUltra = 0 then it will require the use of xp_cmdshell. SQL Server 2005, by default, has disabled this use of xp_cmdshell. To enable this on the server, run the following script in a Query Analyzer sysadmin session on the server:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

If @DBUltra = 1 then it will require the use of sp_OACreate, sp_OAMethod and sp_OADestroy which may be restricted. If so, run the following script in a Query Analyzer sysadmin session on the server:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures',1;
RECONFIGURE;

General Usage:

The SaveTableAsHTML stored procedure accepts 7 parameters, but only @DBFetch is required.

Parameter	Optional/ Mandatory	Data Type	Function
@DBFetch	Mandatory	varchar(4000)	Specifies the SELECT statement used to return a result set from a table (including a temporary table if using the OSQL method), view name, or user-defined function call.
@PCWrite	Optional	varchar(1000)	@PCWrite specifies the location for the data file. The parameter must provide a complete path, including file name, to a location where the SQL Server service account is allowed to create a file. If no value is provided the results are returned as text in the form of HTML table only, without <html> and <body> tags. When you use this parameter, you will normally set the parameter @DBUltra = 1
@DBWhere	Optional	varchar(2000)	Specifies a WHERE clause to filter the data specified by the @DBFetch parameter.
@DBThere	Optional	varchar(2000)	Specifies an ORDER BY clause to sort the data specified by the @DBFetch parameter.
@DBUltra	Optional	bit	@DBUltra is optional and it specifies which data-file-creation method to use. A value of zero (0) uses the OSQL method. A value of one (1) uses the OLE automation method. (Default)
@TableStyle	Optional	varchar(1000)	Allows you to specify a CSS style for the table. The default value for @TableSyle is... border-width: thin; border-spacing: 2px; border-style: solid; border-color: gray; border-collapse: collapse;
@Header	Optional	bit	Used to output the column names as the first line. A value of zero (0) does not include header in the output. A value of one (1) includes header in the first line of the output. (Default)

It's awkward to embed single quotes in a string literal with T-SQL. For convenience when specifying string parameters, caret symbols (^) are converted to single quotes in @DBFetch, @DBWhere and @DBThere. For example:

EXEC master..SaveTableAsHTML @DBFetch='select * from Northwind..Customers', @DBWhere='Country = ^Italy^'

The SaveTableAsHTML stored procedure creates a HTML file at the @PCWrite location on the SQL Server and places in it the columns from the @DBFetch data set (optionally filtered/sorted by @DBWhere or @DBThere).

Example 1: Basic calls which output to the screen

The following examples do not use the parameter @PCWrite which will output the results as text to Query Analyzer. The HTML code is contained by the <TABLE> </TABLE> tags only. This is so that you can insert the query results with other tables in a page.
This example exports a product list sorted/ordered by ProductID from the Northwind database.

USE Northwind
EXECUTE SaveTableAsHTML NULL, 'Products', NULL, 'ProductID'

A similar method to the example above is..

USE Northwind
EXECUTE SaveTableAsHTML @DBFetch = 'Products', @DBThere = 'ProductID'

You can also specify the selection criteria in @DBFetch. @DBWhere is used to specify the WHERE clause. Since @Header=0 the output will not include the column names in the table header.

USE Northwind
EXECUTE SaveTableAsHTML @DBFetch = 'select CompanyName, ContactName, Country from Suppliers', 
@DBWhere = 'Country = ^France^', @Header = 0

You can always use the preferred method which is to specify the entire query in the @DBFetch parameter.

USE Northwind
EXECUTE SaveTableAsHTML @DBFetch = 'select CompanyName, ContactName, Country from Suppliers 
where Country = ^France^', @Header = 0

SaveTableAsHTML works with temp tables, so there is no reason why you can't do the following. This is quite important because implicit functions like CONVERT may not be specified in the @DBFetch parameter.

USE Northwind
select CompanyName, ContactName, Country into #tmp from Suppliers where Country = 'France'
EXECUTE SaveTableAsHTML @DBFetch = #tmp, @Header = 0

Example 2: Sending the output to a file

Use the @PCWrite parameter to specify where on the SQL Server to output the HTML file. You could also use a UNC path. The HTML file is fully bound by <HTML> </HTML> tags.
When using @PCWrite, there are two methods available to output the file, the default is OLE automation and the other is uses OSQL. Please refer to the installation section above as you may need to enable OLE automation and/or xp_cmdshell.
The following example will output all customers to the HTML file specified using the OSQL method.

EXEC master..SaveTableAsHTML @DBFetch = 'select * from Northwind..Customers', 
@PCWrite='c:\temp\test.htm', @DBUltra = 0

Example 3: Using SaveTableAsHTML in Email

The following example shows how to use SaveTableAsHTML in an email. This example is suited only to SQL 2005 as sp_send_dbmail is used.

EXECUTE msdb..sp_send_dbmail
@recipients = 'someone@domain.com',
@subject = 'Test',
@body_format = 'html',
@exclude_query_output = 1,
@append_query_error = 1,
@query = ' PRINT ''<h2>Addresses</h2>''
EXECUTE SaveTableAsHTML @DBFetch = ''select top 10 * from AdventureWorks.Person.Address where City = ^Sydney^''
PRINT ''<br>''
PRINT ''<h2>Products</h2>''
EXECUTE SaveTableAsHTML @DBFetch = ''select top 10 * from AdventureWorks.Production.Product'' '

SQL Formatting Errors:

Occasionally, the query you are using and/or the column data type or name will produce an error similar to the one below:

Msg 16916, Level 16, State 1, Procedure SaveTableAsHTML, Line 269 A cursor with the name 'Records' does not exist. Msg 16916, Level 16, State 1, Procedure SaveTableAsHTML, Line 272 A cursor with the name 'Records' does not exist. Msg 16916, Level 16, State 1, Procedure SaveTableAsHTML, Line 321 A cursor with the name 'Records' does not exist. Msg 16916, Level 16, State 1, Procedure SaveTableAsHTML, Line 321 A cursor with the name 'Records' does not exist.

Usually this error is a result of the stored procedure SaveTableAsHTML not being able to interpret the results so it can convert the data to your requirements. Please check the query you are using in @DBFetch and refer to the Caveats/Recommendations section below for more information.
If you are using the OSQL method (where @DBUltra = 0) you may see an error when outputting records from tables with large numbers of columns. This is because SaveTableAsHTML has limited OSQL queries to 8000 characters. We suggest either using the default OLE automation method (where @DBUltra = 1) or limiting the number of columns returned.

Caveats/Recommendations:

Column titles must not contain spaces. You will get an error if any column contains spaces. A suggestion is to use underscores.
Avoid using column names that are T-SQL reserved words. Also avoid using ID as a column name. If you need to, then bound the name with braces [ID]
Do not use complex queries in @DBFetch, instead, output your query to a temporary table and then reference the temporary table in @DBFetch.
Sometimes SaveTableAsHTML can't handle some data types so CONVERT or CAST the results to a varchar.
When using the @PCWrite parameter, please make sure that the path used actually exists as the path (folder/directory) will not be created if incorrect. The file is saved on the SQL Server so the path and file needs to be appropriate. I recommend setting aside a path like C:\TEMP\ where the old files can be regularly removed.
When you use the parameter @PCWrite and you get the error "Windows error [1]", then check that you specify the full path and filename of the output file and try setting the parameter @DBUltra = 0.

Acknowledgment:

The SQL script was originally provided by Brian Walker (http://searchsqlserver.techtarget.com)
And http://www.virtualobjectives.com.au
Thanks Brian, you saved me from many many hours of frustration and stress.

Steps To Create An Job

Steps for Creating an Job :

Step 1:

Open SQL Server Management Studio and Expand the SQL Server Agent Folder

Close SQL Server Configuration Manager and open SQL Server Management Studio. Within SSMS, expand the SQL Server Agent folder. You'll see the expanded folders shown above.

Step 2:

Create a New SQL Server Agent Job

Next, right-click on the Jobs folder and select New Job from the start-up menu. You'll see the New Job creation window shown above. Fill in the Name field with a unique name for your job (being descriptive will help you manage jobs better down the road!). Specfy the account that you wish to be the owner of the job in the Owner text box. The job will run with the permissions of this account and may only be modified by the owner or sysadmin role members.

Once you've specified a name and owner, choose one of the predefined job categories from the drop-down list. For example, you might choose the "Database Maintenance" category for routine maintenance jobs.

Use the large Description text field to provide a detailed description of the purpose of your job. Write it in such a way that someone (yourself included!) would be able to look at it several years from now and understand the purpose of the job.

Finally, ensure that the Enabled box is checked.

Step 3 :

Enter the SQL Server Agent Job Steps Screen

On the left side of the New Job window, you'll see a Steps icon under the "Select a page" heading. Click this icon to see the blank Job Step List shown above.

Step 4:

Add SQL Server Agent Job Steps

Next, you'll need to add the individual steps for your job. Click the New button to create a new job step and you will see the New Job Step window shown above.

Use the Step Name textbox to provide a descriptive name for the Step.

Use the Database drop-down box to select the database that the job will act upon.

Finally, use the Command textbox to provide the Transact-SQL syntax corresponding to the desired action for this job step. Once you have completed entering the command, click the Parse button to verify the syntax.

After successfully validating the syntax, click OK to create the step. Repeat this process as many times as necessary to define your desired SQL Server Agent job.

Step 5:

Schedule the SQL Server Agent Job

Finally, you'll want to set a schedule for the job by clicking the Schedule icon in the Select a Page portion of the New Job window. You'll see the New Job Schedule window shown above.

Provide a name for the schedule in the Name text box and choose a schedule type (One-time, Recurring, Start when SQL Server Agent Starts or Start When CPUs Become Idle) from the drop-down box. Then use the frequency and duration sections of the window to specify the job's parameters. When you are finished click OK to close the Schedule window and OK to create the job.

Wednesday, March 2, 2011

Stored Procedure Optimization Tips

Use stored procedures instead of heavy-duty queries.

This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

*****

Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.

This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

*****

Call stored procedure using its fully qualified name.

The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

*****

Consider returning the integer value as an RETURN statement instead of an integer value as part of a record set.

The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a record set.

*****

Don’t use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.

The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend to use the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix "sp_" in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

*****

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 readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

*****

Use sp_executesql stored procedure instead of temporary stored procedures.

Microsoft recommends to use the temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures to have a better chance to reuse the execution plans.

*****

If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.

The stored procedure will be recompiled when any structural changes were made to a table or view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, you get chance that only a single sub-procedure will be recompiled, but other sub-procedures will not.

*****

Try to avoid using temporary tables inside your stored procedure.

Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

*****

Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.

Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.

*****

Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.

The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

Use SQL Server Profiler to determine which stored procedures has been recompiled too often.

To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

*****

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:
Try to use UNION ALL statement instead of UNION, whenever possible.

The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.

Index Optimization tips

*****

Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.

*****

Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.

Try to create indexes on columns that have integer values rather than character values.

If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.
Create surrogate integer primary key (identity for example) if your table will not have many insert operations.

Clustered indexes are more preferable than non clustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.

If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.

You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.

You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

Import an Excel Spreadsheet into a SQL Server Database

1. Enter the data into an Excel spreadsheet

First, enter the data into an Excel spreadsheet.
In this example, the default spreadsheet name, Sheet1, has been retained, but if you rename the sheet (to the name you want for the table in the database, for example), that name will be automatically used in the import process (at Step 6).

Small demonstration Excel spreadsheet ready for transfer to a SQL database
Excel spreadsheet ready to export to a SQL table.

2. Start the SQL Import and Export Wizard

Next, in Windows, start the Import and Export Wizard at Start / All Programs / Microsoft SQL Server 2008/ Import and Export Data.
The Welcome page appears. Click Next.

SQL Import and Export Wizard in the Windows Start menu
Windows menu showing SQL Server Import and Export Wizard

Welcome page of the SQL Server Import and Export Wizard

3. Chose your Excel spreadsheet as the Data Source

In the Data Source dropdown of the Choose a Data Source page, select Microsoft Excel.
In the Excel file path box, specify the file path to the Excel spreadsheet.
Select the the version in the Excel version dropdown.
Make sure that First row has column names is checked.
Click Next.

Chose a Data Source page in the SQL Server Import and Export Wizard

4. Chose your SQL database as the destination

In the Destination dropdown list, accept the default setting of SQL Server Native Client 10.0.
In the Server name dropdown list, enter the name of the server. The example is a remote server, so the IP address and port of the server were specified.
Chose the Authentication type. The example is a remote server, so SQL Server authentication, with a user name and password, is required.
In the Database dropdown list, select or type the name of the database.
Click Next.

Chose a Data Destination page in the SQL Server Import and Export Wizard

5. Specify how to copy the data

The default option, Copy data from one or more tables or views, works for this example.
If you want to try the second option, Write a query to specify the data to transfer, the following lolcode snippet may be instructive:

CAN HAS SQL?
DBASE IZ GETDB('db/demo.db')
FUNNAHS IZ DBUCKET(&DBASE&,"CAN I PLZ GET * ALL UP IN lollit")
IM IN UR FUNNAHS ITZA TITLE
VOTEZ IZ &TITLE#ups& - &TITLE#downs&

Click Next.

Specify Table Copy or Query page in the SQL Server Import and Export Wizard

6. Select the source tables and views

The default settings as shown work in this example.
In the Destination column, you can specify a different name for the table in the SQL database if you choose.
Click Preview to see how your data will appear in the destination table.
You can click Edit Mappings to change how your data is assigned at the destination table, but it shouldn't be necessary in this example since you entered the data into the Excel spreadsheet yourself.
Click Next.

Select Source Tables and Views page in the SQL Server Import and Export Wizard

7. Run the "Package"

The following message appears at the bottom of the Run Package page: In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation. A "package" is all of the settings that your have configured so far. In the commercial versions of SQL Server, you can save the package for reuse so you don't have to enter all of the settings the next time you run the wizard. In the Express (free) version of Microsoft SQL Server 2008 Management Studio, you must re-enter all of the settings every time you run the SQL Server Import and Export wizard.

Click Next.

Run Package page in the SQL Server Import and Export Wizard

8. Verify that the package executed successfuly

Click Report to view useful information about the data transfer process.
Click Close.

The execution was successful page in the SQL Server Import and Export Wizard

9. View the new table in SQL Server Management Studio

View your new table by opening Microsoft SQL Server 2008 Management Studio at Start / All Programs / Microsoft SQL Server 2008/ Import and Export Data.

The new table, dbo.Sheet1$, in Microsoft SQL Server Management Studio
Successfully imported table in SQL Server Management Studio

Export a Table from SQL Server to an Excel Spreadsheet

1. Prepare an Excel spreadsheet to receive the SQL table data

First, prepare an Excel spreadsheet to receive the data from the SQL table by adding the exact headings that are in the table to the top row of the spreadsheet (see the two screenshots below).

The target Excel spreadsheet with SQL table headings added
Excel spreadsheet with headers, ready to import a table from a SQL database

The source table, dbo.Sheet1$, in Microsoft SQL Server Management Studio
Successfully imported table in SQL Server Management Studio

2. Start the SQL Import and Export Wizard

Next, in Windows, start the Import and Export Wizard at Start / All Programs / Microsoft SQL Server 2008/ Import and Export Data.
The Welcome page appears. Click Next.

SQL Import and Export Wizard in the Windows Start menu
Windows menu showing SQL Server Import and Export Wizard

3. Chose your SQL table as the Data Source

In the Data Source dropdown list of the Choose a Data Source page, accept the default SQL Server Native Client 10.0.
In the Server name dropdown list, enter the name of the server. The example is a remote server, so the IP address and port of the server were specified.
Chose the Authentication type. The example is a remote server, so SQL Server authentication, with a user name and password, is required.
In the Database dropdown list, select or type the name of the database.
Click Next.

4. Chose your Excel spreadsheet as the Destination

In the Destination dropdown of the Choose a Destination page, select Microsoft Excel.
In the Excel file path box, specify the file path to the target Excel spreadsheet.
Select the the version in the Excel version dropdown.
Make sure that First row has column names is checked.
Click Next.

5. Specify how to copy the data

The default option, Copy data from one or more tables or views, works for this example.
Click Next.

6. Select the source tables and views

The default settings as shown work in this example.
In the Destination column, specify the name of the Excel spreadsheet. In step 1, Sheet2 was used, so that sheet is shown selected here.
Click Preview to see how your data will appear in the destination spreadsheet.
You can click Edit Mappings to change how your data is assigned at the destination spreadsheet, but it shouldn't be necessary in this example since you entered the data into the Excel spreadsheet yourself.
Click Next.

7. Run the "Package"

Click Next.

8. Verify that the package executed successfuly

Click Report to view useful information about the data transfer process.
If you get an error saying that the number of rows is not correct, verify that the headings in the Excel spreadsheet match the SQL table exactly, as shown in step 1.
Click Close.

9. View the spreadsheet with the imported table rows in Excel

View the spreadsheet with the added data rows.

The new spreadsheet, Sheet2, in Microsoft Excel.
Excel spreadsheet with table rows imported from a SQL database

Tuesday, March 1, 2011

SQL Server - Create a Login

This article will give a walkthrough on creating a SQL Login user.

Step 1

Login to SQL Server 2008 instance using windows authentication

Step 2

Right click on server instance name and select properties.

Step 3

Property window will be open. In that select the Security tab.

In server authentication tab you can see your SQL Server in configured for

Windows authentication mode
SQL Server and Windows Authentication mode

So if you want to enable SQL User login to SQL Server then select checked mixed mode here .

And then click ok. Now SQL Server is configured for mixed mode login.

Step 4

Now to create SQL LOGIN, right click on Security tab and select New and then Login .

Step 5

When you click on Login you will get the below window

Give the Login Name

Select SQL Server Authentication and provide your desired password

Disable Enforce password policy

Select default database

Leave default language and click OK to create a new SQL Server login

Now in object explorer you can see User1 login .

Now at time of connecting to SQL Server login User1 can be used.
If want to give the Roles you can give it from Server roles of this user.

SQL Server 2005: How To Restore a Database from a Backup

1. Open SQL Server 2005 Management Studio.
2. Connect to the appropriate server.
3. Expand Databases.
4. Right-click the desired database, mouseover Tasks, mouseover Restore and select Database.
5. The Restore Database window will appear.
6. Ensure that the correct database name appears in the To database field. If not, select it from the dropdown.
7. Under Source for restore, select the From device radio button.
8. Click the button next to the textbox to select the device to restore from.
9. The Specify Backup window will appear.

10. Click Add and locate and select the backup file from the Locate Backup File Window. Click OK.
11. Under Select the backup sets to restore, select all three backups (full, transaction log, and differential).
12. In the left pane, select Options.
13. Under Recovery state, ensure that the Restore with Recovery radio button is selected.
14. Click OK to begin restoring the database.
15. Once the restore is complete, a notification box will appear. Click OK to close the box. You have now restored your database to the most recent state.