June 4, 2009 at 2:09 pm
Background: I'm new at this job, and whoever set up these backups doesn't work here any more. Very small company. Using SQL Server 2005. I have no experience in backups at all. I have used SQL Server from a development standpoint, but not administration.
I'm told that three of the backups are not working, then that one of them, which we'll call Plan A, is now working.
I open SSMS, and on the tree on the left, I open up Management, then Maintenance plans. I see 6 plans, and three of them have the same name as the three plans listed on the ticket, so I am fairly sure I'm in the right place.
I open Plan A, and it shows a diagram of sorts in the main screen. I right-click the name of Plan A on the tree on the left, and select View History. It shows that it has been working on a weekly basis, and stopped failing a month ago. (I say this because it has checkmarks listed for each line in the history, but a red circle thing in the week beore that).
I go back to the diagram thing, and double-click the title in that square. It shows me it's a Full backup, and under Databases, it says "Specific databases". I click that dropdown, and sure enough, certain databases are checked, and the others are not.
Back up is to disk, and a network path is listed under Create a File for every database. The checkbo under it, to create a directory for each DB, is also checked. Backupfile extension is bak, and verify backup integrity is checked.
Now, on to Plan B: Same as above, except backup type is Transaction log, database says "Select one or more". In other words, no specific databases have been selected. The file extension is trn, and Verify backup integrity is not selected. Before I leave this screen, I select a database from the Database dropdown.
Plan C is very much like Plan B. I also selected a database.
Sadly, when I return to B or to C, the Select Database dropdown has not kept my changes. It now says "Select one more more" again, when it should say "Specific Databases". I try this several times. I even click the Save icon up on the menu bar.
This brings up so many questions my head is spinning.
1. Why does Plan C not seem to have a problem while Plan B does? According to the History, C has some success, but B does not.
2. Why won't it save my Select Databse selection?
3. Why don't I see any setting which determines whether this database is daily, weekly, etc.?
Please go easy on me, and also please don't post to me that I need to read BOL. I won't post any question here unless I have tried other alternatives, starting with BOL and then Googling.
thanks
June 4, 2009 at 2:41 pm
Not sure why it doesn't save the databases you select though we had a similar problem with some of ours. Not sure if a service pack or CU fixed it.
You can see the backup schedule by editing the maintenance plan and looking along the top where the different steps are (although you may only have one) - there is an icon you can click on to see the schedule. Alternatively you can see by looking at the properties of the jobs (under SQL Server Agent in the Object Explorer window) and looking at the schedule in there.
When you look at the job history (View History) can you see any errors that indicate what the problem might be with the backups?
June 4, 2009 at 3:06 pm
Ok, that little section at the top with the calendar is probably the culprit. I say this because the two which are working both have something there, called a sub-plan, but the one which is failing and hasn't had any tries since January doesn't have any sub-plans. I just created one and we will see if it worked or not. Thanks.
As for errors, I don't know how to view any errors. I double-clicked the red circle with the X thru it, but nothing.
June 5, 2009 at 9:40 am
You can see the errors that occured when the jobs ran in a few different ways:
In Management Studio, go to SQL Server Agent then Job Activity Monitor. Find your job then right-click and view history.
or
Go to the ERRORLOG which is in a similar location to :\MSSQL.1\MSSQL\LOG. This should record that the job failed and maybe why.
or
Open the job log file (if there is one) which is in a similar location to :\MSSQL.1\MSSQL\LOG.
or
look in eventviewer at the application log (if the job writes to it)
June 5, 2009 at 11:16 am
Putting maintenance plans aside, are you able to successfully back up the db and/or tran log manually? Are the failures at specific times? There could be one of many reasons here, but as the previous poster stated, start with the error log ... see what you can find there.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply