Errors restoring tables

[Michael Rossetto]

A client recently changed his sort order and character set definitions. He is now unable to restore any of his tables. Prior to making the change he carried out the following steps;
1. Printed out the schema(scripts) for each database,
2. BCP`d all data out of SQL.

He the proceeded to make the change (via SQL Setup, rebuild master database option), then he recreated each device (data and log), recreated each database (data and log), ran each schema to rebuild each database (structure, views, etc) and then BCP`d all data back into databases.

He has been doing a full backup everyday since. He needs to restore a table but when he does so SQL reports that the schema is different between source and destination, no changes have been made.

[Jonathan Yang]

The backup and current DB have to be of the same Charset and sort order.
If the backup were done before the change in server was made, you are not able to restore it directly to the changed DB even if with the same table definition.


On 8/9/98 9:52:00 PM, Michael Rossetto wrote:
> A client recently changed his sort order and character set definitions. He
> is now unable to restore any of his tables. Prior to making the change he
> carried out the following steps;
1. Printed out the schema(scripts) for
> each database,
2. BCP`d all data out of SQL.

He the proceeded to make
> the change (via SQL Setup, rebuild master database option), then he
> recreated each device (data and log), recreated each database (data and
> log), ran each schema to rebuild each database (structure, views, etc) and
> then BCP`d all data back into databases.

He has been doing a full backup
> everyday since. He needs to restore a table but when he does so SQL reports
> that the schema is different between source and destination, no changes
> have been made.

[Michael Rossetto]

He is attempting to restore from a backup that has been carried out since the changes have been made.

On 8/9/98 11:32:49 PM, Jonathan Yang wrote:
> The backup and current DB have to be of the same Charset and sort order.
>
If the backup were done before the change in server was made, you are not
> able to restore it directly to the changed DB even if with the same table
> definition.


On 8/9/98 9:52:00 PM, Michael Rossetto wrote:
> A client
> recently changed his sort order and character set definitions. He
> is
> now unable to restore any of his tables. Prior to making the change he
>
> carried out the following steps;
1. Printed out the schema(scripts) for
>
> each database,
2. BCP`d all data out of SQL.

He the proceeded to
> make
> the change (via SQL Setup, rebuild master database option), then
> he
> recreated each device (data and log), recreated each database (data
> and
> log), ran each schema to rebuild each database (structure, views,
> etc) and
> then BCP`d all data back into databases.

He has been doing
> a full backup
> everyday since. He needs to restore a table but when he
> does so SQL reports
> that the schema is different between source and
> destination, no changes
> have been made.

[Jonathan Yang]

Here are two pieces of advice:

1. "rebuild master database option" was mentioned. Did not also reinstall the SQL server utility?

2. Apply SQL Server 6.5 SP4 if you hadn`t done so, in case your problem is caused by a bug which is already fixed.


On 8/9/98 11:41:44 PM, Michael Rossetto wrote:
> He is attempting to restore from a backup that has been carried out since
> the changes have been made.

On 8/9/98 11:32:49 PM, Jonathan Yang wrote:
>
> The backup and current DB have to be of the same Charset and sort
> order.
>
If the backup were done before the change in server was made,
> you are not
> able to restore it directly to the changed DB even if with
> the same table
> definition.


On 8/9/98 9:52:00 PM, Michael Rossetto
> wrote:
> A client
> recently changed his sort order and character set
> definitions. He
> is
> now unable to restore any of his tables. Prior
> to making the change he
>
> carried out the following steps;
1.
> Printed out the schema(scripts) for
>
> each database,
2. BCP`d all
> data out of SQL.

He the proceeded to
> make
> the change (via SQL
> Setup, rebuild master database option), then
> he
> recreated each
> device (data and log), recreated each database (data
> and
> log), ran
> each schema to rebuild each database (structure, views,
> etc) and
>
> then BCP`d all data back into databases.

He has been doing
> a full
> backup
> everyday since. He needs to restore a table but when he
> does
> so SQL reports
> that the schema is different between source and
>
> destination, no changes
> have been made.

[Don Romano]

Hi Michael,

Can you provide the exact here number and messages that are being returned?

I assume he is trying to restore individual tables and not trying to restore the entire database. (Seems obvious... but just checking)

I`m just a little confused and need some clarification of where the problem is. It sounds like he followed the process to rebuild the Master db:

1) bcp out the data from the business database
2) make the DDL script (what did he use... sp_help_revdatabase?)
3) rebuild the Master db to change sort order
4) recreate database using DDL script
5) bcp data back in...

It sounds like all of those steps worked and the database was working and populated after the Master db rebuild with the changes to characters set and sort order. It sounds like his database dumps work fine without errors.

Now when he tries to restore a table from a full backup he is getting a problem. Was he ever able to perform table restores before the change to the database? Can he perform a full database restore? Dumb question: no changes to table schema between backup and restore attempt, right? He meets all requirements for doing a table restore, right?... select into/bulk copy set, etc...

I`m just confused because there doesn`t seem to be an obvious reason for the problem so any additional detailed information might help.

Don