asked
Pause and resume database backup
Hi, Small question: can we Pause and resume database backup in SQL Server using SQL Native backup i.e. BACKUP DATABASE or SQL Server Management Studio??? I think this can be done using third party tools like Litespeed.. Thanks in advance!!!
Yes and No. No, native backups and third party tools wont let you do this. The database has to be transactionally consistent, the backup will have to succeed of fail. Pausing would potentially allow for changes to be made to the data on disk and that would break all sorts of database and data security (ie audit) rules. Yes, if the server is a virtual machine you could pause the box mid restore and then restart it again. Obviously there is a HUGE chance that the database wont recover successfully and/or there will be destroyed sectors that will catch you out when you come to read/write to them. Also if the backup file gets changed while the VM is paused then there is going to be a mess. Everyone else has asked why, so I will too. Why would you want to do this?
Almost everyone. I haven't. I really don't want to think about it...
Nope. My thoughts were "WTF" and "aargh!", with hints of "run away!"
The native backup tools do not support pause and resume, I would be surprised if any other tools offered up such a functionality though, as they normally run using APIs offered up by SQL Server itself and only add extras like compression and encryption. **EDIT** - looking on the litespeed site shows no indication of a pause and resume feature, do you have a link to this feature?
I was just wondering about that - I'm a LiteSpeed user, and have no recollection of that feature being there...
I highly doubt that that would be possible. The backup operation within SQL Server creates basically a block-by-block copy of the SQL database. If this was paused and resumed, you would be highly likely to create a backup that could not be used to restore from in any state, let alone a transactionally consistent state. Check out Jonathan Kaheyias's blog for more detail than I can provide on what SQL Server does when backing up databases:
Also Paul's blog at which demonstrates that the backup has to take enough of the tx log started AFTER the start of the backup to give a transactionally consistent state at the end of the backup. Pausing would just prolong this. Ideally you want a backup to be as quick as poss to minimize the amount of change between start and end!
I knew there was something else about TLogs... thanks for the ref, @Kev
A quick search online revealed zero results for pausing and resuming SQL Server backups so I would think that even third party tools do not offer this ability. I can say from all my reading over the years and working with SQL Server native backups that I have not ever seen the option to pause a backup. Pausing mirroring yes, but not a backup. Out of pure curiosity, why would you want to pause a backup?
