What is Recovery Model?
Recovery Model is actually a transaction log way. It has three types: Simple, Bulk-Logged and Full.
Simple recovery model leads to database engine mimimal transaction log for most operations and truncating the transaction log after each checkpoint. In this mode, "backup log" and "restore log" operations are not allowed;
Bulk-logged recover model leads to mimimal transaction log for bulk operations such as BULK INSERT and SELECT INTO. You can restore the database to the end of the log backup.
Full recover model has the database engine to log all operations into the transaction log.
Query and Change Recovery Model
We can query database recovery models by running the following statement:
SELECT name, recovery_model_desc FROM sys.databases
To change the recovery model of a database, you can use SSMS (SQL Server Management Studio) or run "ALTER DATABASE SET RECOVERY {SIMPLEBULK_LOGGEDFULL}".
-------------------------------------------------------------------
This article is owned by Wacle.
If you have any questions, please contact wacle.wang@gmail.com
-------------------------------------------------------------------
Today I tried the new feature of sending email via SQL Server 2005 database mail which replaces the old SQL mail in SQL Server 2000. Of course, using SQL Server Management Studio is a convenient way to configure the database mail, but I would like to talk about how to use stored procedures to configure and send an email in SQL Server 2005.
The configuration components in SQL Server 2005 include two parts: one is Database Mail Account configuration; the other is Database Mail Profile configuration. SQL Server 2005 provides the following stored procedures in msdb database to manage the configurations:
sysmail_add_account_sp
sysmail_add_principalprofile_sp
sysmail_add_profile_sp
sysmail_add_profileaccount_sp
sysmail_configure_sp
sysmail_delete_account_sp
sysmail_delete_principalprofile_sp
sysmail_delete_profile_sp
sysmail_delete_profileaccount_sp
sysmail_update_account_sp
sysmail_update_principalprofile_sp
sysmail_update_profile_sp
sysmail_update_profileaccount_sp
Assume that you have an internet email account testemail@126.com, you would like to send an email from the email address in SQL 2005. Then you can refer to the steps as follows:
1. Configure your SQL Server instance to allow SQL Database Email.
sp_configure 'show advanced options',1
Go
reconfigure
Go
sp_configure 'Database mail XPs',1
Go
2. Add an email account.
execute msdb.dbo.sysmail_add_account_sp
@account_name='Test DBMail Account',
@description='Test mail account for Database mail',
@email_address = 'testemail@126.com',
@display_name='Test Database mail',
@mailserver_name = 'smtp.126.com',
@mailserver_type = 'SMTP',
@port = 25,
@username = 'testemail',
@password = 'testemail',
@use_default_credentials = 0,
@enable_ssl = 0
Go
3. Add an profile
execute msdb.dbo.sysmail_add_profile_sp
@profile_name='Test DBMail Profile',
@description = 'Profile for testing Database mail'
Go
4. Relate the account to the profile
execute msdb.dbo.sysmail_add_profileaccount_sp
@profile_name='Test DBMail Profile',
@account_name='Test DBMail Account',
@sequence_number = 1
Go
5. Add profile principal and set it as the default profile
execute msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Test DBMail Profile',
@principal_name = 'public',
@is_default = 1
Go
After the above configurations, test sending an email via the following stored procedure:
execute msdb.dbo.sp_send_dbmail @profile_name = 'Test DBMail Profile',
@recipients = 'test@myemailserver.com',
@subject = 'Test Database Mail',
@body = 'Phenix Sun lost in the game 6. Nash cried to go back home.'
Go
To check if your email has been sent, you can run "select * from msdb.dbo.sysmail_mailitems" to check if the sent_status is 1. If your email fails to be sent, run "select * from msdb.dbo.sysmail_event_log" to check the error information.
As you can see, it is also very easy of configurations to send an email in SQL Server 2005.
-------------------------------------------------------------------
This article is owned by Wacle.
If you have any questions, please contact wacle.wang@gmail.com
-------------------------------------------------------------------
Recently I received one post from my customer regarding how to convert ISA ClientIP to our familiar IPv4 format.
I did not find any document regarding the rule of ClientIP in ISA log, but I believed that it was a LONG INT value converted from binary expression. Based on this logic, the conversion rule should be as follows:
1. Convert the number into 32 bit binary expression;
2. Separate the binary into four group by 8 bit;
3. Convert each 8 bit into a decimal value and concate them with a dot.
Then I write the following function for parse ClientIP value in ISA log.
=======================================
create function ufn_getIPAddr(@nIP bigint)
returns nvarchar(20)
as
begin
declare @binIP varbinary(4)
declare @h1 varbinary(1)
declare @h2 varbinary(1)
declare @h3 varbinary(1)
declare @h4 varbinary(1)
declare @strIP nvarchar(20)
SELECT @binIP = CONVERT(VARBINARY(4),@nIP )
select @h1 = SUBSTRING(@binIP,1,1)
select @h2 = SUBSTRING(@binIP,2,1)
select @h3 = SUBSTRING(@binIP,3,1)
select @h4 = SUBSTRING(@binIP,4,1)
select @strIP = CONVERT(nvarchar(3),CONVERT(int,@h1))+'.'
+CONVERT(nvarchar(3),CONVERT(int,@h2))+'.'
+CONVERT(nvarchar(3),CONVERT(int,@h3))+'.'
+CONVERT(nvarchar(3),CONVERT(int,@h4))
--print @strIP
return @strIP
end
==========================================
Execute the statement：
select dbo.ufn_getIPAddr(3232240156)
Output：
192.168.18.28
-------------------------------------------------------------------
This article is owned by Wacle.
If you have any questions, please contact wacle.wang@gmail.com
-------------------------------------------------------------------
