SQL Backups and database availability | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Backups and database availability


What exactly does the backup do ?, and what can users/sysadmins do in a database when a backup is in progress ? Does it put table/page locks on data when it is backing up that specific table/page or does it back up the whole mdf/ndf ?. How does this change with Full, differencial, filegroup and transaction backups ? When backing up a database I have read that it is not possible to run some ALTER DATABASE commands, inserts and deletes, but i have also read that backups maximize their rate of data transfer with minimal effect on transaction throughput. As long as Ive been a DBA i have always done Backups at times when there are the least amount of users and when the server is not being utilised so much, this make perfect sense as I’m sure theres an overhead. Ive looked on the web and in BOL but cant find anything. thanks
I’m thing you are doing well. Is better to full backups when server is not working.
Same thing with differencial. But, transaction log backups is when you think is better to you organization. Could be each hour or each 15 minuts. All depends on your needs.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Here’s an interesting case study on SQL Server backup’s:
http://h71019.www7.hp.com/ActiveAnswers/cache/70699-0-0-0-121.aspx I know there are more like this (even on the MS site, IIRC), but this is the only link I’ve collected. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
I suggest you to look for books – Inside SQL SErver by Kalen Delaney and SQL Server unleashed series which explains the process involved in these processes. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>