BE2010R3 - What is the proper way to backup a MS SQL database/server?

[Nuri_Inuki]

Running Backup Exec 2010 R3.  I'm looking into backing up the MS SQL databases on our Windows servers and was wondering what is the proper way to back them up if I want to be able to: A. Restore just a single database; B. Restore the entire server should the server fail?

Any suggestions?

I'm looking at a job designed by the previous owner and it's only tagging to backup the databases but not the filegroups...not sure why one would do that? Any insight?

Thanks in advance.

[SuperBrain · Accepted solution]

You will need a Agent for SQL license for this.

Once you have the license installed, you will be able to see and select MS SQL databases in your backup job.

It is recommended you create a seperate job for SQL databases without AOFO checked.

Once you have a successful backup of SQL databases done, you will be able to do both A and B.

'B' of course will require a full backup of C: and System State (since this is a disaster recovery scenario) along with SQL backup.

So in your scenario, if you want to backup the Server which also has MS SQL installed:

1. Make sure you have Agent for SQL license installed along with the main Backup Exec license.
Note: If you do not have this license, you will need to purchase one.

2. Make sure you have a Remote Agent for Windwos Systems (RAWS) installed on the SQL server

3. Create two jobs,

- one for your Volumes (C:, D:, etc) and System State

- second for your SQL databases

[Russ_Perry]

Some additional info for the 'B' part of your question...

In doing a disaster recovery of a SQL server it's important to know that the process can be streamlined quite a bit with the SQL system database copies that Backup Exec creates during SQL backup.  See http://www.symantec.com/docs/HOWTO24337 for more info.  Basically if you're backing up the C drive, System State, and all SQL databases and the server crashes, a disaster recovery first involves reinstalling Windows to the same version as original, with same name and in a workgroup.  The restore of system state and drives follows next.  Once this is complete rename the SQL system database copies put in place by the volume restore and start up SQL server.  At this time a database restore of the rest of your SQL databases can be performed.  Other than the initial installation of Windows, no further reinstalls are required...

Note that filegroups and their backup/restore can be tricky and normally are not part of a backup scheme.  In fact they aren't available for selection by default in Backup Exec 2010 and aren't supported at all in Backup Exec 2012..  Database backups will sufficiently protect all of your SQL data.

Russ

[Nuri_Inuki]

If I do not have AOFO, can the file system and SQL databases be backed up on one job or should they still be separated out?

What would be the advantage of separating it out?

[SuperBrain]

If you do not enable AOFO thn file system and SQL can be backed up on one job.

The only reason it is recommended to have seperate FS and DB jobs is to:
1. Avoid failures caused by AOFO + DB backups. (may occur if AOFO is enabled on a database job)
2. Avoid exception messages caused by live db/log/etc files. (may occur if AOFO is not enabled on file system job)

[Nuri_Inuki]

That explains why I get those exceptions on certain job. We don't own the AOFO piece, so...separate jobs would only be beneficial to minimize exception messages.  Hm...might not be worth the administration overhead.

[Nuri_Inuki]

Russ,

Will checking the top level "Microsoft SQL Server" automatically include both the database and the file groups?  If so, then that means I have to individually select all the databases, but then will that not include new databases that are created after the backup job?  How would I go about selecting all databases on that server, including future new ones?

[B77]

My question's pretty similar to this.  I don't want to backup all of my SQL dbs. So as well as deselecting them within the agent do I also need to browse to the file location and deselect the mdf and ldf?