Database backup questions

[br0ck]

I manage an SQL server (MSSQL2000SP4) that has 1 critical database(8Gb)and 2-3 not so critical databases. We are working on plotting out a backup and maintenance procedures. And I’m looking for some insight and constructive criticism

We are using Veritas BackupExec 10 with an AIT4 Drive on another server with the SQL agent on the SQL Server(I do not have a tape drive on the SQL Server). The plan is to run a full backup to disk every hour during business hours 8am-6pm to the Backup Server (that overwrites every 24hrs) and running a full backup with indexes at night to tape. So far it is backing up the 8gb Database in 10 min. to disk.

Ok, that’s the background now to the questions:

To manage logfile pileup would it be suggested that we have the databases in simple recovery mode? We are running an hourly backup to disk.

I have herd mixed reviews of the Veritas Backup Exec SQL Agent. What are your thoughts on this? (it has been running good for me do far)

Thanks for the help

 

[Catadmin]

I don't think Veritas backups up SQL Server. Unless there's a new development that I'm unaware of, Veritas just backups the Server's Hard Drive which isn't quite the same thing.

You should have a good SQL Server backup in place that backs up your critical DB at least once a day in FULL recovery mode so you can take advantage of having Transaction Log backups during the day (I recommend every 1 or 2 hours) to restore to a point in time. Making a full backup every hour is space/IO intensive and could cause problems, so I don't recommend that.

On your not-so-critical dbs, you could get away with having the recovery mode as BULK-Logged or SIMPLE. BULK-Logged will log everything but BCP/Bulk Insert commands run against the dbs. SIMPLE will essentially not use the Transaction Log except while it's processing a transacation. The point being, in SIMPLE, you can't restore to a point in time just to the time of the last full backup, which, IMHO, is NEVER good in a critical db.

Of course, if Veritas does have a SQL Server specific backup tool, just apply the same logic to the backup plans you design through its interface. Just make sure the Veritas you're using isn't the "Windows backup" version because backup via that method will not help you recover your SQL Server.

Hope this helps. Let me know if I made any wrong assumptions about your environment so I can correct myself. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

Catadmin is correct. You should do a full backup once a day, and transaction log backups through out the day.

The trick to figuring out how often to run the transaction log backup is to figure out how much data the company can afford to loose. If you can afford to loose 1 hours worth of data in the event of a database crash, then backup every hour. If you can only afford to loose 5 minutes worth of data, then you need to backup the log every few minutes otherwise you can't meet your SLA.

If you are backing up the database more than every hour or so, you will want to look into differential backups every few hours to speed up recovery time.

This analysis should be done on every database on the system. A system being a crytical system doesn't say how often it should be backed up. We have very critical systems where I'm at now, however they are OLAP databases and only get loaded once per day, so once the load is finished we do a full (or diff depending on the day) are we are done. We have other critical systems which are OLTP systems which we do a T/LOG backup every 30 minutes. Once our tape system upgrade is finished I'll be changing this to include the diffs for faster recovery time.

What ever solution you pick, you need to make sure that you can actually restore. I can tell you horor stories of people who thought they were backing up there data, until they went to restore and the tape was blank.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Denny is absolutely correct on the making sure you can restore part. I've seen corrupted SQL backup devices that make it impossible to get back the data at all. You'll want to periodically test your restore protocols on a test server just to make sure things work the way they should.

Also, this gives you a great excuse to put into writing (and practice) a great disaster recovery plan which you could present to your boss and get kudos for. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"