SQL Server Backup and Transactions

15 Mar 2007 9:19 PM

Amos Soma

Say I do a backup of a database and then restore it. Can someone tell me
what the state of the database will in the following scenarios?

1) A single transaction was running before the backup started and the same
transaction is running after the backup. Will the restored database reflect
this transaction in any way? I assume not but am not sure.

2) A single transaction was running before the backup started but ended
before the backup was done. Will the restored database reflect this
transaction in any way?

3) A single transaction was started after the backup started but still
running after the backup ended. Will the restored database reflect this
transaction in any way?

4) A single transaction was started after the backup started but ended
before the backup was done. Will the restored database reflect this
transaction in any way?

Thanks very much.

15 Mar 2007 10:32 PM

Hate_orphaned_users

I think you are making things to complicated.
But i understand youre questions.
Just remember that all transactions are stored in youre transaction log file
(if database is in Full mode).
So allways have a full back , with transaction log backups(e.g every hour).
And you will be fine :)
And some comment on youre question.
A transaction is either commited or not So if the transaction is not
executed , the backup will not include this transaction.

I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL :)

Show quoteHide quote

"Amos Soma" wrote:

> Say I do a backup of a database and then restore it. Can someone tell me
> what the state of the database will in the following scenarios?
>
> 1) A single transaction was running before the backup started and the same
> transaction is running after the backup. Will the restored database reflect
> this transaction in any way? I assume not but am not sure.
>
> 2) A single transaction was running before the backup started but ended
> before the backup was done. Will the restored database reflect this
> transaction in any way?
>
> 3) A single transaction was started after the backup started but still
> running after the backup ended. Will the restored database reflect this
> transaction in any way?
>
> 4) A single transaction was started after the backup started but ended
> before the backup was done. Will the restored database reflect this
> transaction in any way?
>
> Thanks very much.
>
>
>

16 Mar 2007 1:40 AM

Greg D. Moore (Strider)

"Amos Soma" <amos_j_s***@yahoo.com> wrote in message
news:es0Ore0ZHHA.3268@TK2MSFTNGP04.phx.gbl...

I assume you're talking about a FULL backup and no transaction backups.

> Say I do a backup of a database and then restore it. Can someone tell me
> what the state of the database will in the following scenarios?
>
> 1) A single transaction was running before the backup started and the same
> transaction is running after the backup. Will the restored database
> reflect this transaction in any way? I assume not but am not sure.

If you do a RESTORE WITH RECOVERY (the default) the transaction will be
rolled back to before it started.

This ensures the DB is consistent.

>
> 2) A single transaction was running before the backup started but ended
> before the backup was done. Will the restored database reflect this
> transaction in any way?

As I recall "it depends". As I recall, the full backup marks pages in the
midst of a transaction and when it's done the initial past attempts to do
one more pass to see if those transactions are complete and if so backs them
up.

So, depends on where the backup is when the transaction completes.

>
> 3) A single transaction was started after the backup started but still
> running after the backup ended. Will the restored database reflect this
> transaction in any way?
>

The RESTORE WITH RECOVERY will again give you a database where the
transaction has not been started.

> 4) A single transaction was started after the backup started but ended
> before the backup was done. Will the restored database reflect this
> transaction in any way?

In this case, I think it's the same as 3, but 2 may apply.

The key thing to keep in mind is that you will always get a CONSISTENT state
of the database.

Now if you do a restore WITH NORECOVERY in prep for a transaction log
restore, then things "change".

>
> Thanks very much.
>

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

16 Mar 2007 6:47 AM

Tibor Karaszi

> As I recall "it depends". As I recall, the full backup marks pages in the midst of a transaction
> and when it's done the initial past attempts to do one more pass to see if those transactions are
> complete and if so backs them up.

Not exactly, but end result is the same. The backup process start by picking up the pages (extents)
without and regard of modifications being done. Which is why we call it fuzzy backup - The pages in
themselves doesn't give us a snapshot of the database. But after all pages has been picked up, SQL
Server will include the log records that were generated while the backup was running. So, a restored
database will look like the database when the backup process ended, and if restore is performed
using RECOVERT, then all transactions that weren't committed at that point will be rolled back.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quoteHide quote

"Greg D. Moore (Strider)" <mooregr_deletet***@greenms.com> wrote in message
news:Oe65hw2ZHHA.208@TK2MSFTNGP05.phx.gbl...
> "Amos Soma" <amos_j_s***@yahoo.com> wrote in message news:es0Ore0ZHHA.3268@TK2MSFTNGP04.phx.gbl...
>
> I assume you're talking about a FULL backup and no transaction backups.
>
>> Say I do a backup of a database and then restore it. Can someone tell me what the state of the
>> database will in the following scenarios?
>>
>> 1) A single transaction was running before the backup started and the same transaction is running
>> after the backup. Will the restored database reflect this transaction in any way? I assume not
>> but am not sure.
>
> If you do a RESTORE WITH RECOVERY (the default) the transaction will be rolled back to before it
> started.
>
> This ensures the DB is consistent.
>
>>
>> 2) A single transaction was running before the backup started but ended before the backup was
>> done. Will the restored database reflect this transaction in any way?
>
> As I recall "it depends". As I recall, the full backup marks pages in the midst of a transaction
> and when it's done the initial past attempts to do one more pass to see if those transactions are
> complete and if so backs them up.
>
> So, depends on where the backup is when the transaction completes.
>
>
>>
>> 3) A single transaction was started after the backup started but still running after the backup
>> ended. Will the restored database reflect this transaction in any way?
>>
>
> The RESTORE WITH RECOVERY will again give you a database where the transaction has not been
> started.
>
>
>> 4) A single transaction was started after the backup started but ended before the backup was
>> done. Will the restored database reflect this transaction in any way?
>
> In this case, I think it's the same as 3, but 2 may apply.
>
> The key thing to keep in mind is that you will always get a CONSISTENT state of the database.
>
> Now if you do a restore WITH NORECOVERY in prep for a transaction log restore, then things
> "change".
>
>
>>
>> Thanks very much.
>>
>
>
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>
>