SQL backup problem. 1

[flo2980]

Recently my SQL server has been giving me a failure on the nightly backup. I get a message:

An error occurred on a query to filegroup msdb..PRIMARY.
^ ^ ^ ^ ^
The statement BACKUP DATABASE...FILE=<name> is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
^ ^ ^ ^ ^
Unable to open the item msdb?PRIMARY - skipped.

This causes the whole job to come back as failed. Any way around this?

 

[SQLBill]

Are you trying to backup the transaction log for the MSDB database? If so, you can't do that when the recovery mode is SIMPLE.

Which version of Backup Exec are you using?

-SQLBill

 

[flo2980]

The Version is 8.6 with the SQL module. The sever is a Dell Poweredge with a DLT drive. I will take a look at the log situation after I talk to our DBA.

Thanks........

 

[SQLBill]

According to the SQL Server Books OnLine (Help):

Backing up of filegroups "must be used in conjunction with transaction log backups. For this reason, file backups can only be used with Full Recovery or Bulk-Logged Recovery modes."

-SQLBill

 

[flo2980]

Thanks! It was a new one and they were including the filegroups. We have changed to Bulk and hopefully everything will go OK tonight.

 

[SQLBill]

Backup either the databases OR the filegroups but not both. Backing up both is a duplication of effort. A filegroup backup is done when the database is too large to backup at one time. For example: you have a database that is megaGB adn takes 12 hours to backup. You decide that's too long, so then you backup the filegroups instead. You can backup one filegroup this hour, one the next, etc.

-SQLBill

 

[flo2980]

Thanks SQLBill! Worked like a charm.

 

[pctekk]

SQL Bill !!

Can you help me w/ a backup of an Sql db ??
its on a remote box, sql 2000, BE v.8.6 rv 3878

i'm getting the error,
Unable to open the item cad - skipped.

cad = our db

thanks for any help

Blou

 

[SQLBill]

pctekk,

Instead on posting the same thing four or five times in someone else's thread, please start your own.

-SQLBill

Prepost checklists: FAQ481-4875 FAQ222-2244