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 |
|
kensai
Posting Yak Master
172 Posts |
 Posted - 2010-09-23 : 05:02:38
|
| 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? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-23 : 05:49:05
|
| We have database accessed 24/7 and we do the same as you are proposing - FULL weekly, DIFF daily, TLOG every 15 minutes. It doesn't impact performance of the application, but we do the FULL and DIFF at the quietest time during the day (but I don't think it will matter if you do it at a busy time, just better to do it at a less busy time if possible)Restore the full backups to another machine and use DBCC CHECKDB to ensure that the backups are "recoverable" (and this will also mean that you do not have to do DBCC CHECKDB on the Live server)Make the full backups after you do Index Rebuild and Update Statistics - otherwise the differential backups will be big.(We do index rebuild daily, but only of fragmented indexes, which requires a bespoke maintenance routine, so only really viable if you want to go down that route)Copy the backup files to A.N.Other machine as soon as they are created - in case of machine failure. Then deal with copy-to-tape as a separate, lower priority / background task. |
 |
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2010-09-24 : 06:27:35
|
quote:
Originally posted by Kristen
We have database accessed 24/7 and we do the same as you are proposing - FULL weekly, DIFF daily, TLOG every 15 minutes. It doesn't impact performance of the application, but we do the FULL and DIFF at the quietest time during the day (but I don't think it will matter if you do it at a busy time, just better to do it at a less busy time if possible)Restore the full backups to another machine and use DBCC CHECKDB to ensure that the backups are "recoverable" (and this will also mean that you do not have to do DBCC CHECKDB on the Live server)Make the full backups after you do Index Rebuild and Update Statistics - otherwise the differential backups will be big.(We do index rebuild daily, but only of fragmented indexes, which requires a bespoke maintenance routine, so only really viable if you want to go down that route)Copy the backup files to A.N.Other machine as soon as they are created - in case of machine failure. Then deal with copy-to-tape as a separate, lower priority / background task.
Thank you for the info.Can you elaborate "use DBCC CHECKDB to ensure that the backups are "recoverable"" part? I know how to use dbcc but I don't know what you mean with this. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-24 : 06:43:03
|
| You need to restore a backup, and run DBCC CHECKDB, to be sure that the backup can be restored, and that the restored database is not corrupted.Otherwise if you restore from backup and the database is then corrupted you have a serious, possibly non-recoverable, situation. |
|
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2010-09-24 : 08:57:35
|
quote:
Originally posted by Kristen
You need to restore a backup, and run DBCC CHECKDB, to be sure that the backup can be restored, and that the restored database is not corrupted.Otherwise if you restore from backup and the database is then corrupted you have a serious, possibly non-recoverable, situation.
Got it, thanks. |
|
|
|
|
|
|
|
|
SQL Server live database backup strategies