Problem backing up SQL DBs

[AlEman]

Hi,

I'm having a problem backing up certain dbs which is causing the backup job to fail. I have attached the relevant section of the log file.

I can manually back the database's up in SQL server management studio. However whenever I try to back them up in backupexec I get these errors?

Can anyone suggest how I can fix/troubleshoot this as I'm stumped!

Running BUE 12.0 sp3 on win2003. The databases are on SQL 2005.

Thanks,

Alun

[chicojrman]

Please try doing the following steps:

1. Please keep the consistency check before backup and after backup to None by going to tools-options-SQL

2. Please run a database consistency check using SQL on the database 'uwgm' if the same error occurs then the problem is with the database so to further resolve please contact SQL administrator.

[Owais]

To back up a database
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

Expand Databases, and depending on the database, either select a user database or expand System Databases and select a system database.

Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

In the Database list box, verify the database name. You can optionally select a different database from the list.

You can perform a database backup for any recovery model (FULL, BULK_LOGGED, or SIMPLE).

In the Backup type list box, select Full.

Note that after creating a full database backup, you can create a differential database backup; for more information, see How to: Create a Differential Database Backup (SQL Server Management Studio).

Optionally, you can select Copy Only Backup to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. For more information, see Copy-Only Backups.

Note:
When the Differential option is selected, you cannot create a copy-only backup.
For Backup component, click Database.

Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

Optionally, in the Description text box, enter a description of the backup set.

Specify when the backup set will expire and can be overwritten without explicitly skipping verification of the expiration data:

To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.
The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings Page). To access this, right-click the server name in Object Explorer and select properties; then select the Database Settings page.
To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.
For more information about backup expiration dates, see BACKUP (Transact-SQL).
Choose the type of backup destination by clicking Disk or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.

To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.

To view or select the advanced options, click Options in the Select a page pane.

Select an Overwrite Media option, by clicking one of the following:

Back up to the existing media set
For this option, click either Append to the existing backup set or Overwrite all existing backup sets. For more information, see Appending to Existing Backup Sets and Overwriting Backup Sets.
Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.
Optionally, enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.
Back up to a new media set, and erase all existing backup sets
For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box. For more information, see Creating a New Media Set.
In the Reliability section, optionally check:

Verify backup when finished.
Perform checksum before writing to media, and, optionally, Continue on checksum error. For information on checksums, see Detecting and Coping with Media Errors During Backup and Restore.
If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.

Note:
The options in the Transaction log section are inactive unless you are backing up a transaction log (as specified in the Backup type section of the General page).
SQL Server 2008 Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

To view the current backup compression defaul

[Owais]

 http://www.exforsys.com/tutorials/sql-server-2005/sql-server-backing.html
 browse this link ...it can help you out..!!

[Owais]

http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1116280,00.html



try this one in the last..!!

[Ben_L_]

Do the SQL error logs have any additional information?

[AlEman]

thanks for the input guys.

If a run a backupexec backup (on 1 of the faulty DBs) with the consistency checker switched off it still fails....

---------------------------------------------
Job ended: Friday, May 08, 2009 at 10:44:15 AM
Completed status: Failed
Final error: 0xe0008492 - Database Query Failure. See the job log for details.
Final error category: Resource Errors

For additional information regarding this error refer to link V-79-57344-33938

Errors
Click an error below to locate it in the job log
Backup- CITGM1 V-79-57344-33938 -
An error occurred on a query to database Knowledge.
V-79-57344-33938 - Connection failure
---------------------------------------------

As suggested above looking at the sql ERRORLOG file the below is logged....

---------------------------------------------
2009-05-08 11:37:52.03 spid138 Error: 7105, Severity: 22, State: 9.
2009-05-08 11:37:52.03 spid138 Database ID 8, page (1:101179), slot 0 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.
---------------------------------------------

I assume this means that it's SQL rather than backupexec problem? It's odd that the backup from SQL management studio works whereas backupexec bombs out.

-Alun

[Ben_L_]

Alun,

From that error I would say yes that the problem is in SQL not Backup Exec.

It's odd that the backup from SQL management studio works whereas backupexec bombs out. -- Actually it's not. Studio Manager and Backup Exec backup the databases two completely different ways.  Studio Manager just does a dump of the database to file. Backup Exec uses SQLVDI (per Microsoft Standards) to create a virtual device as a medium between the database and the backup device (disk or tape).

[AlEman]

Thanks Ben. I'll look into the SQl side of things.

-Al

[gviana]

Hi Alun.

Please inform us about this issue. 
Was it solved?