December 22, 2011 at 1:11 am
Hello,
Given that I am currently backing up my test DB at regular intervals and that the disk file I am backing up to is growing ever larger with multiple instances of backups; how then can I govern the size of such backup files? Is there a backup strategy that you could recommend that would allow me to retain, say the last 5 backups, but then overwrite the oldest one and continue rolling over this way so that the last 5 backups are all that are kept?
I know I could do this manually by removing the backup disk file to another drive and doing this every 5 days, but I am guessing that there is a better way to do this and that maybe SSMS can help acomplish this..
Any advice or pointers to any plain-English articles on the broader subject of backup or restore would be welcome.
Regards
Steve
December 22, 2011 at 1:23 am
There is a maintenance cleanup task that should do this. See http://msdn.microsoft.com/en-us/library/ms345177.aspx for details.
Dave
December 22, 2011 at 1:25 am
It sounds like you are backing up to the same file every time, and you have multiple backups in the one file.
Various choices, you say you are new to this, and the database is a test one. In that situation I would look at database maintenance plans - these are easy to set up, can be scheduled automatically and allow you to keep n-days worth of backups etc.
You can get more sophisticated, and historically some people have not liked maintenance plans (often due to bugs/limitations in older releases) but in many cases thay can do a lot of the work for you.
Alternatively if you are running the backups via TSQL you need to look at the WITH INIT option of the backup command to control the overwrite/append behaviour.
Mike
December 22, 2011 at 2:19 am
December 22, 2011 at 10:20 am
raotor (12/22/2011)
Any advice or pointers to any plain-English articles on the broader subject of backup or restore would be welcome.
This is pretty straight forward.
As you may be aware of some of the "rules" a DBA should keep in mind read like:
-- DBA is responsible for database integrity.
-- DBA is responsible for database availability.
-- DBA is responsible for database recoverability.
If you put all of them together you end up with a good baseline for your backup strategy.
Backup strategy has to be designed in a way that ensures that you can recover and make available your database at a point in time that is accepttable for the business.
Not all databases are born equal, some are more critical than others which means the the business has different tolerance for loss of data and loss of service depending on database.
DBA has to work with business to put together a document describing the criticality of each database, usually in three categories like:
1- Critical - Business can't afford a downtime longer than an hour or data loss greather than 5 minutes.
2- Standard - Business can't afford a downtime longer than one day or data loss greather than 1 hour.
3- Non-Critical - Business can live for a week without this database, a one day data loss is acceptable
Once you get your categories and your databases categorized you develop your backup/recovery and business continuitiy strategy so to comply with the business needs.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply