How to setup a complete disaster recovery backup with Backup Exec on a server that has SQL server installed?

[andre_st]

We recently bought Backup Exec 12.5, which also includes the software "Symantec Backup Exec
Continuous Protection Server". The data is beeing stored on tapes.

Now frankly, I am a bit confused as how to go about this whole thing. We are running a Windows Server 2008, which is also running our critical databases in SQL 2008. I have heard that backup programs are often not capable of restoring a SQL server if a crisis would accure.

At the moment I have only installed Backup Exec 12.5 (and the standard version does not appear to include the "SQL Backup agent"), and in the destination for the backup job I am able to pick all the databases for backup. But in the Backup Exec manual the following can be read: "The Backup Exec Agent for Microsoft SQL Server option must be installed on the media server in order to perform a complete SQL Server database recovery." So what kind of backup am I really running if I wont be able to restore the SQL Server database?

At the moment I am letting the SQL Server performing a daily maintenance plan of backing up the databases to a ".bak file". This file then is then included in the regular daily backup routine to a tapestation.

Further more I have made a "Intelligent Disaster Recovery" boot-cd with the Backup Exec, which enables me to boot the server in case of a complete crash. Then from within that application I can run the last backup-tape to restore the operating system and all other files in my selection list. But then again, will this setup be able to restore the SQL Server as well as the databases?

Further more I am not sure if I should be combining Backup Exec with "Symantec Continuous Protections Server"? I dare not to install and test to much, as the server maintains very critical systems, so I need som advice on best practice here. Have I understood it correctly that I can use the "continuous Protection" to create snapshots of the databases and SQL, and then making the Backup Exec saving this data on tape for later restore if needed? Would that setup enable me to perform a "complete" restore from scratch if needed?

(The flashy things in the Continuous Protection systems, with snapthos and restore functions for the users through a web user interface...is nothing we really need.)

Or is it better for me to buy the option of Backup Exec SQL agent for this purpose?

[Colin_Weaver]

What you should probably consider is using the SQL Agent to backup the SQL databases - in addition to doing the file system and system state backups etc to protect the operating system of the server itself (that the IDR process would need.)

Then in the event of a disasters it is a two phase restore - use the IDR disk to put back the operating system and the SQL Program files. Then use a SQL restore job to restore your SQL data.

BTW it is a 2 stage proces because to the SQL Agent backup maintains the database consistency, but needs the SQL servcies to be running in order to do the restore of the database afterwards

[Hemant_Jain]

CPS is not really a disaster recovery solution, if you compare it with Backup Exec. It provides you file level protection. So, it would backup your SQL database files, as real files and would not include interaction with SQL API's for that backup, and hence you would not be able to do differential or log backup of SQL databases. For disaster recovery of SQL, best way to go would be Backup Exec SQL Agent, which would give you the ability to backup and restore SQL databases using SQL API's. SQL Agent does not do file level backup, but interacts with SQL API's for backup and hence it is the recommended solution for SQL Backups. So, if restoring individual files, is something you are not looking at, you may avoid using CPS as a solution. You can use Backup Exec to protect your server and SQL databases using SQL Agent. For remote machines, you would require remote agent software to be installed on it. Also, regarding IDR, it is a useful solution for faster recovery, you may also look at option of using BESR as a disaster recovery solution.

Please mark it a solution, if this answers your questions.
Thanks

[andre_st]

Thanks for your reply Colin!

Hmm, I think I know now why this has been confusing me alot. I realized that the SQL Backup Agent was indeed installed along with the Backup Exec 12.5. But it is only an evaluation for 60 days. It doesnt even seem to allow me to uninstall this evaluation. So therefore, I dont really know what options I will have in the long run.

Are the options in the submitted picture not possible with only Backup Exec 12.5 (without the sql-agent), will those choices go away after the evaluation period?



But if the Backup Exec 12.5 is able to restore both the operating system and the SQL program files, wont it be enough to import the .BAK files of the databases in step two? Or will that somehow make the restore operation more risky or difficult, compared to using the SQL Backup agent?

What about "Symantec Continuous Protection Server" which we got along with Backup Exec. Can it be used instead to perform a complete disaster recovery in some way?

[andre_st]

Hemant Jain managed to answer in between, so many of the follow up questions from my last post, have now been answered.

"So, if restoring individual files, is something you are not looking at, you may avoid using CPS as a solution." - we also need backups of individual files, but we feel that it is not necessary to have a continuous backup, since the files do not change that much between days. Therefore it should be enough to just do a backup once a day.

I am still curious though if Backup Exec 12.5, with the Intelligent Disaster Recovery, can be used, in conjunction with the SQL possibilities of making a backup-file of the database, to perform a complete restore of both operating system and SQL databases?

[Hemant_Jain · Accepted solution]

This type of backup selection would be available only when using Backup Exec SQL Agent. Although, you can restore using the bak files which have backed up, but that will be a two stage restore. Specially, it appears from the snapshot that Backup Exec database is also hosted on the same SQL server. So, during recovery you might be in a fix, as in order to recover the database, you would need BE and in order to get BE up.. you will need the SQL database. So, not a good practive to have both your production databases and Backup Exec database in same SQL instance. That said, both ways can help you recover, but without SQL agent, your restores will be time consuming. SQL Agent offers you faster and consistent method of SQL database backup and restore.

Thanks

[andre_st]

Thanks for your help Hemant!

Your answer convinced me that we should preferrably buy the Backup Exec SQL agent to fulfill our needs.

Regards,

André