Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Full backup Internals Expand / Collapse
Author
Message
Posted Tuesday, July 10, 2012 2:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 17, 2013 4:35 AM
Points: 110, Visits: 627
Hi folks,


I need some clarification related on FULL Backup.

Scenario - Some one exec SP which contains 100 insert/updated statements 10:45 Am.

Now i want take backup of that DB 10.46 AM but if i have taken the backup 100 inserted/updated modifications will be available in backup file ..?

If Available how many transaction will be there in backup file.
If not how can we force the backup command after the completion of SP(consider may be some huge transactions)

General points (Don't consider Checkpoint/Lazy writer)

After exec Full backup command what happens internally..?
Post #1327333
Posted Tuesday, July 10, 2012 2:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
The backup started after the commands started, so as long as they finish before the backup finishes, they will be in the restored database


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1327342
Posted Tuesday, July 10, 2012 3:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:27 PM
Points: 1,242, Visits: 1,097
GilaMonster (7/10/2012)
The backup started after the commands started, so as long as they finish before the backup finishes, they will be in the restored database


Basically the message we see in the error log after the DB is restored on a difference machine are those commands who have started after the backup started. Commands finished before the backups are committed and commands don't finish are rolled backup.

Please correct me if my understanding is correct.



Post #1327357
Posted Tuesday, July 10, 2012 3:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
Not entirely.

What messages?

The backup copies the data pages as it encounters them. It also copies the portion of the tran log it needs to make the restore consistent.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1327366
Posted Tuesday, July 10, 2012 3:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 17, 2013 4:35 AM
Points: 110, Visits: 627
but if we consider
DB - 200GB
for backup it will take 25 min..
suppose 10:45 i started backup of 200GB database
Small Transactions occurring on particular database between 10:45 to 11:05 all are committed transactions .
Backup will cmp at 11:10.
Now we restore the backup file can i get data up to 11:05 (or) 10:45

Post #1327372
Posted Tuesday, July 10, 2012 4:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
You will get a database that is transactionally consistent as of the point that the backup completed.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1327390
Posted Tuesday, July 10, 2012 5:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 17, 2013 4:35 AM
Points: 110, Visits: 627
Means if backup takes two hours and transactions are committed up to last min
like
12 clock backup started and end at 2 clock i will get data up to last min which are committed.
between 1:50 to 1:59 (committed Transactions)


If the above statement is True
Thanks Gail...



Post #1327466
Posted Monday, December 30, 2013 11:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, April 13, 2014 1:50 PM
Points: 62, Visits: 214


10:45 you started your sp for inserting and if your insert completed at 10.46 and then you started, Full backup - Backup file having all Transaction.

10:45 you started insert and its taken 10.50 for Insert your backup started at 10.46 and its completed at 10.49 - Yourbackup file having all transaction

10:45 you started insert and its taken 10.50 ,backup started at 10.46 and its completed at 10.50 - Your backup file dont have last one min of data

Note : Its not depand at what time you startd insert - Its depand on backup duration. how much time you backup will run ,at that duration how many transation has happend its contain all.

One more very Imp thing what you inserted (commited or uncommited ) - You are doing same above process but what you have inserted is uncommited. Then also your backup file having all transaction but whan you will do restore, SQL server will do recovery and all uncommited data will be rollforward
and you will lose all uncommited data.


You can msg me - rajeshjaiswalraj@gmail.com
Post #1526571
Posted Tuesday, December 31, 2013 2:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 29, 2014 5:19 AM
Points: 11, Visits: 1,383
Hi Gail,

"The backup started after the commands started, so as long as they finish before the backup finishes, they will be in the restored database" sort-of suggests that the start-time of the transaction vs start-time of the backup dictates whether the transaction will be included.

My understanding was that, independent of when a transaction starts, it will be included in the restored database if it completes before the backup completes i.e. transaction COMMIT is determinant (and transaction BEGIN is irrelevant).

Example reference
"Use a Full Database Backup to Restore the Database
You can re-create a whole database in one step by restoring the database from a full database backup to any location. Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished. The restored database matches the state of the original database when the database backup finished, minus any uncommitted transactions." (emphasis added)


Chris
Post #1526696
Posted Friday, January 03, 2014 2:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:16 AM
Points: 50, Visits: 409
Good read to clarify your doubt
http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
Post #1527412
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse