Backup of SQL databases keeps failing. 2

[madeinwales]

I have a number of Databases on one SQL server (SQL 2000). I am backing them up remotely from an NT4 server running BE8.6. I have the SQL 2000 agent installed. one of the old databases (the only one I don't need to back up) backs up fine. The others however give the following error in the log file:

Performing Remote Agent backup

Media Name: "Media created 5/21/02 10:40:06 "
Backup of "TETRA "
Backup set #6 on storage media #1
Backup set description: "Tetra Tests"
Backup Type: DATABASE - Back Up Entire Database
Backup started on 5/21/02 at 11:31:35 .
Database filegroup from Tcr

An error occurred on a query to filegroup Tcr..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 Tcr?PRIMARY - skipped.

Backup completed on 5/21/02 at 11:31:41 .
Backed up 0 filegroup(s) in 1 database(s)
1 item was skipped.
Processed 0 bytes in 6 seconds.
Throughput rate: 0.0 MB/min
----------------------------------------------------------------------

======================================================================
Job Operation - Verify
======================================================================

Verify of &quot;TETRA &quot;
Backup set #6 on storage media #1
Backup set description: &quot;Tetra Tests&quot;
Verify started on 5/21/02 at 11:32:34 .
Database filegroup from Tcr

Verify completed on 5/21/02 at 11:32:34 .
Processed 0 bytes in 1 second.
Throughput rate: 0.0 MB/min
----------------------------------------------------------------------
Consistency checking tables from Tcr
Consistency checking PRIMARY
Consistency Check including Indexes.
Consistency check was completed.


======================================================================
Job ended: Tuesday, May 21, 2002 at 11:32:59 AM
Job completion status: Failed
======================================================================

This is the same for all the databases I am trying to back up, does anyone have any idea why this is happening?

 

[Scanner]

You need to change the recovery mode for all databases that have this problem to &quot;full&quot; instead of &quot;simple&quot;.

To do that (using SQL2k) do the following:
1) Open SQL Enterprise Manager

2) Navigate down the left pane until you see all of the databases

3) Right-click one of the databases with the problem and select &quot;properties&quot;

4) Select the &quot;Options&quot; tab

5) Find the &quot;Recovery&quot; section (halfway down) and change the &quot;Model:&quot; to &quot;Full&quot;

The recovery model setting also controls other items as well. For example, if the recovery model is set to &quot;simple&quot; you can't perform transaction log backups from a maintenance plan in SQL Server. That can be critical if you need more robust disaster recovery than just nightly backups.

You are using maintenance plans...right?

I hope this helps

 

[madeinwales]

Thanks for that, that should sort out the problem. I have simply been asked by the SQL Manager to back up the databases, however, I am almost certain that she will not be using any sort of maintenance plan, and as this is something I am not responsible for I wouldn't know where to start. I presume from your question that this is something that I should recommend she do?

 

[Scanner]

Before I start, I just noticed something. Your original message also mentioned backing up &quot;filegroups&quot;. Veritas doesn't support filegroup backups for any of the following databases:
1) model
2) msdb
3) northwind
4) pubs

Make sure that none of these four are selected for backup in any of the backup jobs listed in the &quot;job definitions&quot; tab.

Now...as for Maintenance Plans...

Yes, I would definitely recommend using them. Maintenance Plans are a way of creating a &quot;point in time&quot; backup of your critical DBs with scheduled dumps of the database(s) and accompanying transaction log file(s).

With a maintenance plan in place, even if the SQL agent on BackupExec doesn't work for some reason, there is still a static dump of the database and the transactions that hit against it that would be backed up just like any other text file.

It takes a little homework to get used to, but it's well worth it.

My database disaster recovery policy is as follows:

1) The maintenance plan &quot;dumps&quot; the three system databases (master, model and msdb) and all of my user databases every day at noon

2) The transaction logs are dumped every two hours between 7:00am and 7:00pm for only the user databases (you can't restore transaction logs on databases that have a recovery model of &quot;simple&quot. That part should sound familiar

3) BackupExec performs a full backup every night for the entire system (including SQL Server, using the SQL Server agent) at midnight

Just FYI...&quot;transaction logs&quot; are records of the hits against a database. When transaction logs are restored, they basically mimic the exact actions taken against the db, kind of like recording keystrokes. It that way they can be used to &quot;roll up&quot; a database to a more recent point in time than the last full DB backup. When the TLogs are dumped, they only dump the information since the last TLog. That way there are no duplicated entries in the database if they need to be restored.

IOW, if one of my user databases crash at 3:30pm, I would just recover from the maintenance plan &quot;dump&quot; at noon, then use the transaction log created at 1:00pm, then the one created at 3:00pm to &quot;roll up&quot; the hits to the database. I would only be out any work done in the 30 minutes between the last TLog and when the database went down.

Without the maintenance plan, I would have to restore from the previous night's tape (if it was a full DB backup) and I wouldn't have any way of recovering the lost work.

Even if it went down at 11:45am (before the noon dump) I would restore from the previous night's backup tape and use the Tlogs created before the crash (starting at 7:00am) to roll up transactions until 11:00. This way I would only lose out on 45 minutes of work.

I realize that this is far more than you really wanted to know, and it does drift WAY off of the topic of BackupExec (it probably should've been in the SQL Server forum) but I tend to get a little long winded when I explain things. She may already know more about maintenance plans than I do, but you may want to mention it just in case.

I hope I've helped, good luck and keep me posted.

 

[madeinwales]

Thanks again. I'm sorry about this, I hope you can excuse my ignorance, but as far as I see within Job Definitions|Selections, when I go to the SQL Server, the only boxes available to select under SQL Server are the Filegroups, unless I just select the databases in the right hand window. Originally I selected both the filegroups, in the left window, and the databases, in the right window, but when I saw there were problems I simply chose the filegroups. This seemed to back up the database as well (on one particualr db during a test backup), so I presumed that this was all I needed to select. As I am unfamiliar with SQL I don't understand the difference between the Filegroups and Databases.

 

[Scanner]

No problem. I get enough information and help from people on this website that I don't have the slightest bit of anxiety helping others when needed.

Filegroups are designed for EXTREMELY large databases. When the backup window isn't sufficient to allow a full backup of the entire database one option is to &quot;split-up&quot; the database sending selected tables, queries, indexes, etc... to a filegroup. Then you can selectively backup portions of the database over time to allow the backup window to work correctly.

There are a few rules that must be applied, however. The log files (Tlog) can't be separated in the same way as the data files. So you must remember that if you have to restore a filegroup, then you must also restore the log files separately before the database is back in sync. Also, you must remember to de-select the &quot;truncate log on checkpoint&quot; option for each database involved in the filegroup(s).

There is really no reason to use filegroups unless you are in an &quot;Enterprise&quot; level system. Perhaps if you are running a in a clustered environment or one BackupExec server is responsible for archiving several large database servers. Otherwise, you should just backup the database(s) by themselves.

And you're right, the only way to do that is to de-select the filegroups in the left window and select the database files in the right window.

And I would still recommend a good maintenance plan

 

[pctekk]

scanner,

do you have more details on how you run your "maintenance to dump the sql files" so then backup exec can pick them up ??

thanks so much !!

 

[Scanner]

I could elaborate if you like.

Before I do, you might want to start a new thread for this issue since this one is relatively old.

 

[pctekk]

np

starting it now..........
called
how to "maintenance to dump the sql files"

thanks