Backup databases

Hi

I'm trying to setup a back up plan for a number of databases, I initially set up one plan to include all user databases which worked fine or so I thought, when I check them a few days later I noticed that some of the databases were not appearing in the backup set, the only way I could get these to appear is to set the comp level to 90, now when we run certain applications we get an error, when I return the comp level back to 70 then the application works fine, is there a reason I can not back up any database on sql 2005 without it being a comp level 90?

Thanks inadvance

I'm guessing that the maintenance plan you've created uses some feature or syntax which didn't exist in SQL 7.0

Look over the TSQL backup commands in your maintenance plan and verify that the syntax there is compatible with SQL 7.0

|||

Hi thanks for the reply, but its not getting that far, stepping through the wizard,first it asks for the database(s) to back up, the choice being all system databases, or specific databases and I don't see any database that has not been set to comp level 90, so no tsql to check.

Thanks

|||

I see that on my system as well.

Is compatibility level 80 an option for you? Databases with that compatibility level do show up in the Wizard.

I'll check on why 70 databases are excluded, but I suspect that it has to do with what was supported at that version.

Ultimately your best option may be to write a backup script yourself.

|||

Hi

Yes thanks, not sure why I didn't think of that, but setting to comp level 80 does the trick,, thanks!

|||I have encountered the same problem when I setup the maintenance plan with the maintenance plan Wizard. Any help if I cannot set the comp. level to 80? Please advice, thanks!

Backup a Database with a number as name

I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?
Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>

Backup a Database with a number as name

I've got some numbered databases in an SQL Server 2000 instance with SP3a (e
g
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a
(eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>

Backup a Database with a number as name

I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
'300'). When I create a task to backup this database, I can't get the
statement parsed. I've tried the following:
backup database 301 to Disk_301 with init;
backup database '301' to Disk_301 with init;
backup database "301" to Disk_301 with init;
All statements above result in "Error 170: Incorrect syntax near '301'"
What's going on here?Wilbert,
You need square brackets.
create database [300]
go
backup database [300] to disk = 'c:\temp\300.bak'
go
Gives:
The CREATE DATABASE process is allocating 0.63 MB on disk '300'.
The CREATE DATABASE process is allocating 0.49 MB on disk '300_log'.
Processed 80 pages for database '300', file '300' on file 1.
Processed 1 pages for database '300', file '300_log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.254 seconds (2.588
MB/sec).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've got some numbered databases in an SQL Server 2000 instance with SP3a (eg
> '300'). When I create a task to backup this database, I can't get the
> statement parsed. I've tried the following:
> backup database 301 to Disk_301 with init;
> backup database '301' to Disk_301 with init;
> backup database "301" to Disk_301 with init;
> All statements above result in "Error 170: Incorrect syntax near '301'"
> What's going on here?
>

backing up the model database with BackupExec

Hello:
I used Backup Exec to try to backup a number of databases, including the mod
el database. However, the job failed with the following error message. Any
idea why? Also, if the model database is not part of the backup job, the b
ackup is ok.
An error occurred on a query to filegroup model..PRIMARY.
^ ^ ^ ^ ^
Could not find filegroup 'PRIMARY' in sysfilegroups for database 'model'.
^ ^ ^ ^ ^
Unable to open the item model?PRIMARY - skipped.
Thanks,
JoeHi,
Look into the below post from Microsoft Support for this error: ( I got thsi
from old posts)
When researching this issue, I found one issue where we found that the
customer was performing a full database backup, in addition to filegroup
backups. When a database recovery model is set to simple, we cannot
perform a filegroup backup. As a test, if you have this option set, what
happens when you remove the check for Filegroup backups in your backupexec
User interface?
Thanks
Hari
MCDBA
"Joe Brown" <health_livin@.yahoo.com> wrote in message
news:387823D1-8BC5-479D-9AF7-B0551C04BA7D@.microsoft.com...
> Hello:
> I used Backup Exec to try to backup a number of databases, including the
model database. However, the job failed with the following error message.
Any idea why? Also, if the model database is not part of the backup job,
the backup is ok.
> An error occurred on a query to filegroup model..PRIMARY.
> ^ ^ ^ ^ ^
> Could not find filegroup 'PRIMARY' in sysfilegroups for database 'model'.
> ^ ^ ^ ^ ^
> Unable to open the item model?PRIMARY - skipped.
> Thanks,
> Joe