Automating backup for MS SQL 2008 R2 EE database

Hi EE,

I'm new to automating SQL 2008 R2 backup. Is there a script or a way for me to achieve this.

 I would like to achieve the following.

1. Automate Database backup to a local drive on the host machine nightly.
2. Automate Database backup to a shared drive on a file server on the domain.
3. I want to be notified by email of the backup. May be tell me that it is successful.
4. Automate removal of database that are x days old for the purpose of cleanup.

My Servers are windows 2008 R2 and run MS SQL 2008 R2 SP1.

I have standalone host machines as well as a database hosted on a two nodes Cluster.

Any help much appreciated.
Who is Participating?
jogosConnect With a Mentor Commented:
A backup-drive will get fragmented so when using a local drive see it's not your C-drive, nor the the drive where other (database)files are located that needs good performance.

What you can do?
a) maintenance plans
there's an in-build feature to do configure that
a link to a little more advanced inside what you can change the task

b) automate it yourself
ex like script on link put in a sqlAgent job
Notice that in the example master and msdb-db are not backuped and they also should but maybe on other shedule
DaeraAuthor Commented:
Thanks for the info
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.