question

Mandar Alawani avatar image
Mandar Alawani 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!!!
sql-server-2008sql-server-2005backupbackup-restore
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

any particular reason for wanting to do this?
2 Likes 2 ·
Fatherjack avatar image
Fatherjack answered
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?
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Almost everyone. I haven't. I really don't want to think about it...
1 Like 1 ·
Nope. My thoughts were "WTF" and "aargh!", with hints of "run away!"
1 Like 1 ·
I bet you thought it though :)
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I was just wondering about that - I'm a LiteSpeed user, and have no recollection of that feature being there...
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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:
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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!
2 Likes 2 ·
I knew there was something else about TLogs... thanks for the ref, @Kev
0 Likes 0 ·
Tim avatar image
Tim answered
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?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.