May 3, 2006 at 3:12 pm
Hello,
Multiple schedule database backups are running on a single database. Every morning I look in the backup folder and there are several backups that have ran. one at 9:30pm another at 3:00 am etc...
Looks to me someone has ran the database maintenance planner wizard serveral times without knowing they already had a scheduled backup plan going.
I need to reduce this to one scheduled backup. Can someone tell me how to remove or stop the others using EM.
Thank you,
William
May 3, 2006 at 4:01 pm
Is it a question about SQL Server 2005 or SQL Server 2000? Depending on the version you can assign users to different roles, so they will not be able to create jobs. Also make sure that your scheduled backups are not the transaction log backups that are supposed to be run several times a day. But first of all, I would find out who created this jobs, he/she may have a very good reason to create them.
Regards,Yelena Varsha
May 3, 2006 at 4:15 pm
Yelena,
It is SQL 2005 and the jobs that are running are left over from the previous Administrator and a user. I need to clean everything up and make sure the backups are doing what they are suppose to do. I just don't know where to look to edit\remove the jobs in EM.
Thanks,
William
May 4, 2006 at 6:03 am
In SQL 2005 EM,
to find the maintenance plans: (DB_SERVER) -> Management -> Maintenance Plans
to find the scheduled jobs: (DB_SERVER) -> SQL Server Agent -> Jobs
** replace (DB_SERVER) with the name of your database server
Norene Malaney
May 4, 2006 at 6:04 am
I don't think you can connect from EM to SQL 2005 - I think you would get an error about needing to connect to SQL 2005 using SMO. Anyhow, for SQL Server Management Studio which is the replacement for EM with SQL 2005 there are a couple places you can check.
As far as jobs you can check those in Management Studio under SQL Server Agent, Jobs. You can double-click the job and depending what kind of job it is you might find what you're looking for in the job properties some place- maybe job step properties even. Or maybe you can tell from the job history (Right click, view job history)
As far as SQL 2005 Manintenance plans those are in Management Studio, Management, Maintenance Plans - you should be able to see databases part of maint plan in the propeties of the Maintenance plans.. assuming these are maint plans of course.. and if you upgraded from SQL 2000 to SQL 2005 you might have your old SQL 2000 maint plans now listed in management Studio under Management, Legacy - since those would have been migrated over.
As far as preventing users from accessing Management Studio that sounds to me like it would just be through security. Depending if your SQL 2005 server is using windows authentication only or windows and sql authentication- you certainly should be able to control who can connect- either by restricting members of the SQL Server local admin group and/or by restricting the SQL login privileges. With Management Studio you have to explicity "connect" to the SQL 2005 instance - so that should help you lock it down more.
Oh and incidentally you can manage SQL 2000 with Management Studio, but I really didn't think you could go the other way around (Enterprise Manager managing SQL 2005).
Good luck.
May 4, 2006 at 12:56 pm
David summarized everything correctly.
You may want also to read an article here posted just yesterday that describes roles for Agent users so they will be able or not will be able to create jobs:
Regular Columnist : Dinesh Asanka
Posted: 05/03/2006
SQL Server Agent 2005
http://www.sqlservercentral.com/columnists/dasanka/sqlserveragent2005.asp
Regards,Yelena Varsha
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply