Re: Is msdb transactional???

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/29/04 

Date: Tue, 29 Jun 2004 08:26:34 +0200

Mary,

It is true that backup history for all backups performed on an SQL Server is in the msdb database.
Just check the backup% tables in there. Since EM uses these tables to drive the restore dialog, I
prefer to always have an up-to-date copy of my msdb database. I implement this by doing backup of
the msdb database after I've done a backup of any other database (so I always have up-to-date copy
of the backup history tables). I could go for doing a database backup after each backup of the other
databases. But as I often do log backup once per hour for my normal database, I prefer to do log
backup for msdb as well. So, I set recovery to full, and then do both db and log backup for msdb.

One thing to be aware of is that Agent sets msdb to simple at startup. This is easily handled by
just creating a job that sets msdb to full and run that as an auto-start job in Agent.

There is no "once and for all" answer. Only you can decide what is appropriate for you. I know lots
of people who things that it is overkill to backup log of msdb. That is their opinion, which I
respect. Mine is different, though. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mary Kerrigan" <mkerrigan@ktoys.com> wrote in message
news:652faee5.0406281001.3562189e@posting.google.com...
> Once and for all, is the msdb database transactional?  I am getting
> conflicting answers searching for this in the newsgroup.  Do I need to
> backup the msdb transaction log?  One poster says that the backup
> history, jobs, etc. are kept in the msdb transaction log, another says
> they're kept in the database and NOT the log.

Relevant Pages

  • Re: MSDB (Subspect)
    ... I have one more suggestion to this issue, I feel that during the MSDB mdf ... and LDF physical file copy (cold Backup), they would have started the SQL ... Stop & Start the SQL Server ... > Probably because the database was damaged when you took the backup. ...
    (microsoft.public.sqlserver.server)
  • Re: Replacing Default Instance
    ... This is under the assumption only your MSDB has got issues. ... database can be preserved. ... If you have problems in detach/attach, try with backup and restore strategy. ... > tables do not have several columns required by the SQL Agent. ...
    (microsoft.public.sqlserver.server)
  • Re: MSDE2000, Recuperate from backup after DB is deleted.
    ... the backup was created from Enterprise Manager and the database was deleted ... I cannot recall if the backup history question ... Is there a system database available where it would be possible to replace ...
    (microsoft.public.sqlserver.server)
  • Re: MSDE2000, Recuperate from backup after DB is deleted.
    ... if you wish to delete the backup history as well and if you say yes it is ... > After making a backup of a database in an MSDE server, ... There does not seem to be a to point to the database backup file ...
    (microsoft.public.sqlserver.server)
  • Re: Coping everything to New Server
    ... Are these brought in with the master or msdb database? ... Sames thing with the Backup devices under ... > original sql server. ...
    (microsoft.public.sqlserver.setup)
Loading