Using Backup Exec 12.5 in conjunctionwith SQL Agent - Are we now capable of a complete, Intelligent disaster recovery?

[andre_st]

We are a small company that use one Windows Server 2008, and a SQL Server 2008 for our main business tools. We initially thought that Backup Exec 12.5 would be enough for us to perform a full, Intelligent Disaster Recovery - but I was told by a Symantec Employee that we also would need the SQL-agent (as discussed in this previous post)

https://www-secure.symantec.com/connect/forums/how-setup-complete-disaster-recovery-backup-backup-ex...

So, now we have purchased the SQL-agent, and I have configured the Backup Exec to perform a daily full backup, including all SQL Databases. Further more I have configured Backup Exec to daily copy the "dr-file" to an external USB-drive, and made a Boot-cd with all the Servers necessary drivers etc.
 
As we unfortunately do not have the possibility of "testing" this setup to verify that this actually works - in terms of making a complete recovery of both Server OS, and all SQL databases, in case a disaster, would you say that this is all we need?

Lets say that all the servers harddrive would melt down one night - should I then be able to boot with the recovery cd to a "new" server, point to the "dr-file" on the USB-memory > which points out the backup tapes that should be used > run the tapes.... and then have an identical server, ready for operation?

[Colin_Weaver]

The issue with backing up any database technology is maintaining a consistent backup. Imaging software can take what is called a "crash consistent backup" which in effect means that you are in the same state as if someone had pulled the power plug whilst your server is up and being actively used by clients.

The database agents however call into APIs that backup/export the contents of the database in a consistemt manner which is written to the backup device either directly or as a secondary stage.

When it comes to restoration an image based backup can restore the complete OS and databases in one pass - but you might have the various issues that can affect restarting a crash consistent image which might mean you have to work with the database vendor to make the database consistent.

Using the agent based restore mechanism you have to get the operating system and the program files that support the database back first and then restore the database (using the APIs again) to restore the data into the database as a second phase - but is shoudl be consistent with this method.

Backup Exec uses an Agent based backup and restore  mechanism for SQL as such I would expect you to need to perform a DR restore of the Operating System and the SQL program files - followed by a specific restore of the SQL databases.

[andre_st]

Hi Colin!

Thanks for your reply!

I am not sure of what you mean by: "might mean you have to work with the database vendor to make the database consistent."

I found the following text in the Backup Exec Documentation:

"After using Intelligent Disaster Recovery to recover the Windows server, IDR automatically replaces the damaged master and model databases with copies of the master and model databases. After SQL is restarted and the latest master database backup and all other system databases are restored, you must still restore all user databases after completing the IDR Recovery."

This confirms what you mentioned in your post. In the text it says "you must still restore all user databases after completing the IDR Recovery", is this simply done by just creating a restore job as usual, and selecting the databases from the selection list. Or is it more complicated than that?

Up untill now, we have configured the SQL Server with a maintenance plan which makes backup copies (.bak) of the critical user database. Am I correct to say that since we now have the SQL Agent, we can disable this function - as the IDR in conjunction with the Backup SQL Agent would do a better job?

[Colin_Weaver]

What the SQL agent does is call the same interface that you currently use to create your .BAK files but instead of writing to a file it redirects the result to the Backup Exec tape engine to write to a tape (or to a backup to disk file)

So you can choose
1) use the SQL agent to backup and when it comes to a restore, do the IDR restore and then just do  a standard SQL Agent restore of your user databases as a new (second job)

2) continue to use your maintenance plan to create a .BAK file which you then backup when backing up the operating system with Backup Exec. when it comes to a restore you use IDR to restore the operating system and the BAK file and then use SQL command to restore from the BAK file.

As you can see with either method you have to do two phases - one to restore the operating system and one to restore the consistent copy of the user database into SQL itself.

If you have used a different backup product (such a s Backup Exec System Recovery instead of Backup Exec for Windows)  which performs an image level backup of the disk structure -  then this is the one that can generate crash consistent backups which in theory would allow you to recover immediately to the exact state at the time of the backup in one pass (so saving time)  However there is a small chance  that a crash consistent backup will not be 100% consistent which is where talking to a database specialist might be needed. - the chance is very similar to the same chance that if you turned off a live server without shutting it down that all the databases would mount correctly without some maintenance being required.

In my previous message I was trying to outline why any backup product might need a two phase restore but that there are some backup products on the market that might be able to get the complete server including databases back in one phase.

[andre_st]

Ok!

I will go for the first option then - I.E making the SQL Agent responsible for making copies of the user databases.

Thanks for your help!