May 29, 2009 at 5:26 pm
I remember reading about this some time ago, that a full db backup contains data as of the time of backup completion time (not backup start time).
Is this true? I would like to confirm.
Thanks for any responses!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 30, 2009 at 2:12 am
The data pages are backed up as the backup process reads them, so pages early in the file may be as they were at the beginning of the backup, pages at the end as they were at the end of the backup.
After the data is backed up, the portion of the log covered by the backup interval is backed up as well, and included in the DB backup. SQL uses that to get the DB back to a consistent state.
This should help you: http://www.sqlskills.com/blogs/paul/post/Debunking-a-couple-of-myths-around-full-database-backups.aspx
From the comments on that post:
Paul
The point-in-time at which the database is restored to is the point at which the data-portion-reading part of the backup operation completed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2009 at 4:34 am
GilaMonster (5/30/2009)
The data pages are backed up as the backup process reads them, so pages early in the file may be as they were at the beginning of the backup, pages at the end as they were at the end of the backup.After the data is backed up, the portion of the log covered by the backup interval is backed up as well, and included in the DB backup. SQL uses that to get the DB back to a consistent state.
This should help you: http://www.sqlskills.com/blogs/paul/post/Debunking-a-couple-of-myths-around-full-database-backups.aspx
From the comments on that post:
Paul
The point-in-time at which the database is restored to is the point at which the data-portion-reading part of the backup operation completed.
Thank you!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 30, 2009 at 4:52 am
We are doing a critical outage tonight, and I have a 20-minute window to backup a large number of databases before our storage admin does the tape backups.
My schedule is:
7:00 pm: all user sessions closed
7:00 - 7:20 pm: database backups
7:20 - 8:00 pm: tape backups
8:00 pm: shutdown of SQL instances
Most of the db backups normally take about 10-20 minutes to complete, but I'm worried that because I'm doing them all at the same time the SAN will be overloaded, and I will not be able to finish everything within the 20-minute alloted window (7 to 7:20 pm).
So I was thinking of starting some of the backups (the ones that normally take about 20 minutes) at around 6:50 pm to spread the load on the SAN and ensure all backups are done by 7:20 pm.
Provided those backups complete after 7:00 pm, the possibility data will be lost because of a long-running transaction will be minimal.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 30, 2009 at 7:57 am
If all of the user databases are in full recovery mode, then full backups could be made earlier (starting at 6PM?), and then at 7 PM, only transaction log backups of these databases and full backups of the system databases would be needed.
SQL = Scarcely Qualifies as a Language
May 30, 2009 at 8:29 am
You could always take the DBs offline as you finish the backups. That way no one would be able to make any transactions. It may make the downtime more, but means that there's no possibility of losing data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2009 at 3:01 am
Carl Federl (5/30/2009)
If all of the user databases are in full recovery mode, then full backups could be made earlier (starting at 6PM?), and then at 7 PM, only transaction log backups of these databases and full backups of the system databases would be needed.
Thanks, in fact this is what I ended up doing.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 31, 2009 at 3:02 am
GilaMonster (5/30/2009)
You could always take the DBs offline as you finish the backups. That way no one would be able to make any transactions. It may make the downtime more, but means that there's no possibility of losing data.
Thanks, that's a good suggestion.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply