Maint Plan not deleting old Backup Files
-
It's such a mixed bag. I really like the TSQL language enhancements, having a try/catch has simplified my life a lot.
But with the maintenance plans, the senior DBA-s have decided to forego the new SISS based functionality for them and the related jobs and are scripting them all and will be running them from an Oracle based system that has been deemed more reliable than the SQL Server options.
The new rule of thumb is avoid SISS at all costs.
Skål - jh
-
I too am very disappointed in the new maint. plans. Very disappointed ! They were so easy, quick and reliable in 2000. They have gone very much backwards in that in 2005.
I used to brad how easy, quick and realiable they are in SQL Server to our main Oracle DBA as the RMAN 9i stuff works well but a pain to setup and admin.... Now, with 2005 I can no longer sing that tune.
I am not pressing our app folks to upgrade due to this.... sad but true !
-
Seems Im having the same problems too. I setup a Plan to perform a Clean Up History, Full Backup w/Overwrite, Check DB, Shrink Files, and Update Statistics once a week and Another for just Diff Backups. I have some notify user and retries on failures included throughout the plan.
It runs without error but the backup files arent overwriting and Im not sure what is actually happening. I created another one with just a Full Backup w/Overwrite and it works without problem for now but I dont really trust these things now.
I guess Ill skip trying these intricate plans I thought I had created and use SQL scripts with windows scheduler.
-
Found something strange and Im not sure if this should be happening. I enabled edit on the backup task object in my plan and selected the View T-SQL button to look as the SQL Script. I noticed that besides the Master database every other database was using "NOINIT" instead of "INIT".
I know that the statement may not be exact on the server but I thought that would have to do with dates and times. I think the option for overwriting the database shouldnt be conditional for a object that supposed to overwrite the file. If so then why would the Master database be overwriting?
-
As an alternate, you can this SQL command in the Scheduled Job, which does the neat job.
backup log xxxx to disk = 'd:\dumpdev\yyyy.trn' with init
xxxx = Name of the Database, yyyy = Name of the BackUp of log file.
Regards
N.Raja
-
Gentlemen, pls clarify my confusion with our 'favorite' MP.
So how do i specify the backup retention now?
Do i have to use Cleanup History Task? i thought that it only removes old History Files or it's removing old backup files as well?
It drives me crazy, why all these complications? Mama, take me back to my favorite sql 2000.
-
You must create another task of Maintance cleanup task and then specify the folders of which you have the db backups to delete from. Also, be careful of the file extention. You must specify bak only.... not *.bak.....
-
You can still use the BACKUP LOG database_name WITH TRUNCATE_ONLY. The server log will tell you this command is deprecated, but it still works to checkpoint the log file. Then you can DBCC SHRINKDATABASE ('database_name')
EJ
"If you don't take the time to do it right, when are you going to find the time to do it over?" -
Active transactions (tlog) will not be truncated if you run the backup log with TRUNCATE_ONLY option...
MohammedU
Microsoft SQL Server MVP
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply