The service could not be started. Reason: Service 'MSSQLServerOLAPService' start request failed

Problem:When i try reinstall Microsoft SQL Server Analysis Services it's keep failing.

Error Message: The service could not be started. Reason:  Service 'MSSQLServerOLAPService' start request failed.
 Configuration file:C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121217_132025\ConfigurationFile.ini
Detailed results:
  Feature:                       Analysis Services
  Status:                        Failed: see logs for details
  MSI status:                    Passed
  Configuration status:          Failed: see details below
  Configuration error code:      0xD448CDD2
  Configuration error description: The service could not be started. Reason:  Service 'MSSQLServerOLAPService' start request failed.
  Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121217_132025\Detail.txt

Workaround :
Open the Event Viewer(Administrative Tools) and Clear out all logs for
Application
Security
System
Finally restart your SQL Server Analysis Services via SQL Server Configuration Manager

References :http://richbackbi.wordpress.com/2010/12/03/mssqlserverolapservice-start-request-failed/
http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/dd04e9d8-e613-44bf-bf4a-a446efdc64aa

SQL Server Encryption

Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve access control problems. However, it enhances security by limiting data loss even if access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it is encrypted.

First of all run below T-SQL statment to get current value
select * from sys.dm_exec_connections
To find Encript_option column value ( It should be FALSE)

1. Open the SQL server Configuration Manager
2. SQL Server Network Configuration
   ( Protocols for MSSQLSERVER)  Right click to see propertise
3. You will see three tabs(Flags,Certificate,Advanced)
4. click Flags tab
Force encryption ( default is No)
 change to Yes

Then it is required to restart SQL Server service
Run the following  T-SQL statment
select * from sys.dm_exec_connections
To find encript_option column value ( It should be TRUE)

Important Note
Although encryption is a valuable tool to help ensure security, it should not be considered for all data or connections. When you are deciding whether to implement encryption, consider how users will access data. If users access data over a public network, data encryption might be required to increase security. However, if all access involves a secure intranet configuration, encryption might not be required. Any use of encryption should also include a maintenance strategy for passwords, keys, and certificates.
Source : http://technet.microsoft.com/en-us/library/bb510663.aspx

Pages and Extents in SQL Server

In SQL server data stored on page. Disk I/O operations are performed at the page level. A single page is consist of 8KB (8192 Bytes) and Eight physical contiguous pages are one Extents.

One Data Page (8KB) = 8192 Bytes

Page Header       =   96 Bytes
Data Rows         = 8060 Bytes
Row Offset Array  =   36 Bytes

Total Bytes per page is 8192 Bytes



If you want to read more about page and extents please check out this below Microsoft links
http://msdn.microsoft.com/en-us/library/ms190969(v=sql.105).aspx
Source : Microsoft

Know your Snapshot database in SQL Server

A snapshot database is a read-only purpose and  static view of source database. The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation.

1.       How do you create snapshot Database? Using

      T-SQL on Query Analyser

CREATE DATABASE [AdventureWorks_dbss1843] ON

( NAME = N'AdventureWorks_Data', FILENAME = N'E:\MSSQL\Data\AdventureWorks_data_1800.ss' )

AS SNAPSHOT OF [AdventureWorks]

GO

2.       Does snapshot database contain log file?

No

3.       If my snapshot database become suspect can you fix it ?

No

4.       Can you backup/restore snapshot database?

No

5.       Can you change recovery model for snapshot database?

No

6.       Can i create table/view in the snapshot database?

No

7.       Can i  use SSMS to create a snapshot database?

No

Source: Microsoft & Paul S Randal(SQLSkills)

E-Book Gallery for Microsoft Technologies

Big Thanks to Monica Rush for her contribution to publish Microsoft eBooks, these eBooks are free you can download it. There are more than 50 ebooks available here.

Download content for ASP.NET, Office, SQL Server, Windows Azure, SharePoint Server and other Microsoft technologies in e-book formats. Reference, guide, and step-by-step information are all available. All the e-books are free. New books will be posted as they become available.

To view white papers for Microsoft technologies, click here. To view community and partner content for Microsoft technologies, click here. To learn more about how to install and read e-book content on your device, click here.   To learn about new free e-books for Microsoft technologies, follow us on Twitter here . To learn about new white papers for Microsoft technologies, follow us on Twitter here . To provide feedback on the page or to suggest new content, click here.

Source : Microsoft Technet

http://social.technet.microsoft.com/wiki/contents/articles/11608.e-book-gallery-for-microsoft-technologies.aspx

Introducing Microsoft SQL Server 2012 free ebook

Complete version of Introducing Microsoft SQL Server 2012, by Ross Mistry  and Stacia Misner is now ready as a free download! You can download the PDF version of this title below link (288 pages; 10.8 MB).


http://blogs.msdn.com/b/microsoft_press/archive/2012/03/15/free-ebook-introducing-microsoft-sql-server-2012.aspx

Restore Database to Point in Time (STOPAT)

RESTORE LOG ... WITH RECOVERY, STOPAT option allows you to restore database backup to a point in time.  This option gives you the ability to restore a database backup file prior to an event that occurred.
Each of these transactions has a LSN (log sequence number) along with a timestamp, so when you restoring the transaction log you have the ability to tell SQL Server where to stop reading transactions that need to be restored.

For expample my database got currupted at -- 13:58.
I have full backup at 13:00 and Transaction log backup at  14:06
I want to restore the log at this point 'Nov 07, 2012 01:57:00 PM'

Use AdventureWorks

go

-- Full Backup has taken 13:00

Select * from Emp where EmployeeID =5;

--Title =Tool Designer
--13:46 

UPDATE Emp SET Title ='Tool Designer Engineer' where EmployeeID =5;

GO

Select * from Emp where EmployeeID =6;

--Title =Marketing Manager
-- 13:52

UPDATE Emp SET Title ='Assit Marketing Manager' where EmployeeID =6;

GO

-- Database has been corrupted
-- 13:58

Select * from Emp where EmployeeID =7;

--Title =Production Supervisor - WC60
-- 14:01

UPDATE Emp SET Title ='Production Supervisor' where EmployeeID =7 ;

GO

-- 14:06 Transaction Backup completed

-- Restore Full Database @ 13:00 with NORECOVERY

use master

GO

RESTORE DATABASE AdventureWorks FROM

DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_1.BAK',

DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_2.BAK',

DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_3.BAK'

WITH NORECOVERY , STATS = 5

GO

-- Point in time restore

-- Restoring now Transaction log backup WITH RECOVERY, STOPAT = 'Nov 07, 2012 01:57:00 PM'

RESTORE LOG AdventureWorks

FROM DISK ='E:\MSSQL\Backup\TRAN\AdventureWorks_7_Nov2012_14h6m_TRAN_1.BAK'

WITH RECOVERY, STOPAT = 'Nov 07, 2012 01:57:00 PM' , STATS = 5
GO

select EmployeeID,NationalIDNumber, ManagerID,Title

from Emp where EmployeeID IN(5,6,7)

Thanks to Viewers of this blog

I would like to say thanks to all my readers/viewers of this blog. I hope this blog helps you to either fix your SQL problem or learning new things in your life. I have decided to post at least 50 post per year and each post I am spending around 20 minutes to write it that means 50 posts x 20 minutes = 1000(minutes)/60 Minutes = 16.66 hours per year (Two working days) in my life spending here for SQL Community. Thanks very much for your valuable feedbacks and contacting me by email, I am trying to answer your question asap, If  i get chance to open my email box. So far my blog has had more than 60000 viewers visited.

The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
When you try to run ssis package  you will get this above error only if you are not started Distributed transaction coordinator.

Make sure to start the Distributed Transaction Coordinator via services

Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this

Error:
Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "SumSales_Summary_TestIndex" on view "AdventureWorks.dbo.vSumSales_Summary_TestByProductLine" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.

-- Table design
CREATE TABLE [dbo].[Sales_Summary_Test](
 [SaleID] [int] IDENTITY(1,1) NOT NULL,
 [OrderDate] [datetime] NULL,
 [ProductLine] [nvarchar](2) NULL, 
 [SubTotal] [numeric](38, 6) NULL) ON [PRIMARY]
GO

-- Creating  a View
CREATE VIEW dbo.vSumSales_Summary_TestByProductLine
     WITH SCHEMABINDING 
AS
SELECT s.ProductLine AS ProductLine,
 COUNT_BIG(*) AS NumberOfProductLine, 
 SUM(s.SubTotal) AS SubTotal
FROM [dbo].[Sales_Summary_Test] AS s
WHERE ProductLine IS NOT NULL
GROUP BY s.ProductLine
go

SELECT * FROM vSumSales_Summary_TestByProductLine
go

-- Create a Index for view
CREATE UNIQUE CLUSTERED INDEX SumSales_Summary_TestIndex
 ON dbo.vSumSales_Summary_TestByProductLine(ProductLine)
go

Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "SumSales_Summary_TestIndex" on view "AdventureWorks.dbo.vSumSales_Summary_TestByProductLine" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.

Solution:
You must change NULL column to NOT NULL column
CREATE TABLE [dbo].[Sales_Summary_Test](
 [SaleID] [int] IDENTITY(1,1) NOT NULL,
 [OrderDate]   [datetime] NOT NULL,
 [ProductLine] [nvarchar](2) NOT NULL,
 [SubTotal]    [numeric](38, 6) NOT NULL) ON [PRIMARY]
GO

Names must be in two-part format and an object cannot reference itself

When you try to create a Indexed view for the query you will get this following error message in this case. if you include database on from clause.

CREATE VIEW dbo.vSales_Summary_ProductLine
WITH SCHEMABINDING
AS
SELECT S.ProductLine AS ProductLine,
COUNT_BIG(*) AS NumberOfProductLine,
SUM(S.SubTotal) AS SubTotal
FROM [AdventureWorks].[dbo].[Sales_Summary] AS S
GROUP BY S.ProductLine
go

Msg 4512, Level 16, State 3, Procedure vSales_Summary_TestByProductLine, Line 4
Cannot schema bind view 'dbo.vSSales_Summary_ProductLine' because name 'AdventureWorks.dbo.Sales_Summary' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Solution :  You need to remove the database name from the above script

CREATE VIEW dbo.vSales_Summary_ProductLine
WITH SCHEMABINDING
AS
SELECT S.ProductLine AS ProductLine,
COUNT_BIG(*) AS NumberOfProductLine,
SUM(S.SubTotal) AS SubTotal
FROM [dbo].[Sales_Summary] AS S
GROUP BY S.ProductLine
go

How to find index usage against table

If you want to find out index usage for user table then  sys.indexes and sys.dm_db_index_usage_stats that give you more details. You should look for user seeks counts on this result

Select OBJECT_NAME(i.object_id) AS table_name,

i.name AS index_name,i.type_desc,
ius.user_seeks,ius.last_user_seek
From sys.indexes i
Inner Join sys.dm_db_index_usage_stats ius
on i.object_id = ius.object_id
and i.index_id = ius.index_id
and ius.database_id = DB_ID()
where ius.object_id = OBJECT_ID('dbo.Customers')
go

SQL Server 4.2

 

SQL Books Collection

SQL 2000 Books

SQL Server 2000 is best friend for DBAs

SQL 7.0 Books

The version SQL Server 7.0 are new era for SQL Server life for DBAs, I was one fortunate to read this books

SQL 6.5 Books

This is one of my favourite SQL Server 6.5 Book and SQL Server Performance Optimization and Tuning Handbook

SQL Server Connections 2012 at the Bellagio in Las Vegas

Oct 29-Nov 1 at the Bellagio in Las Vegas  for the hottest industry event fo this year 2012:

SQL Server Connections is packed with sessions from expert speakers providing you with valuable information that you can take back to work and put into practice immediately.

http://www.devconnections.com/shows/fall2012/default.aspx?s=191

ps1 cannot be loaded because the execution of scripts is disabled on this system

If your powershell script failed test.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details. At line:1 char:19
1. First run the powershell command

Get-ExecutionPolicy

You will see Restricted; if then

2. Enable powershell to run the below

Set-ExecutionPolicy RemoteSigned

You will see RemoteSigned:

Your powershell  script will work now onwards

SQL Server 2012 Express Editions

Microsoft also released a new set of SQL Server 2012 Express editions. SQL Server Express is a free edition of SQL Server, and it’s used as the internal database for hundreds of third-party products.
Although it's free, however, it also has several limitations. Compute-wise, the new SQL Server 2012 Express editions are limited to the lesser of one socket or four cores. The editions are also limited in size. The maximum memory for the database engine is limited to 1GB and the maximum relational database size is 10GB.
Although SQL Server Express is limited in scalability, a common misconception is that it’s limited to a single database or 10 connections. Neither of these is true. SQL Server Express can support multiple databases where each database can be up to 10GB. The myth about the number of connections is probably a holdover from the early days of MDSE (Microsoft Database Engine—the first version of the desktop SQL Server Engine that was released in the SQL Server 2000 timeframe.)
SQL Server 2012 Express has no limits on incoming connections or workload other than the CPU and memory limitations. And it comes in both 32-bit and 64-bit versions. You can download all of the SQL Server 2012 Express editions from Microsoft’s website.

Reference : http://www.microsoft.com/betaexperience/pd/SQLEXPCTAV2/enus/default.aspx

Microsoft Confirms October 25 Launch Event in NYC for Windows 8

While Windows 8 will be made generally available via software upgrades and new PC purchases starting October 26, Microsoft confirmed today that it will hold a launch event for the product on October 25 in New York City. This confirms reports that the firm would hold a “midnight madness” event the night before in order to celebrate the launch and let customers get their hands on Windows 8 as soon as possible.

My Windows Weekly co-host Mary Jo Foley wrote about the October 25 event in Mark your Microsoft calendars: Windows 8, Windows Phone 8 launch dates revealed last month. “October 25, the day before general availability, will be Microsoft's big launch event for Windows 8 and the Surface RT,” she wrote. “And that event will be in New York City, the site of most recent previous Windows launches. Windows 8 and the Surface RT ARM-based devices should be available (at least technically) at midnight, following the launch event.”

Partitioning Large Table for 1120 millions Rows with SQL Server 2008R2

Create partitioned for existing table with more than 1120 millions (1120962253) rows today. It was amazing to see the query performance within few seconds with my client database.

Make sure to create a files groups ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4] for partitioned table 
Also you can create Separate file group for Non-Clustered Index  on different drive NCINDEX_FG5

USE [TestDB]
GO

--Step 1. Creating a Partition Function
CREATE PARTITION FUNCTION PFSvrId_Left (numeric (10,0))
AS RANGE LEFT FOR VALUES (399, 499, 699, 799);

--The result for this RANGE LEFT assignment is:
--{min … 399}, {400 … 499}, {500 … 699}, {799 … max}

--Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME SvrIdScheme
AS PARTITION  PFSvrId_Left
TO ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4],[PRIMARY])

--Step 3. CREATE CLUSTERED INDEX 1

-- Now create a Partitioned using clustered index based Scheme
CREATE CLUSTERED INDEX [idx_LoadID] ON [dbo].[MyTable]
(
      [M_ID] ASC
) ON SvrIdScheme(s_id)

-- Step 4.  CREATE NONCLUSTERED INDEX 2 on Separate file group  
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(  [t_id] ASC,  [s_id] ASC
)ON [NCINDEX_FG5] 
GO

--– Check for new partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyTable')
 GO

Creating a database with multiple file groups

USE [master]
GO
/****** Object:  Database [Testdb]    Script Date: 09/18/2012 16:52:59 ******/
CREATE DATABASE [Testdb] ON  PRIMARY
( NAME = N'Testdb', FILENAME = N'F:\SQLDBs\Testdb.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_DATA]
( NAME = N'Test_Data', FILENAME = N'F:\SQLDBs\Test_Data.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_DATA2]
( NAME = N'Test_Data2', FILENAME = N'F:\SQLDBs\Test_Data2.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_INDEX]
( NAME = N'Test_Index', FILENAME = N'F:\SQLDBs\Test_Index.ndf' , SIZE = 5242880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'Testdb_log', FILENAME = N'G:\SQLLogs\Testdb_log.ldf' , SIZE = 1048576KB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [Testdb] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Testdb].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

Blog status

Thanks very much for everyone to visitng this blog, It is really amazing journey to more than 51000 hits.

 

 

Download Service Pack 2 for Microsoft® SQL Server® 2008 R2

Make sure to read KB article 2630458 before you install SP2

Download Service Pack 2 for Microsoft® SQL Server® 2008 R2 from the following web link

http://www.microsoft.com/en-gb/download/details.aspx?id=30437

Index was outside the bounds of the array

An error occurred while executing batch. Error message is: Index was outside the bounds of the array.
Today i come up with the above error on development box. A table is tiny and only 89 thousands rows. I found the fowllowing two links from microsoft site.
http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/d30194ae-9f6a-46ab-b054-6dc2ed9195c2
http://support.microsoft.com/kb/2459027
I am running SQL Server version Microsoft SQL Server 2008 R2 (SP1) - 10.50.2789.0 (X64)

I try to reproduce the same from created test database on the same error but it did not show up the same erro.

Resolution: Create a replica table of issue table and export the data that is works fine.

How to create a Snapshot database

sp_helpdb MyTest
go

-- The following script is easiest way to create a Snapshot database. 

CREATE DATABASE MyTest_snapshot_db ON
( NAME = MyTest,
FILENAME = 'D:\MSSQL\DATA\MyTest_snapshot_db.ss' )
AS SNAPSHOT OF MyTest;
go

Note: Remember you can't create a snapshot database using SSMS but you can delete snapshot database using ssms.

[SQLSTATE 42000] (Error 10060) OLE DB provider "SQLNCLI10" for linked server

[SQLSTATE 42000] (Error 10060)  OLE DB provider "SQLNCLI10" for linked server

Error Message
Executed as user: MyServer\Myaccount. TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. [SQLSTATE 42000] (Error 10060)  OLE DB provider "SQLNCLI10" for linked server "" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI10" for linked server "MYSERVER" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412).  The step failed.
Open Component Services via

 Run then dcomcnfg


To resolve this problem, follow these steps:

1. Click Start, point to All Programs, point to Administrative Tools, and then click Component Services.
2. In the Component Services Wizard, expand Component Services, and then double-click Computers.
3. Right-click My Computer, and then click Properties.
4. Click the MS DTC tab, and then click Security Configuration.
5. In the Security Configuration dialog box, click to select the Network DTC Access check box.
6. Under Network DTC Access, click Network Transactions.
   
   Note If you installed Service Pack 1 for Windows Server 2003, you can click Allow Inbound and Allow Outbound.
7. Make sure that DTC Logon Account is set to NT Authority\NetworkService.
8. Click OK.
9. In the message box, click Yes to continue.
10. In the DTC Console Message dialog box, click OK.
11. In the System Properties dialog box, click OK.
12. Reboot the computer for these changes to take effect.
    
    Note In some cases, you must start the DTC service before you start the SQL Server service so that the linked server distributed queries work well.
13. Add above DTC configuration on both server
14. You need  add XACT_ABORT ON on your stored procedure

Server: Msg 3013 RESTORE DATABASE is terminating abnormally.

Server: Msg 3013
RESTORE DATABASE is terminating abnormally.

Server: Msg 3159
The tail of the log for the database "DBTest" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Solution : If you want to restore the database on to new server where you created the database was never backup before then you need to take backup of your database on new server ( mostly empty db) before restore the database from the backup file.

[264] An attempt was made to send an email when no email session has been established

You need to enable mail profile located in the properties of the SQL Agent,
Alert System and choose Mail System and Mail profile from the drop down listbox.
Finally you must restart the SQL Server Agent and run your scheduled job to get email notification

SQLServer Error: 15404, Could not obtain information

Date  25/06/2012 08:40:01
Log  SQL Server Agent (Current - 25/06/2012 08:47:00)
Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'servrername\SQLAgent', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin),

If you ever clone sql server from other server. you might end up with this above error when you run any sql server agent jobs. Because name of the current sql server and oringally installed sql server is totally different.

Robocopy files to another Drive

Robocopy files to another Drive with estimated time to complete
If you ever plan to move the sql data(mdf) or log file (LDF) to another drive then try to use robocopy is much better than windows file copying and takes less time.

In that command prompt e.g:
F:\Robocopy  "E:\SQLData" "F:\SQLData" mydb.mdf mydb.mdf /ETA

E:\Robocopy  "D:\SQLLogs" "G:\SQLLogs" mydb_log.ldf mydb_log /ETA

How to import sql server profiler trace file into table

If you want to analyse the sql profiler output then import into table using a fn_trace_gettable.

USE Mydbname
GO
SELECT * INTO trace_table
FROM ::fn_trace_gettable('C:\Temp\myFile.trc', default) 

Stripe SQL Backups on multiple files

-- FULL Backups
DECLARE @DBName SYSNAME, @BUFileName VARCHAR(256) , @BULocation VARCHAR(256), @SQLCommand NVARCHAR (1000)  
SET @DBName = 'mydbname'
SET @BUFileName = @DBName + '_' + DATENAME(dw, GETDATE()) +  '_' + DATENAME(dd, GETDATE()) + '_'
  + DATENAME(mm, GETDATE()) + DATENAME(YEAR, GETDATE()) +
  + '_' + DATENAME(hh, GETDATE()) + 'h' + DATENAME([MINUTE], GETDATE()) + 'm_Full_'
SET @BULocation = 'K:\MSSQL\Backups\'
-- PRINT @BUFileName   
SET @SQLCommand = 'BACKUP DATABASE ' + @DBName + ' TO
   DISK = ''' + @BULocation + @BUFileName + '1.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '2.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '3.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '4.BAK''' +',
   WITH
  NOFORMAT, INIT, NOREWIND, NOUNLOAD,
  COMPRESSION, STATS = 10'
-- PRINT @SQLCommand
EXEC sp_executeSQL @SQLCOMMAND 



--DIFFERENTIAL Backups
DECLARE @DBName SYSNAME, @BUFileName VARCHAR(256) , @BULocation VARCHAR(256), @SQLCommand NVARCHAR (1000)  
SET @DBName = 'mydbname'
SET @BUFileName = @DBName + '_' + DATENAME(dw, GETDATE()) +  '_' + DATENAME(dd, GETDATE()) + '_'
  + DATENAME(mm, GETDATE()) + DATENAME(YEAR, GETDATE()) +
  + '_' + DATENAME(hh, GETDATE()) + 'h' + DATENAME([MINUTE], GETDATE()) + 'm_Full_'
SET @BULocation = 'K:\MSSQL\Backups\'
-- PRINT @BUFileName   
SET @SQLCommand = 'BACKUP DATABASE ' + @DBName + ' TO
   DISK = ''' + @BULocation + @BUFileName + '1.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '2.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '3.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '4.BAK''' +',
   WITH DIFFERENTIAL,
  NOFORMAT, INIT, NOREWIND, NOUNLOAD,
  COMPRESSION, STATS = 10'
-- PRINT @SQLCommand
EXEC sp_executeSQL @SQLCOMMAND 

An attempt was made to send an email when no email session has been established

When you get this error message on your sql server agent Error Logs
Make sure to enable profiles on sql server agent propertise

1) Right click SQL Server Agent ---> Propertise
2) Select Alert Systems
3) Checkbox Enable Mail profile

The report server cannot process the report or shared dataset

The report server cannot process the report or shared dataset. The shared data source 'DataSource' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)

1. Click your report file for eg. MyReports(rdl)
 You can see the propertiese left hand side
2. Click the file Data Sources
  a) A custom Ddata source
             Data Source Type  : Microsoft SQL Server
             Connection String : Data Source=myservername; Initial Catalog=DBName
  b) Connect using
    Credentials stored securely in the report server
            User name : myreportlogin
            Password  : mypwd
3. Test Connection
4. Back to report folder and refresh your reports

Database Recovery model and backup types

When you try to configure the Database recovery option for backup strategy make sure to select the correct recovery model . If you need further more about this read from microsoft web.

Database	Backup Type
Recovery_Model	Full	Differential	Transaction
FULL	Yes	Yes	Yes
Bulk-logged	Yes	Yes	Yes
Simple	Yes	Yes	No

How to restore database from RedGate SQLBackup Files

If you are using RedGate Sql backup to restore the database then you can use following t-sql code

EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [AdventureWorks] 
FROM
   DISK = ''\\myserver\Backup\AdventureWorks_01.sqb'',
   DISK = ''\\myserver\Backup\AdventureWorks_02.sqb''
WITH RECOVERY,
    MOVE ''AdventureWorks''     TO ''H:\MSSQL\Data\AdventureWorks.mdf'',
    MOVE ''AdventureWorks_log'' TO ''I:\MSSQL\Logs\AdventureWorks_log.ldf'',
REPLACE"'

How to start sql server without tempdb

When i try to take image of sql server for installation to new server  i could not restart sql server service on new server due to tempdb database files missing. In that image server where tempdb database files were placed on E:\mssql\data drive and  new sql server only got C: drive  and all system dbs there on c: drive  files. This solution is tested and worked fine.

--  SQL Server Path 
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
Step:1  You are going to  start sql using single user with minimal configuration on command prompt
 cmd-- > sqlservr -f -m
Now you create Folder on 'C:\MSSQL\DATA\
-- Open the second window for command prompt
c:\sqlcmd
1>
use master
go
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='C:\MSSQL\DATA\Tempdb.MDF')
go
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='C:\MSSQL\DATA\Templog.LDF')
go
>quit

How to attach a data file MDF without log file (LDF)

USE master
go
CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\MSSQL\Data\AdventureWorks2008R2_Data.mdf') -- change the drive and file path
FOR ATTACH_REBUILD_LOG ;

Find Drive size and free space

-- This query will run SQL Server 2008 R2 only
SELECT distinct vs.volume_mount_point AS DriveName,
vs.total_bytes/1073741824 as TotalSize_GB,
vs.available_bytes/1073741824 AS FreeSpace_GB,
(vs.total_bytes/1073741824) -
(vs.available_bytes/1073741824) AS SpaceUsed_GB,
CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,3)) * 100 AS [SpaceUsed %]
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY vs.volume_mount_point

SQL SERVER 2012 available now

The new vesion of SQL Server 2012 is released today.
http://www.microsoft.com/sqlserver/en/us/default.aspx

You can download evaluation copy from
http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

How to insert rows into table with identity column

Imagine you have  two billion rows on employee table, you have to archive this table. After archiving you are going to insert back only 110 million rows to employee table from employee_temp, There is a column emp_id is identity, so how can you insert data back from archived tables.  Here you can find the tested solution , first you need to disable an indentity column to auto insert.

Step 1: Make sure to stop all process(application connection) against employee table
Step2 :Create a new table employee_copy from employee table
           Find max(emp_id) from employee eg: 2000000001and  RESEED this value to employee_copy
           DBCC CHECKIDENT (employee_copy , RESEED, 2000000001)
Step3:  Rename the orginal table using sp_rename 'employee' , 'employee_temp'
Step4: Rename the new table using sp_rename 'employee_copy' , 'employee'
Step5:  Insert the rows from employee_temp table to employee table

SET IDENTITY_INSERT employee ON
GO
INSERT INTO employee (emp_id, emp_name, country_code)
SELECT emp_id, emp_name, country_code from employee_temp(nolock) where emp_id > 110000000
GO
SET IDENTITY_INSERT employee OFF
GO