Open management studio >>connect to SQL Server>>Expand Management>>Right Click Maintenance plan>>Select New Maintenance Plan

Give name as you like ( FullBackupDaily) it will open designer window.Left side you can see ToolBox tab.

Select Backup Database task and double click or drag and drop it to designer pane.

Edit the Task for options>> connection as local,Backup type as Full,Specify the Databases which u want to include for this backup.

Provide valid path s backup directory (check backup integrity for best practice)

2. just full backup –already covered in above steps

3. full bak file should be with date, like testdb20110708.bak, testdb20110709.bak, consequently day by day.

our plan will create backups as you expect,Now schedule it as per your requirement.

to schedule please chek for ” subplan schedule” option.

select frequency as daily ,time as 5 Am.

4. just keep the recent 2 days bak files, like testdb20110708.bak and testdb20110709.bak

We can use Maintenance cleanup task to achieve this

drag and drop Maintenance cleanup task from ToolBox >>edit >>select backup files>>provide your backup files location>>file extension as Bak>>Check Delete files as ..>>Provide 2 days in Delete files older than following box.

5. backup to a remote disk–already covered in above steps,but i recommend you to take backup into local drive first, later you can copy to remote disk if require.

6. if could, how to fullfil differential and transactional log backup that will occurs every 24 hours and 1 hour respectitively?

You can perform using same above steps but change backup type and schedule accordingly for each maintenance plan .


Comments are closed