SQL backup and restore...

[Rob_Pelletier]

I am a bit worried that the protection offered by Veritas/Symantec BackupExec may not be completely idiot-proof. When it comes to SQL Server, I AM an idiot. I really know very little about SQL Server, but since it is on the network with all of our other servers, it has fallen upon me to be the protector of the databases. The database vendor has assured me that the databases have their backup options set properly, (some have Full, others Simple Recovery) but I figure they'll all be off on a golf vacation on the day that I need to restore something.

Is the SQL database backup and restore as simple as the BackupExec documentation would have you believe, or are there a multitude of settings throughout the backup configuration that I need to choose carefully in order to properly protect our data?

Can anyone point me to a document (or a previous thread) that could take some of the mystery out of all this?

Has anyone had any luck restoring SQL databases backed up with the BE SQL Agent, and do you have any bits of knowledge I should have now (before disaster strikes)?

Thanks.

[Deepali_Badave]

Hello,

Please refer the Backup exec admin guide for more information:

http://ftp.support.veritas.com/pub/support/products/Backup_Exec_for_WindowsNT/279401.pdf

Regards,

[David_Sanz]

Rob

As far as I know, the SQL Server agent is the one that gives the fewest problems. Quite easy to set up, just take into account that you need yo put the SQL databases with the Full Recovery Model if you are to make differential or Registry backups (if you need only complete backups, this is not mandatory).

Also you can redirect databases to a different server when restoring.

The admin guide is the best document you could review. You have it in your media server, in \BEAdminxx.pdf.

Regards

[Rob_Pelletier]

Thanks for your replies...

I guess my main concern is that the database vendor has decided to place some of his databases into Simple Recovery mode, and others in Full Recovery mode.

I don't know the potential ramifications of changing them all into Full Mode, nor do I understand how the backups made using the Enterprise Manager Maintenance Plans and the backups made daily by the Backupexec SQL Agent would each come into play in the event that I need to do a restore.

It is pretty scary. knowing so little about how MS SQL server works, yet being responsible for protecting its data!

I ordered book on SQL 2000 Backup and Restore - hopefully I'll learn enough to make sure my client has the protection he needs...

[David_Sanz]

Rob

SQL Server is a transactional database, and it uses a transaction log to keep the changes made to the database prior to commiting them. Full mode means that the log keeps on growing until someone (Backup Exec when performing a full backup) purges it. Simple mode doesn't let Backup Exec make incremental or differential backups (whisch are based on the transaction log) because SQL Server purges that log to save space.

That's the difference.

Regards

[Rob_Pelletier]

So, the backups that are done in the Enterprise Manage Maintenance - do they affect the transaction logs as well?

I wonder why anyone would even bother using Simple Mode then - why keep transaction logs after a backup has rendered their content irrelevant?

[Jeff_Zankofsky]

Rob,

If you are going to set your databases to the Full recovery model, do not run any backups using the Enterprise Manager. This will mess up the backup sequence.. You will also need to be running log backups to truncate the trans log if using the Full recovery model. This will keep the trans logs from growing too large.

[Rob_Pelletier]

Thanks.

I hope the book I have on order explains all of this, or I'll never get it done right.

Now I'm faced with the task of arguing with the SQL specialists about Full vs. Simple and Veritas vs Enterprise Mgr.

Gee, nothing's easy...

[Jeff_Zankofsky]

Here's a link to the SQL best practices doc: http://support.veritas.com/docs/285757

Applies to 10d even though it says 11d.

[shweta_rege]

Hello,



Could you please Update us on the issue?




Thank You,


Shweta

[Rob_Pelletier]

An update? Hmmmm...

My posting was an attempt to find a quick and easy way to make sure I was protecting my client's SQL databases without having to learn everything there is to know about Full and Simple Recovery Modes, Enterprise Manager Maintenance Plans, and transaction logs. Yep, I wanted a guarantee without doing any of the homework - guess I was being a bit naive...

First, I suppose I'll need to read the BackupExec Admin Guide's handling of the topic. Then I'll need to try to learn how the SQL Server Enterprise Manager's Maintenance Plans and BackupExec's SQL Agent's backups affect each other. Since SQL server keeps these transaction logs, and since those logs are affected somewhat by the different types of backups being done, I need to find out how to combine the completeness of daily BE Agent backups with the every-other-hour differential backups currently being done by the Enterprise Manager. This may assist me in balancing the completeness of the full backups with the up-to-the-minute protection of the differentials, and maybe allow me to restore a more up-to-date data set should I need to restore something.

Part of the problem is that the database vendor did his thing with the Enterprise Manager, without knowing what I was doing with the BackupExec - there was no collaboration. His knowledge of the database world would be more helpful in putting this plan all together, I suppose.

I'll take a look at the Admin Guide's handling of the topic, and also take into account the suggestions and reference made by Jeff Zankovsky regarding SQL backup best practises. While doing a daily Full Recovery Mode backup of these databases is an easy way of protecting the data, it would be nice to also provide differentials during the day to provide a more current restore should it be required.

Sorry, this isn't much of an update - it's just where my head's at with this stuff right now.

Thanks to everyone for their input so far - it's been quite helpful.

[Keith_Langmead]

I think there is some confusion here...

With Full and Simply recovery mode the difference is the amount of data being recorded. In Full mode SQL will record every single transaction which is made, while Simple only records a few minimal pieces of information. The upshot is that with a simple mode database you can only do a full backup, while a full mode database you can do a full and an incremental / differential backup, since the inc / diff backup just backs up the transaction log, so you can role through the transactions until you reach the point in time you want to restore to.

So the big question you need to consider is do you need to do inc / diff backups as well as full backups. Generally the deciding factor here is that of storage space to hold the backup files.

The main difference between Backup Exec and Enterprise Manager backups in my opinion is the speed of recovery and the type of recovery. While Backup Exec will restore the complete system, you generally backup your data to tape which takes time to restore information once you've found the correct tape etc, Enterprise Manager on the other hand generally has the advantage that you're backing up the data to the hard drive, which allows you to restore very quickly. The downside is that you have to restore each database individually, which negates that speed.

I personally use both, so I have Backup Exec's nightly backups going to tape, and ready for a complete system restore, and Enterprise Managers backups going to disk, which I can then use to restore individual databases if the need requires.

If you make sure the EM backup runs after the BEWS one has finished that will ensure that you don't have any issues with any transaction log backups you run from EM during the day.

[Rob_Pelletier]

Well that certainly clarifies things somewhat.

I have about 20 databases, all of them related in different ways. Some are part of the main accounting application, others in companion apps. Some of them, as I mentioned, have been configured to be in Simple Recovery mode, while others have been set up in Full Recovery. I am not sure if all this was carefully considered, or if the installers of the one database have a method they always use, and the others just install their app, and don't even cosider backup - leave that for someone else.

My goal here is to provide some kind of balance: I want full protection , easy to understand, set up and administer. I also would like to be able to provide more current backups, say every couple of hours or so during the business day, so that, in the event of a corruption issue or some other problem, I could restore a data set and minimize the loss of information.

So, this could be something I could do by combining a BEWS full backup daily of all SQL databases, and then do differentials every couple of hours using EM.

I will try to learn more about how the transaction logs are affected by both backup types to see how to make them play well together. Obviously, keeping a week's worth of differentials is useless unless I keep a co-ordinated set of full backups to pair them with.

The rest of this client's data (Word and Excel file, etc.) is fine with a daily backup, but the mission-critical nature of the databases is making me want to understand this process fully, so I can make the right decisions.

Thanks for your input.