patternMinor
How are SQL backups different from IT regular nightly server backups?
Viewed 0 times
backupsaresqlregularnightlydifferenthowserverfrom
Problem
Our IT department backs up the entire server every night (an SQL Server instance is installed on this server), which should be backing up that server as well as the entire network, in case something goes wrong...
So my manager asked what is the significant of my Full, Differential and Log SQL backups versus whatever the IT department backs up? To save more space on our server rather than keeping these files for a couple of weeks and deleting them, she thinks IT will just provide them!
I know it's not right, as I can restore up to the last 30 minutes with my log backups, IT restores it the next day, but is this the only difference?
Since I save/send my database backup files to the same server, IT will restore them but if I don't have these backup jobs in my maintenance plan then IT can just restore the SQL instance without any of our tables, transactions... etc.
Am I getting this right?
Any advice would be really appreciated.
So my manager asked what is the significant of my Full, Differential and Log SQL backups versus whatever the IT department backs up? To save more space on our server rather than keeping these files for a couple of weeks and deleting them, she thinks IT will just provide them!
I know it's not right, as I can restore up to the last 30 minutes with my log backups, IT restores it the next day, but is this the only difference?
Since I save/send my database backup files to the same server, IT will restore them but if I don't have these backup jobs in my maintenance plan then IT can just restore the SQL instance without any of our tables, transactions... etc.
Am I getting this right?
Any advice would be really appreciated.
Solution
Database backups give you point-in-time restore capability (provided you have
Server backups do not replace database backups, they complement them by "archiving" the database backup files long-term (i.e. more than just today).
In the end you and your management have to decide your RPO (the recovery point objective - how much you need to be able to recover in a crash). With only daily server backups and no database backups, you stand to lose a full day's work in the worst case.
Edit: @Sting has a valid point in that shadow copies (the mechanism most likely used to make server backups) are not very likely to take an exactly simultaneous copy of all your database files (including log files), which may lead to inconsistencies when you restore the backup. For instance, if the shadow copy reads the transaction log a few milliseconds before it reads the database file, the database file could contain an uncommitted transaction, but as the transaction was committed a millisecond later, the log will not have any record of it.
FULL recovery model). Even if your IT people take backups every few minutes, which is extremely unlikely, you'll still have a gap.Server backups do not replace database backups, they complement them by "archiving" the database backup files long-term (i.e. more than just today).
In the end you and your management have to decide your RPO (the recovery point objective - how much you need to be able to recover in a crash). With only daily server backups and no database backups, you stand to lose a full day's work in the worst case.
Edit: @Sting has a valid point in that shadow copies (the mechanism most likely used to make server backups) are not very likely to take an exactly simultaneous copy of all your database files (including log files), which may lead to inconsistencies when you restore the backup. For instance, if the shadow copy reads the transaction log a few milliseconds before it reads the database file, the database file could contain an uncommitted transaction, but as the transaction was committed a millisecond later, the log will not have any record of it.
Context
StackExchange Database Administrators Q#130091, answer score: 8
Revisions (0)
No revisions yet.