November 5, 2013 at 7:27 am
We've a server (SQL 2008R2) with two sets of full backups running at the sametime..One is local backup with sql agent job and other is with tsm to a different location..
Seems like sql server cannot allow concurrent full backups..I could see one process is getting blocked by other..
My understanding with fullbackup is, it doesn't lock any objects in the db and shouldn't block anyother userprocess..Can anyone explain why this is happening..
November 5, 2013 at 9:24 am
You can't full back up a database at the same time as another full backup is already running.
Worst case is the second will queue behind the first one until the latter completes.
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
November 5, 2013 at 10:00 am
thankyou..Yes, there's blocking and I understand we cannot have two full backups at sametime..Can you give me an explanation on why it cant be done or provide any BOL article about this issue..
My app team (backup reschedule requires their approval)would ask me the same thing, I'm trying to get full details on this issue..
thanks inadvance
November 5, 2013 at 10:27 am
OK, this is largely from my own experience, but backed up by what we know about database backups.
When a database backup is started, an internal snapshot is taken to preserve the state of the data on the data pages (rather like, but not exactly the same as, a database snapshot).
The backup process streams out each of the extents via this snapshot, so that a 'point-in-time' backup is possible. The process also keeps track of extents that have changed while the backup was being taken, then these are backed up at the end, thus guaranteeing that all the data that were in the database when the backup completed are in the backup.
It's not possible to run concurrent full backups because you would then effectively have 2 'snapshots' to maintain, and I'm not sure the storage engine is designed for that.
That said, why do you need 2 concurrent backups ? Or is this a scheduling thing ? 🙂
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
November 5, 2013 at 10:50 am
There's no snapshot involved in a backup, there's nothing that keeps track of changed extents during a backup process (other than the normal logging process) and changed extents are not included in the end of the backup file.
What happens is that the backup process first runs a checkpoint (all dirty data pages to disk) and then does a straight extent by extent copy of the data file on disk to the backup device. This is not necessarily a consistent point in time copy, it doesn't have to be. Once the copy of the data is done, the backup then backs up enough of the transaction log to ensure it can restore to a consistent point in time (the time that the data-copying portion of the backup completes). This is the log from either the checkpoint at the beginning of the backup or the oldest active transaction at the time the backup completes, whichever is older.
http://www.sqlskills.com/blogs/paul/debunking-a-couple-of-myths-around-full-database-backups/
It's CheckDB that uses an internal shapshot to get a transactionally consistent view of the database.
As for why you can't run two full backups, it's the way SQL is designed. I'm sure it would be possible (similar to the way you can run full and log backups concurrently), but it currently can't. There's a restriction on backups and file modification operations that requires that they run one after another.
That said, it wouldn't be a good idea to run concurrent backups even if it were possible. Twice the IO load, twice the memory usage does not make for a very practical feature.
To be honest, all backups should be done the same way. If there's a backup process (full, diff and log as necessary) to tsm, then don't add another full backup. At worst you mess up the restore path (if there's diffs), at best it's more time, more IO, more space required.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2013 at 11:00 pm
cooldude001 (11/5/2013)
My app team (backup reschedule requires their approval)would ask me the same thing
http://technet.microsoft.com/en-us/library/ms189315(v=sql.105).aspx
November 7, 2013 at 2:42 am
Could you reschedule and change tsm to simply backup from DISK the native backup file you have taken already?
Taking two full backups could impact your ability to take and restore differential backups. It wouldn't prevent you from doing so but it would take some planning and coordination.
Another thing to consider as a possibility is using WITH MIRROR. I've never used it in production myself.
BACKUP DATABASE AdventureWorks2012
TO DISK='X:\SQLServerBackups\AdventureWorks1a.bak',
DISK='Y:\SQLServerBackups\AdventureWorks2a.bak',
DISK='Z:\SQLServerBackups\AdventureWorks3a.bak'
MIRROR TO DISK='X:\SQLServerBackups\AdventureWorks1b.bak',
DISK='Y:\SQLServerBackups\AdventureWorks2b.bak',
DISK='Z:\SQLServerBackups\AdventureWorks3b.bak';
GO
November 7, 2013 at 3:06 pm
I can't imagine why you'd want to make two backups concurrently. If there is a need to have the ability to quickly restore to two separate, but close, points in time you should to a Full, then a Differenitial or Transaction Log backup. If there is a need to have a second full backup somewhere else, then have an agent job that copies the full backup BAK to the second location immediately upon completion.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply