+ Post New Thread
Results 1 to 9 of 9
How do you do....it? Thread, SQL Server 2008 - Automating DB Backup using the SQL Mgmt Console in Technical; Hi there, I know some of you will have done this, if so can you please share how you went ...
  1. #1
    bodminman's Avatar
    Join Date
    Apr 2007
    Location
    Sunny Suffolk
    Posts
    1,163
    Thank Post
    734
    Thanked 224 Times in 116 Posts
    Rep Power
    90

    SQL Server 2008 - Automating DB Backup using the SQL Mgmt Console

    Hi there,

    I know some of you will have done this, if so can you please share how you went about it.

    I currently use BackupExec 10d and would like to schedule a backup of SIMS and FMS to a particular location and have BackupExec backing up that location.

    Any help / guidance is as usual much appreciated!

    Thanks

  2. #2

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,785
    Thank Post
    715
    Thanked 1,441 Times in 1,194 Posts
    Rep Power
    360
    I think the advice is to script dbattach to backup the db because it also does something funky with the logins to allow you to move the file to a new machine, however it can also be done in management studio assuming you are restoring to the same box.

    matt40k also has a tool that is used to keep tidy a backups folder, which you can search for in the mis forum. you might be best with this request in the mis forum actually.

    I'll tell you what i've got set up anyway:

    In management studio connect to your server, and open up the management tab. Under maintenance plans create a new plan. i've combined my SIMS and FMS together but you could do them separately.

    Drag a 'Backup Database Task' from the left to the middle.
    Right click to edit and choose the databases you want from the dropdown. I choose master, sims, fms.
    Choose the folder to direct the backups to, anywhere your backup exec can access. I also tick the create subdirectory for each database to keep them separate, and also verify.

    That's pretty much it. Hit save, it's easy to forget to do that.
    You can right click on the plan name on the menu on the left and execute at will.
    However, at the top of the central part of the screen you can add a schedule to the plan, so it occurs nightly at a specified time.

    Recently after some kind of unexpected reboot, due to severe storms and power failure, i found the plan was not executing, so i changed the schedule and resaved and it kicked started things, so worth bearing in mind.

    However as an added precaution, i've added a notify task from the tools on the left to email me on success / failure of the task. This is one of the reasons i should split the backup task into two, because sims is more likely to fail due to insufficient disk space (i tidy manually at the moment).

    I zip the files up and tidy every week, though i should look into the automation of that soon, but am holding off until i move to a clean windows 2008 VM.

    Anyway, the notify operator task is really useful, but requires setting up an operator and link to exchange server / email system. I'll have to look up where i got the instructions for that, but basically setup up an operator, and also double click on database mail, under maintenance plans and run through the wizard.

    EDIT: If the backup task block has an arrow drag it to the notify task. If not right click and choose add precedent constraint and specify To notify task. Right clicking on the arrow going from backup to notify lets you choose Success. Do the same again to make another one for failure.

    You may just get backup exec to backup the location and use logs in there to check on whether the task is taking place okay.

    Hope that is what you were looking for, and not unnecessarily simplified.
    Last edited by vikpaw; 23rd January 2011 at 11:49 AM. Reason: addition on precedence arrows.

  3. 2 Thanks to vikpaw:

    bodminman (23rd January 2011), rh91uk (23rd January 2011)

  4. #3
    rh91uk's Avatar
    Join Date
    Sep 2008
    Location
    UK
    Posts
    877
    Thank Post
    137
    Thanked 132 Times in 114 Posts
    Rep Power
    36
    I backup using an SQL maintenance plan and then script it and copy it to a seperate NAS Box. However, I should look at scripting detaching the database as that gives us better flexibility as such.

    However I have restored BAKS with logins before with no problem using the enclosed script which adds the "Restore Backup" to your dbAttatch program from SIMS

    @echo off
    "X:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\dbattach.EXE" /RESTOREUI /auto /user=SA /password=SAPASSWORD /server=%computername%\ESCC

  5. Thanks to rh91uk from:

    bodminman (23rd January 2011)

  6. #4

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,785
    Thank Post
    715
    Thanked 1,441 Times in 1,194 Posts
    Rep Power
    360
    In case you missed it, i've added a paragraph on adding arrows to initiate the notify task.

  7. Thanks to vikpaw from:

    bodminman (23rd January 2011)

  8. #5

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,785
    Thank Post
    715
    Thanked 1,441 Times in 1,194 Posts
    Rep Power
    360
    @rh91uk - the restore should be fine if it's to the same box with the same master table, assuming, that passwords in the live system haven't changed between the backup / restore process. The problem would occur if you moved to a new box.

    We used to get told, that it's fine to run a system manager backup even with users on the system. This used to be the only way we could truncate the logs, and shrink the ldf file, but that doesn't seem to be an issue anymore.

    I did have an application my old support company gave me that would automate that task, i don't know if it was just using dbattach or not, but i probably wouldn't trust it now as dbattach may well have had a few revisions since 2005!

  9. Thanks to vikpaw from:

    bodminman (23rd January 2011)

  10. #6

    Join Date
    Apr 2010
    Posts
    2,104
    Thank Post
    95
    Thanked 189 Times in 156 Posts
    Rep Power
    84
    SQL has a built in backup tool. Create a new maintenance plan in SQL manager to backup the databases to a folder of your choice. you can get it to delete backed up databases after X days.

  11. Thanks to edutech4schools from:

    bodminman (23rd January 2011)

  12. #7

    GREED's Avatar
    Join Date
    Mar 2008
    Location
    Portsmouth
    Posts
    3,116
    Thank Post
    381
    Thanked 389 Times in 314 Posts
    Blog Entries
    8
    Rep Power
    180
    Vik has described the answer using maintenance plans exactly, that is how I have always backed up Capita databases, has never failed on me.

  13. Thanks to GREED from:

    bodminman (23rd January 2011)

  14. #8

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,785
    Thank Post
    715
    Thanked 1,441 Times in 1,194 Posts
    Rep Power
    360
    This KB from supportnet explains how to automate using the dbattach program, though i don't think it does the zipping anymore.

    http://support.capitaes.co.uk/websup...29316.xmlFalse

    KB29316

  15. Thanks to vikpaw from:

    bodminman (24th January 2011)

  16. #9

    GREED's Avatar
    Join Date
    Mar 2008
    Location
    Portsmouth
    Posts
    3,116
    Thank Post
    381
    Thanked 389 Times in 314 Posts
    Blog Entries
    8
    Rep Power
    180
    dbattach stopped zipping ages ago I think vik, due to issues on the server & security

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 21
    Last Post: 18th January 2011, 12:20 PM
  2. Moodle / Server 2008 / IIS7 / MS SQL Server 2008 Express
    By Kamran7860 in forum Virtual Learning Platforms
    Replies: 3
    Last Post: 23rd November 2010, 06:03 AM
  3. New SIMS Server - Windows 2008 R2 & SQL 2008 R2
    By MYK-IT in forum MIS Systems
    Replies: 23
    Last Post: 17th June 2010, 09:31 AM
  4. Windows 2008, SQL 2008 and Backup Exec 12
    By joe90bass in forum Windows
    Replies: 2
    Last Post: 5th May 2010, 02:49 PM
  5. Backup Exec 10d and MS SQL 2008
    By Duke in forum Windows
    Replies: 4
    Last Post: 12th April 2010, 10:21 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •