Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to speed up backup process. Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 10:59 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:46 AM
Points: 1,588, Visits: 2,661
Hello,

Apart from Backing up to multiple locations and compressing the backup file, is there any other way to SPEED UP the Backup Process ?. Without using third party tools of course.

Thanks in advance.
Smith.
Post #1360558
Posted Tuesday, September 18, 2012 5:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 14,201, Visits: 28,530
Hardware and compression are the two best things that I know of for making backups run faster. There just aren't any knobs to twist or switches to throw that make a big difference.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1360699
Posted Tuesday, September 18, 2012 12:11 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:01 AM
Points: 1,299, Visits: 3,003

One thing i have heard from one of the well know MVP's is that if you re-org your indices and then take a backup it might save some time and space.
Post #1360928
Posted Wednesday, September 19, 2012 9:41 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:46 AM
Points: 1,588, Visits: 2,661
..Something came up my mind this morning..

Suppose a transaction was started before the backup process starts, only one transaction for example.
Backup process back up the pages.. transaction is still open..
But before the backup completes the above said transaction commits/Roll Back.
So backup will also include the part of LOG for "Roll forward/Roll Back" while restoring the database.

Now my question is that, backing up the LOG is an overhead here is it ? So if I disconnect all the users before the backup starts, ensuring there's no any transaction open which may or may not commit before the backup process ends, would it have been more faster ?. (I know I just can't disconnect all the users ofcourse ! )


Thanks.
Post #1361713
Posted Thursday, September 20, 2012 12:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 4, 2014 5:29 AM
Points: 965, Visits: 396
Given that the bulk of the time taken to do a backup is I/O, the things to do to speed up the backup are based around reducing that - use whatever in-built compressions you have within SQL Server to compress the data files and the backup files being created.

The other thing you can do is make sure that your database isn't cluttered up with data that's not needed - look into various archiving strategies for really old un-touched data that you only need for compliance / audit, for example. Archive it off into a separate database, or keep an "old stuff" filegroup that doesn't get backed up as often as the "current stuff" filegroup. Hmm. I'm going to have to think about that one...



Post #1361759
Posted Thursday, September 20, 2012 3:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 14,201, Visits: 28,530
Joy Smith San (9/19/2012)
..Something came up my mind this morning..

Suppose a transaction was started before the backup process starts, only one transaction for example.
Backup process back up the pages.. transaction is still open..
But before the backup completes the above said transaction commits/Roll Back.
So backup will also include the part of LOG for "Roll forward/Roll Back" while restoring the database.

Now my question is that, backing up the LOG is an overhead here is it ? So if I disconnect all the users before the backup starts, ensuring there's no any transaction open which may or may not commit before the backup process ends, would it have been more faster ?. (I know I just can't disconnect all the users ofcourse ! )


Thanks.


Yeah, if you give the backup process less work to do, handling rollback/rollforward, then yeah, it'll be faster. But I wouldn't suggest disconnecting all users was a viable tuning method.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1361823
Posted Thursday, September 20, 2012 3:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:45 AM
Points: 1,058, Visits: 2,696
Joy Smith San (9/19/2012)
if I disconnect all the users before the backup starts, ensuring there's no any transaction open which may or may not commit before the backup process ends, would it have been more faster ?. (I know I just can't disconnect all the users ofcourse ! )


one more thing if the user is operating some data wont that gets hampered.

How about weekly full backup on sunday night and hourly or other timing of Differential backup? hope this will defenetiley help large DB's.





Regards
Durai Nagarajan
Post #1361831
Posted Thursday, September 20, 2012 9:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:36 AM
Points: 31,362, Visits: 15,823
Users working with data don't impact the data reading portion of the backup. They can impact the log, as noted above, but that's mostly depending on the amount of logging done.

As far as the timing, that can impact backup since you have I/O and CPU resources being shared among all processes. If the backup runs at the same time as something else doing work on the server, there is an impact, but it's never been that significant for most of my servers unless heave compression is in use.

The ways you speed up backups are compression (needs more CPU, reduces I/O) and backup to faster or multiple devices.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1362100
Posted Friday, September 21, 2012 8:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:36 AM
Points: 893, Visits: 2,476
For minor possible changes, you can look at adding the options:
BLOCKSIZE = 65536
BUFFERCOUNT = <something appropriate - maybe try 12 for 4GB RAM or any 32-bit, 128 for 64GB RAM>
MAXTRANSFERSIZE = 4194304

Other than that and the advice above, adding more disks to source or target drives and/or checking for filesystem level fragmentation on the source or the target are options.
Post #1362721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse