SQL Server Backup and Recovery Models

http://www.mssqltips.com/tip.asp?tip=1219

I have never been able to figure out the mystery of why transaction log backup always failed until today. It's definitely a huge oversight on my behalf. Here is one good link on the recovery models of each system database in SQL Server:

http://msdn.microsoft.com/en-us/library/ms365937.aspx

To sum it up:

Master (Simple) - Okay to backup the database file but NOT the transaction log

Model (Full) - Full backup on both database file and transaction log

msdb (Simple) - changed to Full Recovery Model is highly recommended. Full backup on both database file and transaction log

tempdb (Simple) - Simple Recovery Model is REQUIRED. You CANNOT backup tempdb database.

Hope this reminds and helps the ones who find the tips helpful! Cheers!

SQL Server 2005 Database Email setup and stored procedures

Here's a great article on how to setup the Database E-mail accordingly in SQL Server 2005:

http://www.mssqltips.com/tip.asp?tip=1438

And the T-SQL way to do it as well as a sample use of the sp_send_dbmail Stored Procedure:

http://www.dotnetspider.com/resources/19638-Sending-email-through-sql-server-stored-procedure.aspx

I personally find Database E-mail on SQL Server 2005 a step forward from SQL Server 2000. It did away the buggy MAPI mail profile and instead allows you to create Mail Profiles and multiple SMTP Accounts under each Mail Profile.

Mail Profile is like an "umbrella" that may consist one or more SMTP accounts. The Mail Profile name is also what's been used in the sp_send_dbmail Store Prodecure to send e-mails.

SMTP account is the actual configuration of which SMTP server to use, the originator's e-mail address, etc.

One of the advantage of Mail Profile is the ability to configure multiple SMTP accounts. It could be used as a "fail over", in the event that if the first SMTP account failed to send e-mail for whatever reason, it would automatically use the second SMTP account to send the same e-mail.

The sp_send_dbmail Stored Procedure also has some significant improvements and provide additional flexibility.

Finally, database developers are able to utilize Stored Procedure to send e-mails directly from within a Scheduled Task or Stored Procedure, instead of relying on external programs for sending e-mails.

Hopefully you would find this article a bit helpful! I welcome any comments and questions alike. I would also continue to modify this post as I dig deeper in Database Email and Stored Procedure in SQL Server 2005.

Cheers! Thanks for reading!