SQL Server Portal

I received this query recently, when I was recovered one of my blog reader data. Once the recovery is done, I advised him to take a backup on regular basis etc etc. Next morning, I receive an email from him with a screen shot showing that, there is no option to take Transaction Backup in his SQL Server. I recreated the same screen shot, as shown below.

So What is the solution ? Basically your database is in SIMPLE recovery model due this you do not have Transaction Backup option. So In order to take transaction backup, you need to change your database recovery options from SIMPLE to FULL. Lets do it step by step.

Step 1 :
Open your SQL Server management studio than right click on the database and select database properties as shown in the screen shot below.

Step 2 :
Select the options and further select the recovery model as FULL on your right hand side and press OK as shown in the screen shot below.

Step 3 :
Now, you need to go back and take the backup and this time you will find the transaction backup option as shown in the screen shot below.

Note : It is recommended to change your recovery model from simple to FULL