SQL Server Performance

what gets backed up?

Discussion in 'General DBA Questions' started by shahab03, Mar 15, 2006.

  1. shahab03 Member

    hello everyone... wondering if someone can help answer this question...

    when you perform a complete backup for sql server db what gets backedup? what happens to transactions running at the time of the backup? at what point does sql server determine which committed changes to backup or not?

    thanks
  2. skeane New Member

    The backup will include enough of the log to restore a consistent database. Essentially, any transactions that are complete at the time the backup finishes are included.
  3. Luis Martin Moderator

    Just for curiosity: Why do you want to backup in working times?.
    BTW: I don't know the answer to your question.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  4. shahab03 Member

    actually in oracle there is also consistent and when you take a consistent backup oracle goes back to check which image of the record to backup. i am just trying to figure the mechanism of how sql server identifies which records qualify for a consistent backup.
  5. FrankKalis Moderator

    You can of course backup an SQL Server database which is online. The impact on transactional throughput isn't that high and, again of course, that db is always in a consistent state after being backed up. Here's a very intersting link to a case study:
    http://activeanswers.compaq.com/ActiveAnswers/Render/1,1027,540-6-100-225-1,00.htm

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  6. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by LuisMartin</i><br /><br />Just for curiosity: Why do you want to backup in working times?.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />One reason that immediately comes to mind is, that the db is that large, that a backup can't be finished in a maintenance window. Or that there isn't such a window at all. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  7. shahab03 Member

    hello Frank

    Thanks for responding to my post. I see that you are also an MVP. I was wondering how big of a career booster is it for you?

    Thanks

    Shahab


    quote:Originally posted by FrankKalis

    You can of course backup an SQL Server database which is online. The impact on transactional throughput isn't that high and, again of course, that db is always in a consistent state after being backed up. Here's a very intersting link to a case study:
    http://activeanswers.compaq.com/ActiveAnswers/Render/1,1027,540-6-100-225-1,00.htm

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  8. FrankKalis Moderator

    quote:
    I was wondering how big of a career booster is it for you?
    When you look at my profile here, you'll see that I'm not in IT business at all. So, I can't tell you anything about a career booster for sure. But I've been told by other members in the German community that I have a high market value and probably a lot of that comes from the MVP award.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  9. skeane New Member

    quote:Originally posted by shahab03

    actually in oracle there is also consistent and when you take a consistent backup oracle goes back to check which image of the record to backup. i am just trying to figure the mechanism of how sql server identifies which records qualify for a consistent backup.
    Oracle and SQL Server handle this differently. I'm a few years weak on Oracle, so it's specifics escape me (something to do with REDO logs and rollback segments or something?). In SQL, a checkpoint will write dirty pages to disk in the data files. Some of those pages will include changes that have been commited, and others changes that have not. All changes (before and after images) are written to the log before the dirty pages are flushed. When a database starts up, whether at system startup or after a restore (unless NORECOVERY is specified), it will undergo a recovery. Recovery will roll forward the changes in the log that have been committed, and roll back the ones that have not. The log provides the redo/undo data to bring the database to a consistent state.
  10. shahab03 Member

    by the way are there specific website one needs to be active at become an MVP? how does microsoft recognize someone as MVP?


    quote:Originally posted by FrankKalis


    quote:
    I was wondering how big of a career booster is it for you?
    When you look at my profile here, you'll see that I'm not in IT business at all. So, I can't tell you anything about a career booster for sure. But I've been told by other members in the German community that I have a high market value and probably a lot of that comes from the MVP award.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  11. Tahsin New Member

  12. FrankKalis Moderator

    No, there are not specific website one needs to be present on. When MS becomes aware of a person, either by their support personell monitoring the communities or other members referring to a specific individual, it doesn't matter which community that is.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  13. Twan New Member

    Hi ya,

    just to be pedantic, but the backup takes the transactions that are complete as at the start of the backup or as at the completion of the backup

    I thought it was as at the start... I thought that the backup agent would mark the current place in the log file and then start to backup the data pages. From then until the end of the backup SQL will not allow the data pages in memory to be written to without first writing them into the backup. Once it has backed up all of the data pages, it backs up the log up until the mark it put in the log when it started, and then it finishes...

    Cheers
    Twan
  14. thomas New Member

    It's my understanding that the backup is made to be consistent at the point at which it completes, i.e. all committed transactions up to that point are applied to the backup. And that a full backup doesn't back up or affect the log at all, though it uses the log to ensure the backup's consistent.
  15. thomas New Member

    Actually.. how wrong can I be? Inside SQL Server 2000 states

    "A full backup can be made while your SQL Server is in use. This is considered a "fuzzy" backup—that is, it is not an exact image of the state of the database at any particular point in time. The backup threads just copy extents, and if other processes need to make changes to those extents while the backup is in progress, they can do so."

    So ... er... it's neither.
  16. Twan New Member

    Hi Thomas,

    taken literally I don't think that the statement inside Inside SQL Server can be correct, since if it was it would mean that you couldn't restore the database back to a consistent state...? after all if processes are happily writing to pages without the backup agent/threads being aware of it, then you would get partial changes through, which surely can't be right?

    Cheers
    Twan
  17. Twan New Member

    Ok found this via Google, in answer to a question about backups. It seems that the backup process in 6.5 was as I described, but it changed with version 7.0 as you mentioned. The key is that the logs will have a record of all of the changes since the LSN noted by the backup when the backup began.

    >>>>>>>>>>>>>
    Same as 7.0. Data pages are backuped as they are, and the transaction log records generated during
    the backup process are also included. If the transaction isn't finished at end-time of the backup,
    the COMMIT log records isn't included in the backup and when you restore the backup, the transaction
    will be rolled back.
    >>>>>>>>>>>>>
  18. thomas New Member

    Yeah it does sound a bit dubious, in that the database could be inconsistent if it works as it says in the book. It's also very briefly described in there, I got the feeling there's more to it really than she covers.
  19. skeane New Member

    I can confirm from personal experience that on SQL 2000 a transaction started and ended while a backup is in progress is included in the backup. I sometimes take advantage of this fact; I have a nightly process that backs up a database on one server, then restores it on another server. The backup takes 90 minutes, but I have 60 minutes worth of updates to run, and no time to do both serially. I've kicked off the backup, then started the update process. Lo and behold, the restored database includes all of my updates.
  20. FrankKalis Moderator

    Guys, have you read the link I've mentioned?

    quote:
    Most importantly, the SQL Server 2000 backup does not delay normal database activity. It backs
    up the database files as fast as possible without regard to logical inconsistencies introduced by
    active transactions. It then captures the transaction log written during the time the data was being
    backed up. During the restore, the data is restored first, and then the transaction log is applied to
    make the database logically consistent. The result is that online backups performed during normal
    operations have a minimal effect on transaction throughput, while maintaining high backup
    performance.
    I believe there is also a whitepaper from MS out there, but I don't know the URL right now.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page