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?
4 Answers
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.
-
You mean there's no locking of tables while the dump is made? for full backups I do think it's necessary to lock the tables. For any large table, the effect on a web application would be that "nothing happens"..– 3moloSep 23, 2010 at 9:31
-
No locking. SQL Server is an enterprise database. It bbrings the db file into a consisstent state and suspends all write operations (hence higher memory load) during a backup, then makes the backup in the background. Locks are only held in memory. Naturally only if you use the backup API.– TomTomSep 23, 2010 at 9:56
-
Correct, backups don't block, as long as you are using actual SQL backups (or a 3rd-party SQL backup product like Quest Litespeed) and not trying something like backing up the MDF and LDF files from the file system manually.– BradCSep 23, 2010 at 13:19
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.
answered Sep 23, 2010 at 12:29
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.
-
Not necessarily. Seriously. I run a database - 800gb once filled totally - that is regularly backed up. My restore scenario is one week off. Not SUCH a big problem. But loosing the data woult put me into hell ;)– TomTomSep 23, 2010 at 14:01
-
Oh I see your point TomTom, but your business is taking this into account presumably. If the CEO said "In the event of a disaster, this db must be online again within 24 hours" you'd change your backup plan to meet the requirements, right?– Rob MoirSep 23, 2010 at 15:58
-
1Yes. Stil lwould take me a week - but I would have a replication system online and use mirroring ;) Failover in seconds, even if hardware burns. Backups should never be used (i.e. they are the last line of defense).– TomTomApr 12, 2013 at 11:39
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.
answered Sep 23, 2010 at 13:09
