Backups Failing

I set up a full backup maintenance plan for my databases and the database portion of the backup jobs completes successfully, but the transaction log portion successfully backs up master and model, but fails for the other databases with the message:

Backup can not be performed on database 'msdb'. This sub task is ignored.

What's the problem?

Bob

Check the recovery model of your databases. They are probably in SIMPLE mode so transaction logs are not allowed.

HTH!

|||

I thought this was answered, but it's not. I have never had the master db transaction log backed up. I have set it to full backup as I have with the others and they all work, but master still does not.

Bob

|||

You can only perform transaction log backups on databases in the full or bulk-logged recovery modes (check your db options for this). Master and MSDB databases are always set to simple therefore no transaction log backup can occur on these databases. When configuring your maintenance plans these should be omitted.

|||As said above you can perform only Full backups with Master database regardless of the recovery model ! and full,differential and tran log backups with msdb db if the recovery model is full or bulk logged ! for model db if the recovery is full all 3 backups are possible if its simple only tran logs are not possible in model

|||

Thanks all - I see that now.

Backup/Restore Status

Very often I have to do large backup and restores. This is either done via jobs, or the SQL Maintance tools. Neither so any status of a backup/restore. Overall progress, bytes written, etc. EM when backing up or restoring does show a GUI progress bar that isn't perfect, but does show progress to some extent.
So far I can look in the sysprocesses tables and find what spid is running the command, but that doesn't tell me much. The console command line util is for tape backups. Is there anything that can monitor the progress of backups and restores.
Thanks,
ETThe native SQL Server BACKUP and RESTORE commands have a STATUS parameter,
that when specified, returns status messages, indicating the progress of the
backup/restore operation.
Here's an example:
RESTORE DATABASE YourDB FROM DISK='\\SomePath\SomeFile.BAK' WITH STATUS = 1
See, SQL Server Books Online for more information.
Note that, even after specifying the STATUS with BACKUP or RESTORE command,
you will not see any progress messages coming up, while SQL Server is
creating the file.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Twiggy" <etwilegar@.nospamttgonline.com> wrote in message
news:D4500856-A29C-44EF-A7E8-828D7F6DDBCD@.microsoft.com...
Very often I have to do large backup and restores. This is either done via
jobs, or the SQL Maintance tools. Neither so any status of a backup/restore.
Overall progress, bytes written, etc. EM when backing up or restoring does
show a GUI progress bar that isn't perfect, but does show progress to some
extent.
So far I can look in the sysprocesses tables and find what spid is running
the command, but that doesn't tell me much. The console command line util is
for tape backups. Is there anything that can monitor the progress of
backups and restores.
Thanks,
ET|||Well I knew that EM was doing this and grabbing the results. In this case I'm not in control of the actual restore command. The command is already running, is there a way for another connection to get status on the operation. Is there a system table that information is written to?|||No Twiggy, there is not...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Twiggy" <etwilegar@.ttgonline.com> wrote in message
news:72E2D27A-36B1-4013-94D8-04A60F0A8782@.microsoft.com...
> Well I knew that EM was doing this and grabbing the results. In this case
I'm not in control of the actual restore command. The command is already
running, is there a way for another connection to get status on the
operation. Is there a system table that information is written to?
>|||I find this really weird. I wonder how the heck their internal developers debug backup/restore issues. I guess the code for backup and restoring hasn't changed a lot since 7, but still I'm sure there is a switch that would turn on logging of some sort. I'd hate to be the guy that had to write the Enterprise manager code, parsing the text output from restore with status.
Guess I'll wait for mirroring.

BACKUP VERIFY

Hi everyone,
While investigating why some of our SQL BUs were taking so long I have
realised that the person who set up the jobs initally has actually included 2
statements in the job step: a BACKUP one and a BACKUP VERIFY statement.
Now at the moment, and I am not sure if it is right, the verify statement is
taking longer to run than the initial backup.
I understand why you would want to use this statement straight after running
a back up but considering that it is taking so long, is it actually causing
more problem in our situation.
Before I consider disabling the statement I would like to get
recommendations on how, when, where and why you would use it? I am after best
practices and any known issues that may be related to the BACKUP VEDRIFY
statement.
Any info, suggestions, recommendations are welcomed regarding the subject.
Thanks in advance
Regards
BabeBACKUP VERIFY dos not guarantee that the backup file can be restored, though
it does give you a higher degree of confidence. In practice, I rarely, if not
never, use it. I certainly would not schedule it immediately after each
BACKUP statement for databases of any significant size.
Linchi
"Babe" wrote:
> Hi everyone,
> While investigating why some of our SQL BUs were taking so long I have
> realised that the person who set up the jobs initally has actually included 2
> statements in the job step: a BACKUP one and a BACKUP VERIFY statement.
> Now at the moment, and I am not sure if it is right, the verify statement is
> taking longer to run than the initial backup.
> I understand why you would want to use this statement straight after running
> a back up but considering that it is taking so long, is it actually causing
> more problem in our situation.
> Before I consider disabling the statement I would like to get
> recommendations on how, when, where and why you would use it? I am after best
> practices and any known issues that may be related to the BACKUP VEDRIFY
> statement.
> Any info, suggestions, recommendations are welcomed regarding the subject.
> Thanks in advance
> Regards
> Babe|||Please note, that usefullness of BACKUP VERIFY depends on version of MS SQL.
On MS SQL 2005 it checks checksums. On the previous versions it only checks
the internal structure of backup file.
Recuping, the only way to check your backup is restore. Doing BACKUP VERIFY
on the versions before MS SQL 2005 (specially in the cases other than tape
backup where media errors are more probable) is wasting of your time.
"Babe" wrote:
> Hi everyone,
> While investigating why some of our SQL BUs were taking so long I have
> realised that the person who set up the jobs initally has actually included 2
> statements in the job step: a BACKUP one and a BACKUP VERIFY statement.
> Now at the moment, and I am not sure if it is right, the verify statement is
> taking longer to run than the initial backup.
> I understand why you would want to use this statement straight after running
> a back up but considering that it is taking so long, is it actually causing
> more problem in our situation.
> Before I consider disabling the statement I would like to get
> recommendations on how, when, where and why you would use it? I am after best
> practices and any known issues that may be related to the BACKUP VEDRIFY
> statement.
> Any info, suggestions, recommendations are welcomed regarding the subject.
> Thanks in advance
> Regards
> Babe|||> On MS SQL 2005 it checks checksums.
... assuming the checksums are there, which they aren't by default. So to get a more reliable check,
the backup should be taken using the CHECKSUM option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Dmitrij Siemieniako" <DmitrijSiemieniako@.discussions.microsoft.com> wrote in message
news:28A1B0A8-D219-46D4-B572-92D0E2FBEA66@.microsoft.com...
> Please note, that usefullness of BACKUP VERIFY depends on version of MS SQL.
> On MS SQL 2005 it checks checksums. On the previous versions it only checks
> the internal structure of backup file.
> Recuping, the only way to check your backup is restore. Doing BACKUP VERIFY
> on the versions before MS SQL 2005 (specially in the cases other than tape
> backup where media errors are more probable) is wasting of your time.
> "Babe" wrote:
>> Hi everyone,
>> While investigating why some of our SQL BUs were taking so long I have
>> realised that the person who set up the jobs initally has actually included 2
>> statements in the job step: a BACKUP one and a BACKUP VERIFY statement.
>> Now at the moment, and I am not sure if it is right, the verify statement is
>> taking longer to run than the initial backup.
>> I understand why you would want to use this statement straight after running
>> a back up but considering that it is taking so long, is it actually causing
>> more problem in our situation.
>> Before I consider disabling the statement I would like to get
>> recommendations on how, when, where and why you would use it? I am after best
>> practices and any known issues that may be related to the BACKUP VEDRIFY
>> statement.
>> Any info, suggestions, recommendations are welcomed regarding the subject.
>> Thanks in advance
>> Regards
>> Babe|||Hi everyone, thank you for the information/suggestions!
--
Babe
"Babe" wrote:
> Hi everyone,
> While investigating why some of our SQL BUs were taking so long I have
> realised that the person who set up the jobs initally has actually included 2
> statements in the job step: a BACKUP one and a BACKUP VERIFY statement.
> Now at the moment, and I am not sure if it is right, the verify statement is
> taking longer to run than the initial backup.
> I understand why you would want to use this statement straight after running
> a back up but considering that it is taking so long, is it actually causing
> more problem in our situation.
> Before I consider disabling the statement I would like to get
> recommendations on how, when, where and why you would use it? I am after best
> practices and any known issues that may be related to the BACKUP VEDRIFY
> statement.
> Any info, suggestions, recommendations are welcomed regarding the subject.
> Thanks in advance
> Regards
> Babe