Automating backup for MS SQL 2008 R2 EE database

Posted on 2012-08-24
Last Modified: 2012-08-28
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.
Question by:Daera
    LVL 25

    Accepted Solution

    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

    Author Closing Comment

    Thanks for the info

    Featured Post

    Too many email signature changes to deal with?

    Are you constantly being asked to update your organization's email signatures? Do they take up too much of your time? Wouldn't you love to be able to manage all signatures from one central location, easily design them and deploy them quickly to users. Well, you can!

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Know what services you can and cannot, should and should not combine on your server.
    To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
    This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now