• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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.
0
Daera
Asked:
Daera
1 Solution
 
jogosCommented:
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

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now