SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database

In this article, I discuss about the Database Mail which is used to send the email using SQL Server.

Database mail is the replacement for SQLMail with many enhancements. So one should stop using SQLMail and upgrade to the Database Mail.

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out:

1. Create Profile and Account
2. Configure Email
3. Send Email

Step 1: Create Profile and Account

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node.

This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

step 1.1

step 1.2

step 1.3

step 1.4

step 1.5

step 1.6

step 1.7

step 1.8

step 1.9

step 1.10

step 1.11

Step 2: Configure Email

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE _
 'Database Mail XPs', 1 GO RECONFIGURE GO 

Step 2

Step 3: Send Email

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb GO EXEC sp_send_dbmail @profile_name='PinalProfile', _
@recipients='test@Example.com', @subject='Test message', _
@body='This is the body of the test message. _
Congrats Database Mail Received By you Successfully.' 

Step 3.1

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker. Read more at SQL SERVER - Introduction to Service Broker.

Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems,sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems. The status of the mail sent can be seen in sysmail_mailitems table. When the mail is sent successfully, the sent_status field of thesysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that have failed will have the sent_status field value to 2 and those are unsent will have value 3. The log can be checked in sysmail_log table as shown below:

SELECT * FROM sysmail_mailitems GO SELECT * FROM sysmail_log GO 

Step 3.2

Status can be verified using sysmail_sentitems table.

Step 3.3

After sending mail, you can check the mail received in your inbox.

Important Searches :-

• How to send email from sql server 2008
• How to send email from sql serve
• How to send email from sql 
• How to Configure Mail in Database 
• How to Configure Database Mail

credits - codeproject

No Comments

SQL Server Transaction Log Backups

OverviewIf your database is set to the "Full" or "Bulk-logged" recovery model then you will be able to issue "Transaction Log" backups.  By having transaction log backups along with full backups you have the ability to do a point in time restore, so if someone accidently deletes all data in a database you can recover the database to the point in time right before the delete occurred.  The only caveat to this is if your database is set to the "Bulk-logged" recovery model and a bulk operation was issued, you will need to restore the entire transaction log.

ExplanationA transaction log backup allows you to backup the active part of the transaction log.  So after you issue a "Full" or "Differential" backup the transaction log backup will have any transactions that were created after those other backups completed.  After the transaction log backup is issued, the space within the transaction log can be reused for other processes.  If a transaction log backup is not taken, the transaction log will continue to grow.

A transaction log backup can be completed either using T-SQL or by using SSMS.  The following examples show you how to create a transaction log backup.

---

Create a transaction log backup of the AdventureWorks database to one disk file

T-SQL

BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN' GO

SQL Server Management Studio

• Right click on the database name
• Select Tasks > Backup
• Select "Transaction Log" as the backup type
• Select "Disk" as the destination
• Click on "Add..." to add a backup file and type "C:\AdventureWorks.TRN" and click "OK"
• Click "OK" again to create the backup

No Comments

How to take File Backups in SQL Server ?

OverviewAnother option for backing up your databases is to use "File" backups. This allows you to backup each file independently instead of having to backup the entire database. This is only relevant when you have created multiple data files for your database.  One reason for this type of backup is if you have a very large files and need to back them up individually. For the most part you probably only have one data file, so this is option is not relevant.

ExplanationAs mentioned above you can back up each data file individually. If you have a very large database and have large data files this option may be relevant.

A file backup can be completed either using T-SQL or by using SSMS. The following examples show you how to create a transaction log backup.

---

Create a file backup of the TestBackup database

For this example I created a new database called TestBackup that has two data files and one log file. The two data files are called 'TestBackup' and 'TestBackup2'. The code below shows how to backup each file separately.

T-SQL

BACKUP DATABASE TestBackup FILE = 'TestBackup' TO DISK = 'C:\TestBackup_TestBackup.FIL' GO BACKUP DATABASE TestBackup FILE = 'TestBackup2' TO DISK = 'C:\TestBackup_TestBackup2.FIL' GO

SQL Server Management Studio

• Right click on the database name
• Select Tasks > Backup
• Select either "Full" or "Differential" as the backup type
• Select "Files and filegroups"
• Select the appropriate file and click "OK"

• Select "Disk" as the destination
• Click on "Add..." to add a backup file and type "C:\TestBackup_TestBackup.FIL" and click "OK"
• Click "OK" again to create the backup and repeat for other files

No Comments

SQL Server Differential Backups

OverviewAnother option to assist with your recovery is to create "Differential" backups.  A "Differential" backup is a backup of any extent that has changed since the last "Full" backup was created.

ExplanationThe way differential backups work is that they will backup all extents that have changed since the last full backup.  An extent is made up of eight 8KB pages, so an extent is 64KB of data.  Each time any data has been changed a flag is turned on to let SQL Server know that if a "Differential" backup is created it should include the data from this extent.  When a "Full" backup is taken these flags are turned off.

So if you do a full backup and then do a differential backup, the differential backup will contain only the extents that have changed.  If you wait some time and do another differential backup, this new differential backup will contain all extents that have changed since the last full backup.  Each time you create a new differential backup it will contain every extent changed since the last full backup.  When you go to restore your database, to get to the most current time you only need to restore the full backup and the most recent differential backup.  All of the other differential backups can be ignored.

If your database is in the Simple recovery model, you can still use full and differential backups. This does not allow you to do point in time recovery, but it will allow you to restore your data to a more current point in time then if you only had a full backup.

If your database is in the Full or Bulk-Logged recovery model you can also use differential backups to eliminate the number of transaction logs that will need to be restored.  Since the differential will backup all extents since the last full backup, at restore time you can restore your full backup, your most recent differential backup and then any transaction log backups that were created after the most recent differential backup.  This cuts down on the number of files that need to be restored.

---

Create a differential backup of the AdventureWorks database to one disk file

T-SQL

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.DIF' WITH DIFFERENTIAL GO

SQL Server Management Studio

• Right click on the database name
• Select Tasks > Backup
• Select "Differential" as the backup type
• Select "Disk" as the destination
• Click on "Add..." to add a backup file and type "C:\AdventureWorks.DIF" and click "OK"
• Click "OK" again to create the backup

No Comments

How to get Database Backup History for a Single Database ?

Here is the script suggested by SQL Expert, who has written excellent script which goes back and retrieves the history of any single database.

USE AdventureWorks
GO-- Get Backup History for required databaseSELECT TOP 100
s.database_name,m.physical_device_name,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB'AS bkSize, CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,CASE s.[type] WHEN 'D' THEN 'Full'WHEN 'I' THEN 'Differential'WHEN 'L' THEN 'Transaction Log'END AS BackupType,s.server_name,s.recovery_modelFROM msdb.dbo.backupset sINNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id =m.media_set_idWHERE s.database_name = DB_NAME() -- Remove this line for all the databaseORDER BY backup_start_date DESC, backup_finish_date
GO

Very neat script and in my above example I have ran that for single database adventureworks and you can see following results. The same can be ran for multiple database as well if you just remove the WHERE condition.

No Comments

How to create database with user defined size (Fixed Database Size) ?

USE master
GO
CREATE DATABASE database
ON
( NAME = Empl_dat,
   FILENAME = 'f:',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
   FILENAME = 'g:',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )
GO

Just use above query and replace database with your database name and logon sizes as per your requirements.. 

How to create database without specifying its size ?

No Comments

How to create database without specifying its size ?

This example creates a database named sales with three filegroups:

• The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increments for these files is specified as 15 percent.
• A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.
• A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.

CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri1dat.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 15% ),
( NAME = SPri2_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )
GO

G. Attach a database

Example B creates a database named Archive with the following physical files:

c:\program files\microsoft sql server\mssql\data\archdat1.mdf
c:\program files\microsoft sql server\mssql\data\archdat2.ndf
c:\program files\microsoft sql server\mssql\data\archdat3.ndf
c:\program files\microsoft sql server\mssql\data\archlog1.ldf
c:\program files\microsoft sql server\mssql\data\archlog2.ldf

The database can be detached using the sp_detach_db stored procedure, and then reattached using CREATE DATABASE with the FOR ATTACH clause:

sp_detach_db Archive
GO
CREATE DATABASE Archive
ON PRIMARY (FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf')
FOR ATTACH
GO

No Comments

How to delete records from one table using join with another table ?

So today our missions is to delete all employee records if in the title of their department can be found word "sales". It can happen in the real life. Imagine that newly elected CEO decides to close all sales operations.

MS Access syntax will be:

1. The well known way, that uses sub-query:

DELETE *
FROM Employees
WHERE DeptNo IN
  (SELECT DeptNo
  FROM Departments
  WHERE LCase(DeptName) LIKE '*sales*')

2. The more efficient way is to use "Delete Join":

DELETE a.*
FROM Employees AS a INNER JOIN Departments AS b
ON a.DeptNo = b.DeptNo
WHERE LCase(b.DeptName) LIKE '*sales*'

3. Same query ("Delete Join") on SQLServer 2005:

DELETE a
FROM Employees AS a INNER JOIN Departments AS b
ON a.DeptNo = b.DeptNo
WHERE LOWER(b.DeptName) LIKE '%sales%'

4. Abstruct query ("Delete Join") on SQLServer 2005/2008:
Suppose that we have two tables. Fitting numbers in id column are marked by green color.

Table1	Table2
id ItemName 1 item1 2 item2 4 item4 5 item5	id 2 3 5 9

Following delete statement will remove records with id 2 and 5 from Table1:

DELETE a
FROM Table1 AS a INNER JOIN Table2 AS b
ON a.id = b.id 

Important Key Words :

How to delete records from one table using join with another table
Delete records from one table using join with another table
Delete records using join queries
Delete records join query

No Comments