SQL Full, Differential, Transaction log Backup

How to Change Simple Mode to Full Mode

In order to take a transaction log backup, you first need to check if the database is in Full Recovery model, if the database is in Simple Recovery model, you do not need to take transaction log backups.

Steps:

1.     Login into SQL Server Management Studio. 

2.     Right click on the database and choose Properties.

 

    3.     Select Option, Select Mode from Recovery model drop-down list.

 

  

How to schedule a backup

Steps:

1.     Logon into SQL Server Management Studio.

2.     Go to Management then Maintenance Plans.

    3.      Right click on Maintenance Plans and choose “Maintenance Plan    Wizard”.

  

4.     Click on Next to skip the introduction screen.

5. In the next menu, give a Name to the maintenance plan and also change the Schedule of the maintenance plan (how often the backup will be executed) by selecting single schedule for entire plan. Click on Next.

   6.  Then choose the actions that the Maintenance Plan will do. Here you select the option “Back Up Database (Full)”.
 

Optionally, you may want to also select the task called “Clean Up History”. This task deletes the history for old backup/restore operations. Or you may also select the task called “Maintenance Cleanup Task”, which deletes old text files that were produced by old maintenance plan executions.

7.     Then specify the Maintenance Task order (in what order the tasks will be executed).

8.    In the next menu, select the databases for which you want backup (Full) to be executed. Also select where you want to back up the database’s Full e.g. on a disk drive (on a specific folder) or on a tape drive. Be careful to specify an existing valid folder (if the folder does not already exist, you will get an error message). Also choose to verify backup integrity, to make sure the backup you captured is valid.

  

9.   In the next menu, choose the text files (the maintenance plan reports) that you want to delete. This menu will not appear if you had not previously selected the “Maintenance Cleanup Task” in step (6).

10.   In the next menu you define how old is the historical data you want to delete. If you haven’t selected the task “Clean Up History” in step (6) you will not see this menu.

11.   In the next menu, you can optionally choose to generate a report for this maintenance plan, for each time it is executed. Generating a report will help you identify any issues with the execution plan of the maintenance plans.

12.  In the next menu, you can review the actions you have already selected. Then by pressing “Finish” you create the Maintenance Plan.

13.  Follow steps 1-12 to create Maintenance Plans for Differential, Transaction log.

14.    Start SQL Server Agent.