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 |
|
vladimir.stokic
Starting Member
25 Posts |
 Posted - 2007-03-08 : 04:39:39
|
| Hi thereThis is the scenario:I have a heavy duty database, that is being accessed very, very frequently (i.e. 100 times in a minute).Now, I would like to make a backup of the database, just in case something goes wrong (recovery reasons, etc.).My question is how will making a backup impact the performance of the database and how will I be sure that the backup is in the consistent state?Thank you |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-08 : 10:02:58
|
| "My question is how will making a backup impact the performance of the database "Well, it obviously adds to the server's load, but SQL Server is designed to use "online backup"But it would have been better if you had taken the backup into consideration in your design and testing!How much data can you afford to lose?So perhaps backing up once a day, during a quiet period, is sufficient?Or maybe combine that with a more frequent additional backup of changes only, or transactions."how will I be sure that the backup is in the consistent state"If its a single database then SQL Server backups are consistent. For a mission critical database you should transfer every backup to another machine, restore into SQL Server on that second machine, and use DBCC CHECKDB to prove that the backup file is restorable. For less mission critical databases it is probably sufficient to do this once a week or so.Kristen |
 |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2007-03-08 : 10:40:12
|
| Backup consistency on full backups in SQL 2005 are ensured by using the transaction log to track changes made to the database during the backup. That way even long running backups are consistent. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-08 : 12:30:09
|
| Isn't that in SQL 2000 too? I seem to remember that in SQL 2000 backups contained all the data as-at-the-end of the backup. Whereas prior to that the backup was as-at-the-start-of-the-backup.To do the at-the-start thingie the backup read-locked the whole database and watched for write-requests, and then backed up those pages out-of-sequence (and released the lock). I thought that was a cool idea, but obviously if there are lots of writes to the database they get blocked ...... so I supposed in SQL 2000 they just backup whatever they find in the database, and THEN backup anything in the TLOG that is after the start of the backup.Maybe there is something even-more-slinky in SQL 2005 ?!Kristen |
|
|
|
vladimir.stokic
Starting Member
25 Posts |
Posted - 2007-03-09 : 03:33:15
|
Thanks for the help. I am trying to design a whole system to be fault tolerant, which means I must have a backup somewhere; and backups of heavy duty, 24/7 databases are hard to make without making a serious impact on performance.Anyway, thank you all once again. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-09 : 04:34:02
|
| "backups of heavy duty, 24/7 databases are hard to make without making a serious impact on performance"That's not been my experience. What problems are you seeing?We have several N,000 transactions a minute - I don't notice any problems with backup; but we designed our backups in conjunction with our hardware choices and proceduresKristen |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2007-03-09 : 04:44:53
|
| Kristen, the reason I wrote 2005 is that I'm not sure how it works there, it was just that I'm reading "Inside SQL Server 2005: The Storage Engine" by Kalen Delaney, and I just read about this yesterday ;) |
|
|
|
|
|
|
|
|
Database backup and performance