July 10, 2012 at 2:35 am
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..?
July 10, 2012 at 2:52 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2012 at 3:18 am
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.
July 10, 2012 at 3:38 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2012 at 3:49 am
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
July 10, 2012 at 4:09 am
You will get a database that is transactionally consistent as of the point that the backup completed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2012 at 5:58 am
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...
December 30, 2013 at 11:53 am
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
:w00t:
December 31, 2013 at 2:47 am
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).
"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
January 3, 2014 at 2:59 am
Good read to clarify your doubt
http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply