Posts Tagged ‘Simple Recovery model’

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.

transaction backup.1.1

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.

transaction backup.1.4

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.

transaction backup.1.2

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.

transaction backup.1.3

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

Read Full Post »