Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sektor
Starting Member
14 Posts |
 Posted - 2006-04-10 : 16:48:39
|
| Hello everyone!I'm pretty new to the world of SQL and im quickly becoming more and more involved with our companies SQL server (2000 to be exact).I have recently been put in charge of designing a backup solution for our SQL server and was hoping to get ask some questions and get feedback here. Let me give a rundown of what i have:-We just purchased Veritas 10D software for backup (We are backing up quite a few servers in addition to SQL).-We have a 5 tape Autoloader connected to another Windows 2003 Server running the software.-I have deployed the Veritas agent to the SQL server and set everything up.My only question is designing a good strategy. Since i'm pretty new to SQL, im still learning everything and im not entirely sure what would be best to do. For example, in the Veritas software, I have the option of backing up the databases or the transaction logs or both. What is the best way to go about doing this? What gives me the maximum amount of protection while at the same time, giving me the ability to do a full restore?I really appreciate feedback and recommendations.sektor |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-10 : 16:53:18
|
| The best strategy is not to use Veritas to backup databases. Use Veritas to sweep the files to tape only. We perform daily full backups and transaction log backups every 15 minutes. Both are to disk using either the native backup or SQL LiteSpeed. We copy these files to tape using Veritas. We also use log shipping for site duplication at our disaster recovery site. You could implement differential backups as well in this strategy. We don't currently have a need for it though.Tara Kizeraka tduggan |
 |
|
|
sektor
Starting Member
14 Posts |
Posted - 2006-04-10 : 17:00:54
|
quote:
Originally posted by tkizer
The best strategy is not to use Veritas to backup databases. Use Veritas to sweep the files to tape only. We perform daily full backups and transaction log backups every 15 minutes. Both are to disk using either the native backup or SQL LiteSpeed. We copy these files to tape using Veritas. We also use log shipping for site duplication at our disaster recovery site. You could implement differential backups as well in this strategy. We don't currently have a need for it though.Tara Kizeraka tduggan
Interesting strategy. Any particular reason why to avoid using Veritas to backup databases?Currently, we will be performing our backups at night, when the last person leaves. In the future, we might do backups everyone hour, but that won't happen until we get bigger. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-10 : 17:11:13
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51150Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-11 : 01:09:34
|
| "In the future, we might do backups everyone hour, but that won't happen until we get bigger"I wouldn't wait! Backing up Transaction Logs every hours (or better still every 10~15 minutes) means that if you accidentally do "DELETE * FROM MyImportantTable" you will be able to restore to within a few minutes of the accident.If you change your backup strategy "when you are bigger" you will have to retest, prove and debug it all over again.I would recommend that you start with a more comprehensive strategy than you need right now (but obviously not one sufficient to run a mega-corp!)I would also second what Tara said: Backup from SQL Server to disk files first, using SQL's own tools, and then let the tape backup get those files onto the tape. If you need to recover from the "most recent" backup - which is the most likely scenario - the data is right there on the disk, you don't have to wait for it to restore from tape first, having found the right tape / waited for the previous person's tape operation to finish etc.Explicitly exclude the folder that the database files are in from the Tape backup's routine - so it doesn't try to backup the "open files"Kristen |
|
|
|
|
|
|
|
|
General backup strategy question