Tuesday, April 17, 2012

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

Tuesday, April 10, 2012

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

Monday, April 09, 2012

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 FullDifferentialTransaction
FULLYesYesYes
Bulk-loggedYesYesYes
SimpleYesYesNo

Friday, April 06, 2012

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"'

Thursday, April 05, 2012

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