April 4, 2008 at 6:37 am
Hi,
I have a question about backups that are taken whilst updates or inserts are running in SQL 2005. If there is a job that runs from 1am-2am that updates all tables in a database sequentially, a backup job that runs from 1.30am-3am, then a job that inserts rows into each table from 2am-4am, which data will be backed up and which won't? Does SQL Server figure out at the start of a backup which data it will backup and then any changes after that are ignored? Or will SQL Server capture any changes that happen during the backup? I've looked on MSDN but can't find a detailed explanation of how the backup process works. I found a page that says insert and update statements are allowed but it doesn't mention whether DDL statements like alter table are allowed, though it does say that alter database is not allowed. It also doesn't say how SQL Server ensures that these data modifications are captured.
Thanks.
April 4, 2008 at 6:49 am
mr_omid (4/4/2008)
Hi,I have a question about backups that are taken whilst updates or inserts are running in SQL 2005. If there is a job that runs from 1am-2am that updates all tables in a database sequentially, a backup job that runs from 1.30am-3am, then a job that inserts rows into each table from 2am-4am, which data will be backed up and which won't? Does SQL Server figure out at the start of a backup which data it will backup and then any changes after that are ignored? Or will SQL Server capture any changes that happen during the backup? I've looked on MSDN but can't find a detailed explanation of how the backup process works. I found a page that says insert and update statements are allowed but it doesn't mention whether DDL statements like alter table are allowed, though it does say that alter database is not allowed. It also doesn't say how SQL Server ensures that these data modifications are captured.
Thanks.
SQL Server will capture the changed. A full backup stores the database pages, and then it also stores the log entries for operations that have happened during the backup.
Regards,
Andras
April 4, 2008 at 6:53 am
At the beginning of a backup the last Log Sequence Number (LSN) is recorded. Once all the data from the datafiles is written to the backup file, SQL Server uses the transaction log to write all committed transactions after the recorded LSN to the backup file. So in your example a backup which ends at 3:00 will contain all the changes which have been committed by 3:00.
It makes no difference if the transactions contain DML or DDL. As long as they have been committed by the end of the backup job.
[font="Verdana"]Markus Bohse[/font]
April 4, 2008 at 7:31 am
Thanks for the quick replies. Does anyone know of a good reference site where I can do some further reading on this?
April 4, 2008 at 8:16 am
mr_omid (4/4/2008)
Thanks for the quick replies. Does anyone know of a good reference site where I can do some further reading on this?
Books Online (the documentation with SQL Server 2005 or 2008) contains all the above. There is maybe even too much information is such thing exists. Good starting point: http://msdn2.microsoft.com/en-us/library/ms187048.aspx
Regards,
Andras
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply