If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Sybase > backing up large database

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 82
backing up large database

hi guys,
i have large databases around max of 20gig per databases. the problem is that it takes a long time to backup each database everyday. I have thinking to have workaround of backing up first the transaction log then truncate_only or with no_log since it will run full backup at midnight,
what do you think guys. any suggestions. tnx

dump tran db1 to <path>
go
dump tran db1 with truncate_only or dump tran db1 with no_log?
go
dump database db1 to <path>
go

i've been running sybase ase 12.5.3 on windows 2k3 platform.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2005
Location: South Africa
Posts: 1,353
Reduce backup time

Try stripping across multiple files
Or if your disk subsystem support snapshot backups consider using quiesce database and use snapshot backups instead.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 82
can i stripe the backup into 5? sample below.

dump database to "d:\backup\db_s1.dmp"
stripe on "d:\backup\db_s2.dmp"
stripe on "d:\backup\db_s3.dmp"
stripe on "d:\backup\db_s4.dmp"
stripe on "d:\backup\db_s5.dmp"
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 95
Yes you can use 5 stripes.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 82
tnx for the reply ishu.cs.
another thing would there be a max on how many stripes we can perform for each database? tnx
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 95
there is no definite rule for deciding the number of stripes.
Actually the multiple stripe concept work as below.
When multiple stripes are used, database dumps are written to all the devices paralelly and equally.
For instance if we use 5 stripes to take the dumps, on OS level 5 files will be created for the dumps.
ASE will write the dump on all the devices parallely and in equal amount, so we will see that the size of all the 5 files, once the dump is finished, will be same.

So by using stripes, we are using multi processing functionality of OS.

So we can say more number of devices, means more parallism at OS level.
But as the number of stripes increases the burden on OS for performing mutiprocessing also increases, which will again reduce the dump process speed.

So in your case as database is of 20 GB, 5 stripes (of 4 GB each) will be a good idea.
You can further reduce the size of files using compress option while taking the dumps, ideal compression level will reduce the dump size to 60% of the actual database size(its my experience, and may vary from platform to platform, also depends on ASE version).

Hope this will be helpful.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 82
thank you ischu.cs for these inputs.

I've been looking the idea of using compression level with 5 stripes for the backup since it may reduce the dump size by what you've mentioned by 60%.

Thanks again it helps a lot.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On