Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    89

    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.

  2. #2
    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.

  3. #3
    Join Date
    Feb 2012
    Posts
    89
    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"

  4. #4
    Join Date
    Mar 2008
    Posts
    96
    Yes you can use 5 stripes.

  5. #5
    Join Date
    Feb 2012
    Posts
    89
    tnx for the reply ishu.cs.
    another thing would there be a max on how many stripes we can perform for each database? tnx

  6. #6
    Join Date
    Mar 2008
    Posts
    96
    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.

  7. #7
    Join Date
    Feb 2012
    Posts
    89
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •