The June SQL Server Magazine articles are now available on the web and include my latest feature article on Advanced BACKUP and RESTORE Options.
Rather than covering how BACKUP and RESTORE work or how to perform common backup/restore operations I decided to show you some of the less commonly used options that can be very helpful. The article covers:
- Verifying backup integrity
- Tail-of-the-log backups
- Using BUFFERCOUNT and MAXTRANSFERSIZE to tune backup performance
- Piecemeal and PARTIAL restores
- Using marked transactions and STOPATMARK and STOPBEFOREMARK
- Restarting an interrupted restore operation
You can get to the article HERE (no login required).
Enjoy!
6 thoughts on “SQL Server Magazine: feature article on advanced BACKUP and RESTORE options”
Thanks for the post, Paul.
I’ve gone through the article.
What I noticed different is about the options, "buffercount", "maxtransfersize" and "pagechecksum" which are not available when we create backup maintanance plans using maintanence plan wizard.
So should we manually create our backup jobs and add these options as I don’t find a way to add these options to the maintanance plans.
If these are recommended then MS should mention these options in the the script it uses in the maintanance plans and make them editable.
thanks
Yes and no. There are a ton of options for BACKUP and RESTORE. The maintenance plan wizard is supposed to be a simple way to create BACKUP jobs without knowing much about backups – and most people recommend that DBAs with some undertanding of BACKUP do NOT use the maintenance plan wizard but instead use manual scripts to do it.
So no – MS should NOT put everything possible into the maintenance plan wizard.
Thanks
Paul, Unfortunately the no login link still requires to be logged in. Do you happen to have these on the SQLSkills site ?
Thanks
Hey – no we don’t as we don’t own the content. They must have changed the site to require a login. Sorry – nothing I can do about that.
I am doing full backup with recovery,and my problem is Transact log file is getting bigger and bigger. How I can prevent this that not compromise recovery option?Please advise.
You need to take log backups as well…