Pause and resume database backup


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!!!
more ▼

asked Aug 26, 2011 at 06:02 AM in Default

Mandar Alawani gravatar image

Mandar Alawani
376 33 39 44

any particular reason for wanting to do this?
Aug 26, 2011 at 06:19 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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?
more ▼

answered Aug 26, 2011 at 07:36 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

Almost everyone. I haven't. I really don't want to think about it...
Aug 26, 2011 at 08:34 AM ThomasRushton ♦
I bet you thought it though :)
Aug 26, 2011 at 08:59 AM Fatherjack ♦♦
Nope. My thoughts were "WTF" and "aargh!", with hints of "run away!"
Aug 26, 2011 at 01:40 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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?
more ▼

answered Aug 26, 2011 at 06:07 AM

WilliamD gravatar image

25.9k 17 19 41

I was just wondering about that - I'm a LiteSpeed user, and have no recollection of that feature being there...
Aug 26, 2011 at 06:13 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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?
more ▼

answered Aug 26, 2011 at 06:19 AM

Tim gravatar image

36.4k 38 41 139

(comments are locked)
10|1200 characters needed characters left

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: http://www.sqlskills.com/blogs/jonathan/post/An-XEvent-a-Day-(17-of-31)-A-Look-at-Backup-Internals-and-How-to-Track-Backup-and-Restore-Throughput-(Part-1).aspx

more ▼

answered Aug 26, 2011 at 06:13 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

Also Paul's blog at http://www.sqlskills.com/BLOGS/PAUL/post/Debunking-a-couple-of-myths-around-full-database-backups.aspx 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!
Aug 26, 2011 at 07:18 AM Kev Riley ♦♦
I knew there was something else about TLogs... thanks for the ref, @Kev
Aug 26, 2011 at 08:33 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 26, 2011 at 06:02 AM

Seen: 2147 times

Last Updated: Aug 26, 2011 at 06:02 AM