April 20, 2010 at 5:40 pm
I got into a discussion with a co-worker regarding backups prior to a release for an application.
I will go over my interpretation of my understanding in SQL server...
For SQL server, when a full backup is initiated you get a checkpoint that is ran in that database to flush dirty pages to disc and the oldest transaction is marked in the T-log...
The full backup starts and continues across the database pages backing them up, the transactions continue to log in the t-log
Any changes that occur to pages after they are backed up are captured in the T-log to maintain database integrity.
Once the full backup is complete, then the active portion of the T-log (up to the oldest active transaction) is also appended / backed up to the backup file to maintain database consistency.
SO...a discussion started about if what they were planning was valid...and I didn't agree from a recovery standpoint
If you start the full backup right before the release (and the above holds true i.e. I didn't misrepresent the backup steps)
And then start the release...all the changes that the release perform would technically be included in that backup thus invalidating the purpose of the backup (in regards to a restore point).
The thought on their part was the backup acted similar to what an Oracle backup could be like (I have no experience with Oracle so I am going on what they described), where you apparently can run a backup (which does a almost static like backup of the database files and also captures all the changes in something called an archive log?) So you could truly capture the database in a backup without all the transactions that occur during the backup operation.
I read Paul Randal's article which I based my beliefs on and feel they are valid...I am just looking for some confirmation from the community...
http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
I had said, to accomplish what he wanted he would either need to have everyone out, then run a full backup to completion and THEN start the release
or
since it was a large db, run a full backup a couple of hours before hand, then get everyone out and run a final t-log backup THEN start the release.
please confirm my thoughts...and thanks in advance,
Lee
April 20, 2010 at 6:29 pm
You are correct.
April 20, 2010 at 7:43 pm
Thanks much for the post...I plan on coming up with a short demonstration (script) for the individual to validate the process of the backup and any changes that happen during the backup and hopefully clear up any misconceptions.
Lee
April 20, 2010 at 9:45 pm
Yep, second vote for you being correct. Let the backup complete *before* you start the release.
April 21, 2010 at 6:01 am
Third vote. You're on the money the other guys need to go back & hit the books.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply