What happens in native SQL backups?
Q: What actually goes on behind the scenes for the different types of native SQL Backups? Ie If for example my backup drive and t-log drive are physically separate to the data drive will there be any adverse effect to data access times for the database data files.
Jason Hall says: In a perfect world you would want all three files on separate drives. For example you would have your data file on drive D:, log file on drive E:, and backup files on drive F:,
Even in this scenario, data access times might be slowed slightly to your data files during the backup as the backup will be causing read IO’s to the data files in addition to the IO’s that are occuring during normal database operation. In this separated scenario you are provided the least amount of IO contention during the backup time and both your database performance and backup performance will be optimized.
A backup is very straightforward, it simply reads blocks of data from your data/log files and writes those blocks out to whichever backup device you have specified.
Technorati Tags:
SQL Backups, T-Logs, database contention, backup performance