Flashbackup SQL 2000\2005 Database backup safe to do?

[NHSAl]

Hi

 We purchased a number of 6.5 Enterprise client licences.  I setup a flashbackup policy to backup one of our SQL servers.  The full backup backed up the entire servers drive successfully.  Because this SQL server is not in a production environment I deleted a few live sql database files (stopped the SQL service and deleted the .mdf files then restarted SQL).  Obviously when going into SQL enterprise manager it displayed the database as suspect.  I then opened netbackup backup & restore on the client, selected the policy type for restore (flashbackup windows) selected the deleted mdf files for restore and began restoring the files.  Once the files were restored and the SQL service was restarted the database all seemed back to normal.

 My question is.

 In a live environment is this a safe and reliable way to do a full backup of an SQL database?

 If not, why?

 I was thinking of using the database management pack to do daily SQL backups, a normal Windows policy doing the server incremental backups, and then use a flashbackup policy to do a weekly full backup. 

Hope this makes sense 

Cheers 

[sdo]

I think you got lucky, perhaps even very lucky.

I definitely would only use the Database Pack for doing MS SQL backups of an open database on production systems, and exclude the mdf and ldf files from other backups.

[Dion]

If you try to back up SQL using a normal file-level or flash-backpup you will definitely hit problems later.  If the database gets written to during the backup, this will invalidate the backup and you will always have to do some kind of recovery on that db.  Doing a recovery is a sure sign that you have lost data and you want SQL to recover whatever it can.  If SQL has information in memory that hasn't been written out to your databases at the time of backup, it will not quiesce this data, leaving your backup in an incosistent state

You should be using the SQL database agent to do your backups.  The agent communicates with SQL at the time of a backup and SQL will manage the flushing of buffers and consistency of the backup.  It also allows you to use SQL machanisms to restore the database (NetBackup talks to SQL).

[Rakesh_Khandelw]

Indeed you got lucky with restoring you DB fom Flash backup to bring it online. May be your database wasn't doing anything at all, I mean no transaction, at the time of backup and that is why your backup was a good one.

This is not really a recommended way to backup any DB. To have a good backup of live database, it should be in quiet mode aka hot backup mode means when you are performing backup there shouldn't be any activity going on within DB. Performing this kind of backup at file system level means you really need to either shutdown the DB or let it run in hot backup mode until you are done completing the backup, which is not really possible in most of the cases. To address these issues, NetBackup has DB agents, which interact directly with database and put only portion of database in hot backup mode while it performs that portion and then move to different portion or so called tablespaces.

You can use  these database agents in conjuction with Snapshot client to complete to have quick and offhost backups.

[NHSAl]

Dion

Ok thanks, that makes sense. It was probably successful due to the database only being a test DB having minimal if any activity.

Thanks

Message Edited by NHSAl on 08-15-2008 06:34 AM

[NHSAl]

Thanks Rakesh 

I have been reading up on the SQL agent.  I have run a few tests by backing up and restoring databases all of which have been successful.   My two main queries due to my lack of understanding are:

What impact does using the agent to back up an SQL database have on user operation during the backup phase?

I understand the advantages of using the snapshot method when backing up a normal file server.  What I’m confused with is how using the snapshot method in conjunction with the SQL agent when backing up an SQL database works?  Is this a better and more efficient method?  From what I understand the agent still interacts directly with SQL and performs the backup.  When does the snapshot method actually come into play when using this method?

[NHSAl]

Could really use some help on my last questions as I have to implement it this week.

Sorry :( 

[Rakesh_Khandelw]

---

@NHSAl wrote:

What impact does using the agent to back up an SQL database have on user operation during the backup phase?

---

Impact on user operation is not noticable unlessyou have OLTP kind of transactions going on and DB is extermely busy. They way agent works, it put small portion of DB in backup mode while NetBackup performs the backup for that piece. Now, if any transaction need to write to this particular piece which is currently being backup and in backup mode, it goes to logs (for oracle it is known as redo logs, not very certain what it is known as in SQL). The particular piece of DB is always available for read purposes. Once the backup for particular piece (tablespaces) finishes it comes back online and next piece goes into backup mode.

So, the only time a user may see performance imapct, when he/she is trying to write to the very same piece which is currently in backup mode but again this impact is very minimum and in most of the cases unnoticable, unless its something very large in size and your redo logs can't hold it.

---

@NHSAl wrote:

I understand the advantages of using the snapshot method when backing up a normal file server. What I’m confused with is how using the snapshot method in conjunction with the SQL agent when backing up an SQL database works? Is this a better and more efficient method? From what I understand the agent still interacts directly with SQL and performs the backup. When does the snapshot method actually come into play when using this method?

---

Snapshot method doesn't mean taking the snapshot at FS level or filer level only, it also includes various off host option, like EMC TimeFinder, Hitachi Shadow image etc. You need to have advance client license to make it work with NetBackup. The way it works third mirror/BCV syncs up in background. Teh SQL agent puts SQL in backup mode, and then break the mirror, disks get mounted on a media server and NetBackup performs a Filesystem level from media server. All this means, minimum impact on production DB host, data going over fiber (from media server to tape) instead of over the LAN and easy recovery. I suggest you read snapshot admin guide to get further details.

Message Edited by Rakesh Khandelwal on 08-19-2008 09:05 AM