what gets backed up?

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

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.

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.

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)

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.

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)

This has been discussed before:

Refer to the following topics:

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10893
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8573

- Tahsin

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

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.

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.

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

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.
>>>>>>>>>>>>>

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.

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.