locked
SQL Server Database backup RRS feed

  • Question

  • Hi Team ,

    I have SQL Cluster 2012 server running with Couple of Critical data base instance . Each instance installed in different drive.

    One of my application database is having mdf of size 1 Gb and ldf is 250 GB . Database is set to full recovery model.

    Now I want to change recovery model to simple and want to shrink the los also. before than I want to take database backup.

    I have 50 GB space left in drive .

    i want to take full database backup on same drive .please let me know how much space i need on that drive to take full backup.

    thanks


    Ravi

    Thursday, November 6, 2014 8:12 AM

Answers

  • Hi,

    It is pretty difficult to predict the backup size, if your using SQL standard  higher edition then you can make use of backup compression property while taking backups it would reduce your backup size to considerable amount.

    In our environment we have database of size 750 GB (650 GB data + 100 GB log) with compress backup the backup size comes upto 125 GB, but it may vary i n your case.

    you can refer below articles for backup compression.

    Backup Compression

    SQL SERVER – 2008 – Introduction to New Feature of Backup Compression


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Thursday, November 6, 2014 8:46 AM
  • There are two options for you here

    1. Take transaction log backup and try shrinking the log file but you might have to take multiple transaction log backups to actually shrink the log file

    2. If you are switching from full recovery to simple you dont need to take full backup reason is switching from full to simple breaks log chain so its better to have Transaction log backup and like Praveen suggested if you take transaction log backup with compression it likely it would fit in free space. But you have to try.

    3. This you have to do on your own risk: (If backup of log in point 2 fails) I dont think changing from full recovery to simple would cause any data loss. So you can switch to simple shrink logs switch back to full and schedule frequent log backups

    Reason why log grew so much because you did not took sufficient transaction log backups. So create maintenance plan which would take frequent log backups also if you dont want point in time recovery for this database let it be in simple recovery


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, November 6, 2014 9:41 AM

All replies

  • Hi,

    It is pretty difficult to predict the backup size, if your using SQL standard  higher edition then you can make use of backup compression property while taking backups it would reduce your backup size to considerable amount.

    In our environment we have database of size 750 GB (650 GB data + 100 GB log) with compress backup the backup size comes upto 125 GB, but it may vary i n your case.

    you can refer below articles for backup compression.

    Backup Compression

    SQL SERVER – 2008 – Introduction to New Feature of Backup Compression


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Thursday, November 6, 2014 8:46 AM
  • There is no easy way to predict unless you take a backup.

    When you try to take the backup and if it fails it will exactly tell you how much space will be required.

    it will be sp_spaceused of the database and what ever transactions in the tlog is required to make the database consistent.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, November 6, 2014 9:23 AM
  • There are two options for you here

    1. Take transaction log backup and try shrinking the log file but you might have to take multiple transaction log backups to actually shrink the log file

    2. If you are switching from full recovery to simple you dont need to take full backup reason is switching from full to simple breaks log chain so its better to have Transaction log backup and like Praveen suggested if you take transaction log backup with compression it likely it would fit in free space. But you have to try.

    3. This you have to do on your own risk: (If backup of log in point 2 fails) I dont think changing from full recovery to simple would cause any data loss. So you can switch to simple shrink logs switch back to full and schedule frequent log backups

    Reason why log grew so much because you did not took sufficient transaction log backups. So create maintenance plan which would take frequent log backups also if you dont want point in time recovery for this database let it be in simple recovery


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, November 6, 2014 9:41 AM