Backup Exec 2010 SQL Agent and MSDE databases

[RobertPBham]

Hi folks,

This is following on from my previous discussion regarding Backup Exec not backing up SQL .mdf files when doing a full system backup. I have now started experimenting with the trial version of the SQL Agent. So here is my setup:

I have a file server that hosts MSDE databases for WSUS and our antivirus package. I created full system backup of the system and used the SQL agent to backup the MSDE databases. This worked successfully.

(To test disaster recovery)
I then built a new server and restored the backup to the server - the system booted perfectly, however, the MSDE databases hadn't restored (as expected). I then tried to restore the backup of the MSDE SQL databases but they will not restore. Backup Exec states that it cannot connect to the resource  - this is because the MSDE services for both databases won't start as the database files don't exist. After numerous bits of testing I have come to the conclusion that even if I was to invest in the SQL agent it would be no use for disaster recovery as you cannot restore the databases if the MSDE SQL services aren't running.

I'm sure that the SQL agent works great for full versions of SQL Server where the service would start without the databases being availabe (I'm assuming so?), but the Backup Exec SQL Agent is of no use for disaster recovery on MSDE SQL databases - is that correct?

As it stands, the only solution I have is to disable Active File Exclusion and rely on VSS to backup the MSDE databases - I've tested this and it works great even though Backup Exec warns against doing it this way. The files are restored with the full system restore and everything starts first time.

Have I missed anything as if not, I believe Symantec should place this information in the manual as it is fairly critical to backup regimes.

Thanks
Rob

[Colin_Weaver · Accepted solution]

Database restore always have to be a 2 pass restore (applies to SQL and Exchnage and other database types)
- one pass to restore the binaries
- one pass to restore the database data

This is expected behaviour - even though it may not have been your expectation.

With regards the starting ot the SQL servcies etc - I suspect your backup and restore process is slightly wrong - did you use IDR or just a manual DR process

If Manual DR did you read the following:
http://entsupport.symantec.com/docs/300136


Note if you want something that takes a crash consistent image of the volumes on the machine including the installed databases for a fast DR process then you should probably be considering something liek Backup Exec System Recovery (BESR) instead of Backup Exec

[RobertPBham]

Hi Colin,

I have always been used to a 2 pass restore (I've carried out numerous Exchange restores using this method).

Thank you for your help - the link you have inlcuded has resolved the issue and allows me to start the SQL services so that I can do a SQL agent database restore! As you've probably guessed, I was doing a manual restore and was struggling with getting the SQL services to start. Now that I've found that I need to rename the idr files, this has helped and everything works.

I already have BESR but was testing our disaster recovery process in the case of complete disaster and the only thing I had was LTO tape to start from.

This is the last piece of the jig saw puzzle and I really appreciate your help - I think that I'll be investing in the SQL agents.

Thanks
Rob