SQL Server live database backup strategies
I'm trying to come up with a backup strategy for SQL Server 2005. I am thinking of doing full backup once a week, differential backup once a day and transaction log backup every 15 minutes. The database is around 50gb in size. The thing is that the database is live every second of the day. Will the full backup disturb any operations? Do I need to do anything in particular to make backups go smooth without pausing any database operations?
Backups work perfectly. No interruption of the database. What you will see is higher IO load, obviously, as the backup io performance comes up on top of the disc IO.
In addition, memory requirements are higher than without backups - obviously.
Along with what has already been stated, my suggestion is that you should determine what the business requirement is regarding the tolerance for data loss and down time. The fact that you're "thinking" about how to backup the database tells me that you're guessing at what is the appropriate method and timing for backing it up instead of knowing what's required in order to meet your business objective regarding data loss and down time.
I can only echo joeqwerty. For a serious line of business app where availability and downtime really matter, you have to start with "What is the business requirements around availability and data integrity" and work forwards from that.
Don't just think about backup, think about restore.
How long would it take to restore the DB to last known good state using your proposed scheme? Do you know? Is that length of time acceptable to the business?
What can the business afford in this area and does that allow you to deliver what they require?
I know it might seem counter-intuitive to think about restore before you've worked out how to back things up, but in the event that something bad does happen then this approach will hurt much less in the long run.
There wont be a 'pause' of database operations with a sql server backup, but that doesn't mean performance will not be impacted. Best practice for this IMHO is to setup a seperate disk just for backup that's seperate to your data and log disk.