December 28, 2011 at 7:46 pm
I have been using maintenance plans to backup all the user databases on my SQL Servers. Lately I have found that the location of my backups has been running out of space before all of them can complete. I have several databases that do not get new data written to and I have been trying to figure out how to use the maintenance plans to get any new user database that is created while excluding the databases that do not get written to. I have throught about moving these databases but the applications that use them would need a lot of work to make that happen. Expanding the drive on the SAN is another option, but there has to be a better way.
Anyone out there have any thoughts other than not using maintenance plans, I know many in the community do not like them, I have found them a easy way to standardize across multiple SQL Servers a clean way of backing up and maintaining databases.
thanks
Aaron
December 28, 2011 at 8:25 pm
They are a quick way of setting up backups and maintenance routines, but they have limitations.
Before we can really help, however, we need to know how your current processes are working. Are you using the maintenance plans to delete older copies of your database backups? Are you appending database backups to existing files? Also, iirc, when you add a new user database you have to add it to an existing maintenance paln or create a new one. I personally don't use maintenance plans myself as I have more control when I use my own home grown plan for backups, index and statistics maintenance.
December 29, 2011 at 7:35 am
My jobs start with deleting the previous backup. I was looking to see if someone had a cursor, or knew of a system table that could be read to find out if a database was written to. Then I could write a backup script to only backup those databases everday, then I would do a full server backup say once a month to get the static databases to tape.
December 30, 2011 at 2:35 pm
How about differential backups? if the issue is space the size of differential backups will follow the volume of data that actually changed on those particular databases.
For these low volatility databases I would take a monthly full backup then a daily differential.
_____________________________________
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply