Post reply

Is data being written to the Db also written to a backup while it's being performed

  • July 9, 2008 at 2:25 am

    #839926

    Colin Smith (7/9/2008)

    I've always been under the impression that when a full backup is carried out, it is a snapshot of the database at the point in time that the backup is first started

    Colin,

    you're impression is wrong. When the backups starts it checks the last LSN number in the transaction log. Once all datapages are written to the backup file all commited transactions which happened after the inital LSN will be applied to the backup file. So the backup is the database at the end of the backup process.

    [font="Verdana"]Markus Bohse[/font]

  • July 9, 2008 at 2:44 am

    #839932

    Thanks Markus, that makes sense with what I'm seeing and now I'm hoping you or someone else can help me with a follow on question. For parallel testing purposes, I'd like to take a backup of production that is just prior to a batch process starting, so is it possible create a backup that is a "snapshot" at the point in time of the backup process begins or do I just estimate when the backup process will finish and adjust the backup schedule accordingly.

  • July 9, 2008 at 3:05 am

    #839937

    If you're using Enterprise edition of SQL2005 you could take a database snapshot.

    [font="Verdana"]Markus Bohse[/font]

  • July 9, 2008 at 6:55 pm

    #840585

    Thanks Markus,

    Unfortunately not using Enterprise, but thanks anyway.

    Cheers, Col

  • July 9, 2008 at 10:29 pm

    #840610

    I would suggest that you take a full backup sometime well before your batch process commences and transaction log backup regular after that. If you need to restore to a point in time, you can easily do this by using the "STOPAT" option of the restore command.

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply