October 19, 2004 at 4:35 am
Hello
In some of our SQL servers, when creating a maintenance plan, in the 'complete backup' tab, the dropdown for 'delete files older than' (which should contain minutes, hours, days, weeks, months) is empty.
Is there a reason for this, such as the SQL Server Agent running without sufficient privileges, is it a known issue, or is there some other reason for it? I've done a search on google, and seen one post on it, but the person rebuilt the machine to fix it (something we'd like to avoid!)
I know that I could write some T-SQL to backup the db (and delete files older than 4 days) and add it as a job, but I'm not a dba and would rather the people looking after the machines be able to create the maintenance plans, as inevitably they will create a db but neglect to tell someone, meaning the backup does not get carried out.
Cheers
Adrian
October 19, 2004 at 5:17 am
Forgot to say, db servers are SQL Server 2000 running on Win 2000 server, with OS and DB service packs applied.
Adrian
October 20, 2004 at 3:27 am
Looks like I have found a solution to this, thanks to a posting to a mssql newsgroup by Himadri Sarkar:
I believe you are running into a specific problem related to MSDE sp2 because prior releases of MSDE did not include sqlmaint. In sp2 we added sqlmaint to MSDE allowing us to create database maintenance plans however sometimes the registry key for default backups is not added.
To resolve the problem you need to do two things:
1) create a default backup folder (optional)
2) Add the reg key
HKLM\software\microsoft\mssqlserver\mssqlserver\backupdirectory and point it to the folder where you want your default backups to go.
Once the reg key is there, the dropdown has the values. The machine I was having problems with I found out had SQL desktop engine and was upgraded to the std edition, but it looks like the upgrade did not create the reg key.
Adrian
October 18, 2005 at 5:04 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply