Restore sql database by query

RESTORE DATABASE [Database name] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.OMEXDEV\MSSQL\Backup\ump.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Install MVC website on IIS7

1st install ASP MVC, then run one of the following depending on your architecture:

 32bit (x86) Windows

%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -ir

 64bit (x64) Windows

%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -ir

 also check this thread for more info: ASP.NET MVC on IIS 7.5

 You could also need to recreate your application/site after these steps.

Query execution important for SQL Server performance SQL server

SQL Server performance monitoring depends on the performance goals. The first step in monitoring is to determine optimal performance for your server and create performance trends based on the captured metric information. If performance is not satisfactory, it’s necessary to diagnose performance problems and find their origin. This involves testing how different queries and applications affect performance

Whatever the performance goals are, they all have one thing in common – to ensure optimal performance. It doesn’t have to be the best performance possible, sometimes you have to tradeoff between needs and cost. To achieve this, analyze available hardware resources and their performance first. Then, analyze how the resources are used. Poor designed databases, inefficient queries, bad indexing, etc. can significantly slow down the whole system

One of the first issues you notice when working with a SQL Server database is the response time. If you execute a stored procedure, query, or search for specific data, you’d like the results to appear quickly. If not, is there anything that can be done to make the query run faster?

The SQL Server query execution plan can answer this question and help you diagnose the problem, if it exists. The execution plan can also help you write efficient queries, create the right indexes that quickly provide only the rows you need, instead of searching through millions of records, etc.

What is a SQL Server query execution plan?

A query plan, execution plan, or query execution plan is an algorithm showing a set of steps in a specific order that is executed to access data in a database

A query plan shows how a query was executed, or how it will be executed which is significant for troubleshooting query performance issues. Executing a SELECT statement to find out its query plan and effect on SQL Server performance can be acceptable, but executing UPDATEs to find that out is not an option. The plan is calculated by a SQL Server component Query Optimizer using minimum of server resources. When creating the SQL Server query execution plan, the number of database objects involved, joins, indexes and their availability, number of output columns, and more is considered

When a new query is executed, Query Optimizer evaluates the query plan, optimizes and compiles it, and stores it in the plan cache. The plan cache is a part of SQL Server buffer where data and query plans are stored (buffered), so they can be reused later

When a query is executed, Query Optimizer first searches the plan cache looking for a query plan that can be reused, thus making the execution faster. If there’s no query plan that can be reused, a new one has to be created, which takes time and therefore makes query execution last longer

A very useful characteristic of query plans is that when a stored procedure is executed, the query plan is created for the stored procedure name and the same query plan will be reused whenever the stored procedure is executed, despite the values specified for procedure parameters. When it comes to executing ad hoc queries, query plans are created based on complete code, so different parameters or any change in code will prevent reuse of the existing plan. This clearly indicates what should be done to make your code run faster – wrap it up as stored procedures or functions, and the existing query plans will be reused and therefore code will be executed much faster

The slow execution of ad hoc queries can be mitigated by using the Optimize for ad hoc workloads option, introduced in SQL Server 2008. The option optimizes the plan cache use, as it solves the situation when query plans executed only once fill up the plan cache. As the buffer cache is used for both data and plan buffering, and the percentage of cache used for each changes in time depending on the current situation, it’s necessary to use the cache wisely. Instead of buffering the whole plan, when the option is set to “True”, only a fragment of the plan is buffered when the query is executed for the first time. When an ad hoc query is executed for the second time, its complete plan is buffered in the cache

The default value is False. The option in available in the SQL Server instance properties

Right-click the SQL Server instance in the SQL Server Management Studio Object Explorer

Select the Advanced tab

In the Miscellaneous options list, set the Optimize for ad hoc workloads option to True

Another way to change this option is to use T-SQL

sp_CONFIGURE 'show advanced options', 1 RECONFIGURE GO sp_configure 'optimize for ad hoc workloads', 1 GO RECONFIGURE GO

However, keep in mind that this is an advanced option, therefore recommended to be modified only by advanced users

How to remove query plans in SQL Server?

Query plans are automatically removed from the plan cache when the SQL Server instance is restarted or memory pressure appears. Not reused plans and the ones that can be easily recompiled are first removed to resolve the memory pressure situation

To remove all query plans from cache manually, use the following statement

1	DBCC FREEPROCCACHE WITH NO_INFOMSGS

Using this statement is recommended for advanced users only and it should be never be used on a production server before previous analysis of the instance state, as it will cause recompilation of stored procedures and thus slowdown the system performance

How to recompile query plans in SQL Server?

Query plans are automatically recompiled whenever a cached plan becomes invalid. A plan can become invalid (or obsolete) if there is a database change. The most common causes are changes of the objects used by the stored procedure: the stored procedure itself, the referenced table or view, indexes used by the plan, statistics, significant key data changes, etc. Next time the same stored procedure is executed, Query Optimizer will find an invalid plan in the cache, and will recompile a new one. The good news is that starting with SQL Server 2005, only the statements that need recompilation are recompiled, not the whole batch

A SQL Server query plan can also be recompiled explicitly when the stored procedure is executed using the WITH RECOMPILE clause, or the sp_recompile stored procedure is executed

In this article we introduced SQL Server query execution plans – explained what they are, how they are created, and how they affect SQL Server performance. As shown, a query plan reuse makes the time needed for execution shorter, therefore it’s recommended to save the frequently used queries as stored procedures. In next part of this series, we’ll present various methods for viewing the query execution plans

- See more at: http://www.sqlshack.com/sql-server-query-execution-plans-basics

Find Trigger with table name in sql server

SELECT

     sysobjects.name AS trigger_name

    ,USER_NAME(sysobjects.uid) AS trigger_owner

    ,s.name AS table_schema

    ,OBJECT_NAME(parent_obj) AS table_name

    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate

    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete

    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert

    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter

    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof

    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]

FROM sysobjects INNER JOIN sys.tables t

    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s 

    ON t.schema_id = s.schema_id

WHERE sysobjects.type = 'TR'

Link sql server to another sql server

USE [master]
GO

/****** Object:  LinkedServer [Server name or IP]    Script Date: 8/27/2014 4:50:48 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'Server name or IP', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Server name or IP',@useself=N'False',@locallogin=NULL,@rmtuser=N'DB user name',@rmtpassword='paessword'

GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'Server name or IP', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Adding DB_executor Role sql server

SQL Server has several fixed database roles such as db_datareader and db_datawriter, which grants the user read and write access respectively to all the tables in the database. Curiously there is no role to grant a user permission to execute stored procedures, but fortunately this is easily resolved by creating a new role.

The following SQL creates the new role in a database, and then grants it execute rights :

-- Create a db_executor role
CREATE ROLE db_executor

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor

A user can then be added to the new role, much like the db_datareader and db_datawriter roles. 

If you want to check that the role has been created and then add a user to the role, right click on a user in the database in SQL Server Management Studio and select ‘Properties’. In the ‘Database role membership’ control notice that the new db_executor role now appears, click the checkbox to add the user to the role, as below : 



Alternatively the user can be added to the role in code using the following SQL :

-- to allocate a user to the new role :
EXEC sp_addrolemember 'db_executor','SQLMatters'

A user added to this role will be able to execute all stored procedures in the database, including ones created in the future.

HTTP Error 404.13 - Not Found : The request filtering module is configured to deny a request that exceeds the request content length

Configuring via IISManager

Open the IIS Manager and select the site or application you need to configure in the left panel

Select "Features View" and double click on the "Request Filtering" icon.

 

Figure : Request Filtering

Note :

If you can't find the "Request Filtering"icon, you need to install the IIS Administration Pack from this link : http://www.iis.net/download/AdministrationPack. This is a lightweight installation which shouldn't take too much time on a decent internet speed.

Double clicking on the "Request Filtering"icon will bring up the Request filtering configuration window. Right click on the window and select the "Edit Feature Settings" option:

 

Figure : Edit Feature Settings option in IIS

Finally, specify the Maximum allowable content length (In Byte) in the following window and click "OK" to save:

 

Figure : Specifying Maximum allowable content length in Bytes

This worked perfect for me and I was able to upload the large file now without any problem. Hope, this will work for you too :)