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.
DaeraAsked:
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
http://msdn.microsoft.com/en-us/library/ms189647.aspx
a link to a little more advanced inside what you can change the task
http://www.mssqltips.com/sqlservertip/1754/sql-server-2005-transaction-log-backup-maintenance-plan-via-ssis/

b) automate it yourself
ex like script on link put in a sqlAgent job http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/
Notice that in the example master and msdb-db are not backuped and they also should but maybe on other shedule
0
 
DaeraAuthor Commented:
Thanks for the info
0
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.