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

[bodminman]

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

[vikpaw]

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.

Edited January 23, 2011 by vikpaw
addition on precedence arrows.

[rh91uk]

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

[vikpaw]

In case you missed it, i've added a paragraph on adding arrows to initiate the notify task.

[vikpaw]

@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!

[edutech4schools]

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.

[GREED]

Vik has described the answer using maintenance plans exactly, that is how I have always backed up Capita databases, has never failed on me.

[vikpaw]

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/websupport/RenderXSL.aspx?KB=KB29316.xmlFalse

 

KB29316

[GREED]

dbattach stopped zipping ages ago I think vik, due to issues on the server & security