December 22, 2006 at 9:35 am
I've got a full backup running on a production server in simple recovery mode.
It's a pretty big database, and I need to finally take it down for a complete reindex, which will take about 8 hours. I'd like to back it up beforehand, but that will take even more time.
My thought is to start a backup before my maintenance window and then shutdown access before the backup completes, but I want to make sure all activity has been backed up.
So, my question is:
If I run a backup while changes are occurring, when it completes, exactly what point in time is the backup valid for? Is it to the point it completes the log backup (which is pretty much the end of the backup) or is it sometime earlier? Can I even do this? That is, can I start a backup with activity occurring, shut down outside access at some later point during the backup, and be assured that all activity is included in the backup?
Thanks,
Marc
December 22, 2006 at 9:47 am
Marc,
My understanding is the backup is valid when you start it. The reason is that as pages are grabbed through the backup process, they are marked as a part of that backup. If you go to change a page that hasn't been backed up, your update is blocked while that page is added to the backup. Then it is marked as complete and the update proceeds.
I don't see a good reference in BOL, but that was how I heard it explained at TechEd 2005 by one of the team.
December 22, 2006 at 10:01 am
Best practices for this type of activity.
1. Switch the database recovery mode to FULL. Do a Full backup. When it's done, stop all jobs (stop the SQLServerAgent service(s)). Put db in single user mode. Backup the transaction log. Now you have the latest data. To restore, restore the full backup using WITH NORECOVERY, then restore the log using WITH RECOVERY. Lastly, switch the database back to SIMPLE.
2. This one will definitely make sure you have everything. Stop SQL Server. Backup/copy the .mdf/.ldf (and any .ndf) files. You can do this since the database is offline. (Or detach the database and backup the files).
-SQLBill
December 22, 2006 at 10:26 am
During a full database or differential backup, Microsoft SQL Server backs up enough of the transaction log to produce a consistent database for when the database is restored.
All changes will be backedup until your backup completed...
If you start your backup at 1:00 am and it takes two hours to complete at 3:00 am all changes to the db until 3:00 am will be in your backup...
You can test this in your dev or prod servers by creating a dummy table to see it is included in the backup or not...
MohammedU
Microsoft SQL Server MVP
December 22, 2006 at 11:15 am
Hmmm...
Not exactly the rousing agreement I was hoping for. Anyone else?
Thanks,
Marc
December 22, 2006 at 3:33 pm
I've been googling for some backup info, and came across this statement on Devx: Choosing the Right Backup in SQL Server
In the backup file, SQL Server stores the names and locations of the files actually used in the database. Upon restoring the database, SQL Server recreates all the necessary files, as many as there might be. A database thus restored is equivalent to the point in time that the backup finished.
HTH
December 26, 2006 at 8:07 am
It's the point at which the backup is finished.
Tom
December 26, 2006 at 9:38 am
I stand corrected. Finish time looks to be the correct time.
December 26, 2006 at 2:44 pm
Tom Goltl is correct. I did the test. The backup included all of activities during its process.
January 2, 2007 at 8:09 am
Thanks, everyone. I appreciate the time you all took to look into it and get back to me.
Marc
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply