backup on SQL2005EE

[Piotr Maciejewski]

I install SQL 2005 Express Edition (upgrade from SQL 7.0 MSDE)
When I execute:
BACKUP DATABASE [Market2000] TO DISK = N'C:\PROGRA~1\MARKET~1\file.bak'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

I received:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 4504 pages for database 'Market2000', file 'Pusta_Data' on file 1.
100 percent processed.
Processed 1 pages for database 'Market2000', file 'Pusta_Log' on file 1.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'mirror_count'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_password_protected'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'mirror'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'flags'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'unicode_locale'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'unicode_compare_style'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'collation_name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_password_protected'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'recovery_model'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'has_bulk_logged_data'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_snapshot'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_readonly'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_single_user'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'has_backup_checksums'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_damaged'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'begins_log_chain'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'has_incomplete_metadata'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_force_offline'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_copy_only'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'first_recovery_fork_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'last_recovery_fork_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'fork_point_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'database_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'family_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state_desc'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'create_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'drop_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'file_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_only_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_write_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'backup_size'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'filegroup_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_readonly'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_present'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'state_desc'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'create_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'drop_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'file_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_only_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'read_write_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_lsn'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'differential_base_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'backup_size'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'filegroup_guid'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_readonly'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'is_present'.
Msg 3009, Level 16, State 1, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
BACKUP DATABASE successfully processed 4505 pages in 8.069 seconds (4.573
MB/sec).

What is wrong? msdb?

[Erland Sommarskog]

Piotr Maciejewski (gbb...@qdnet.pl) writes:
> I install SQL 2005 Express Edition (upgrade from SQL 7.0 MSDE)
> When I execute:
> BACKUP DATABASE [Market2000] TO DISK =
> N'C:\PROGRA~1\MARKET~1\file.bak'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
>
> I received:

>...

> Processed 1 pages for database 'Market2000', file 'Pusta_Log' on file 1.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'mirror_count'.

>...

> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> BACKUP DATABASE successfully processed 4505 pages in 8.069 seconds (4.573
> MB/sec).
>
>
> What is wrong? msdb?

Indeed. It looks as if the definition of msdb is still on SQL7 and misses
new columns added in SQL 2000 and SQL 2005. I have no idea how that could
have happened. A possible workaround could be to install a new fresh
instance of SQL 2005, and then attach the databasees to that instance.
But then you would have to migrate users from the old instance.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Piotr Maciejewski]

Uzytkownik "Erland Sommarskog" <esq...@sommarskog.se> napisal w wiadomosci
news:Xns97077C2E...@127.0.0.1...

> Indeed. It looks as if the definition of msdb is still on SQL7 and misses
> new columns added in SQL 2000 and SQL 2005. I have no idea how that could
> have happened. A possible workaround could be to install a new fresh
> instance of SQL 2005, and then attach the databasees to that instance.
> But then you would have to migrate users from the old instance.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>

You right.
You idea works, but it is't sollution always.

Let's look at my post on microsoft.public.sqlserver.setup called: "Re: SQL
7.0 MSDE -> SQL 2005 Express".
There is more errors (miising files in setup, don't register SQLDMO, after
register it don't want to work well) when upgrading from SQL 7.0 MSDE to SQL
2005. I think than it hadn't be tested!

[Erland Sommarskog]

Piotr Maciejewski (gbb...@qdnet.pl) writes:
> You right.
> You idea works, but it is't sollution always.
>
> Let's look at my post on microsoft.public.sqlserver.setup called: "Re:
> SQL 7.0 MSDE -> SQL 2005 Express". There is more errors (miising files
> in setup, don't register SQLDMO, after register it don't want to work
> well) when upgrading from SQL 7.0 MSDE to SQL 2005. I think than it
> hadn't be tested!

I didn't look at your post in .setup. (Setup issues are always so difficult
to assist with. :-(

But I did get the tip that you may have to run instmsdb.sql to fix MSDB.
I was also told that msdb was not included in MSDE 1.0 by default. This
may be why Setup ignored it. If you do SELECT * FROM sys.databases,
which dbid does msdb have?