locked
back up RRS feed

  • Question

  • Thanks to all for sharing the new things for old problems

    now my doubt seems to be silly but i m having lot of confusion. I E, if we take back up for a user database all the data in my database will be copied to specified path but the data which is in database still be in database so nothing will change except i ve back up but space still its using same what i allotted in the  earlier .

    ex : my database size is 500GB but in every 15 days it s getting full. my back up plan is 20days full back up. every day differential back up for every 2 hours in a day  T log back up. so what can i do except increasing my hard disk capacity to do not disturb my backup plan. note here  i m dedicate the system hard disk capacity to this database almost. after fulfilling the requirement of  O S and server configuration

    hope someone will find a better plan for my problem

    thanking you

    chetan.tk


    Friday, July 8, 2011 7:31 PM

Answers

  • Hi Chetan,

    now my doubt seems to be silly but i m having lot of confusion. I E, if we take back up for a user database all the data in my database will be copied to specified path but the data which is in database still be in database so nothing will change except i ve back up but space still its using same what i allotted in the  earlier .

    Here answer will be dependent on your backup type ,If it is Full backup then your assumption is perfect (data pages will get backed up to specified path in the format of backup file ex .bak)

    If you are doing Transaction log backup (u r database must be in full recovery model)committed transactions will get backed up in to path as log backup file format and those transactions removed from log file.So your log file will not grow huge.

    If you are having space problem in Data drive you can implement different solutions filegroups,Data compression

    File groups

    Best solution for huge databases ,You can create read only file groups for archival data,apply windows level compression for this particular filegroup

    No need to take regular backups for these FGs.Refer below link

    http://msdn.microsoft.com/en-us/library/ms190257%28v=SQL.90%29.aspx

    Data Compression

    You have to use SQLServer 2005 ENt  latest sp (partial data compression)and SQL 2008 ENT for this Data compression.But it needs lot of understanding of your database objects details.And require CPU utilization.Its not recommended compare to above solution.

    If you are having space problem with backup drive ,others already provided solutions.

    In case you are  using SQL Earlier versions.You have to use third party compression utilities like winzip,winrar,7zip command line tools,to compress your backup files.

    Here i am not giving very detailed explanation for every task,but hope you got the  idea.

    Reply your comments.

     

     

     

     


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by Peja Tao Monday, July 11, 2011 3:24 AM
    • Marked as answer by chetan.kt Monday, July 11, 2011 10:02 AM
    Sunday, July 10, 2011 3:47 AM

All replies

  • If you are using SQL 2008, backup compression option you can save almost 85% space... my opinion you can take weekly one full backup could be week end, every day 2-3 differential backups.... and every 20 mins transactional backups....

    In-case you need to restore db... you can restore one full and following recent one differential backup and all log backups from that recent differential backups....

    If your db is very large then would suggest you to implement some kind of archiving processes, its depend upon company strategy my one 6 months data rest can push into archiving db that way you can keep db size in control on production servers.

    hope this helps you... let us know if you need any furthur info.....

     


    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by RaheelKhan Saturday, July 9, 2011 7:34 PM
    Friday, July 8, 2011 8:18 PM
  • You can do any of these to reduce space issue in the server - 

    1. Buy more space for your server
    2. consult with your users and  put some weekly or monthly archival process to free up space
    3. If you keep your backup files in the same server, probably you can use the backup file space in database growth by moving it to a network drive or buying a backup compression software like Litespeed if you are not on 2008 enterprise edition.

    Hope it helps,

    Thanks Shiju

     

    • Proposed as answer by Siva1983 Sunday, July 10, 2011 3:48 AM
    Saturday, July 9, 2011 4:45 AM
  • Hi Chetan,

    Your question is bit confusing.

    Are having space problem for Backup drive or data drive ?

    And which version of SQL you are using ?


    Sivaprasad.L Together We can Achieve
    Saturday, July 9, 2011 10:30 AM
  • Hi Chetan,

    now my doubt seems to be silly but i m having lot of confusion. I E, if we take back up for a user database all the data in my database will be copied to specified path but the data which is in database still be in database so nothing will change except i ve back up but space still its using same what i allotted in the  earlier .

    Here answer will be dependent on your backup type ,If it is Full backup then your assumption is perfect (data pages will get backed up to specified path in the format of backup file ex .bak)

    If you are doing Transaction log backup (u r database must be in full recovery model)committed transactions will get backed up in to path as log backup file format and those transactions removed from log file.So your log file will not grow huge.

    If you are having space problem in Data drive you can implement different solutions filegroups,Data compression

    File groups

    Best solution for huge databases ,You can create read only file groups for archival data,apply windows level compression for this particular filegroup

    No need to take regular backups for these FGs.Refer below link

    http://msdn.microsoft.com/en-us/library/ms190257%28v=SQL.90%29.aspx

    Data Compression

    You have to use SQLServer 2005 ENt  latest sp (partial data compression)and SQL 2008 ENT for this Data compression.But it needs lot of understanding of your database objects details.And require CPU utilization.Its not recommended compare to above solution.

    If you are having space problem with backup drive ,others already provided solutions.

    In case you are  using SQL Earlier versions.You have to use third party compression utilities like winzip,winrar,7zip command line tools,to compress your backup files.

    Here i am not giving very detailed explanation for every task,but hope you got the  idea.

    Reply your comments.

     

     

     

     


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by Peja Tao Monday, July 11, 2011 3:24 AM
    • Marked as answer by chetan.kt Monday, July 11, 2011 10:02 AM
    Sunday, July 10, 2011 3:47 AM