Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-13 : 09:54:12
|
| Paul writes "Greetings.After upgrading from SQL 7 to SQL 2000 and setting up Maintenance Plans,we had encountered a sp_dboption SINGLE_USER error when jobs to backupthe master and msdb were run. A work-around was discovered: disable the"repair minor problems" on the Integrity tab of the Maintenance Plan.(See error 352452 at: http://www.sqlpass.org/news/newsletter/kb_apr01.cfm)Subsequently, we continue to get an xp_sqlmaint failure whenever the jobto backup the Transaction Log for master or msdb is run. I have changedthe Recovery Model for both DBs from "Simple" to "Full", even though the"truncate log on chkpoint" option from SQL 7 for these DBs maps to the Simple recovery model. (Any comments on this step?)Prior to the SQL 2000 upgrade, Maintenance Plans (and their jobs) to backup system and user DBs ran w/o a problem. A named instance ofSQL 2000 was installed and the DB Copy Wizard used to copy the DBsto the new server. New jobs were created and backup jobs of systemDBs began to fail. Full backups now work (with the work-around), butI would like to get the T-Log backups for master and msdb workingas well.We are running Win NT 4.0 w/ latest SP and SQL2K SP2.Appreciate your insights,Paul" |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-05-13 : 10:27:46
|
| When your databases were in the simple recovery mode (had "trunc. log on chkpt" option set) your log backups had to fail. It's quite logical if you think about it: if the log is truncated automatically, there is simply nothing left for you to back up. I am not sure why you weren't getting these errors on SQL7, but I guarantee that as long as "trunc. log on chkpt." option was set, no log backups were performed. Once you switched your databases to the full recovery mode, you will need to take a full database backup before you can perform any log backups. Once you take a full backup, your log backups should run just fine. |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-05-13 : 11:40:39
|
| You can't take log backups of master - end of story.There was a bug that was fixed regarding maintenance plans with multiple databases - if one of the db's was unable to do log backups then they would fail for all db's in the maintenance plan. Since you cannot do a log backup of master the msdb log backup fails - this is presuming you have done a full backup since you switched recovery modes as the previous poster suggested (you need to use sp_dboption to make sure trunc log on chkpoint is not still on in msdb)I rarely set up log backups for msdb as the only thing that really changes is the job history which I'm not that bothered about (on a hour to hour basis). I find full nightly backups and a SIMPLE recovery model sufficentHTHJasper Smith |
|
|
|
|
|
|
|
|
SQL2K Backup Master Log Error