Restore Messed Up Table Names

[David C. Barber]

Using SQL Server 2000 and moving to a new computer. We did a full backup of
the existing database to tape, brought up the new computer with a clean
install using the same server name and IP address, and did a full restore.
Not only were some permissions messed up, but Crystal Reports 10 and some
Access Data Projects refused to run. I finally discovered while running an
SP_WHO that the individual database names that we'd created (meaning not
'master' and the other standard tables) had several dozen blanks appended
onto the end of them. Looking at dbnames in the SP_WHO made it clear that
this had happened, and once I knew what I was looking for it was apparent in
Enterprise Manager as well when I'd select a database name in the left pane.
Interestingly, VB6 applications have no trouble connecting to these tables
without modification of the connection string. Every single CR10 report so
far has had to have it's tables relinked, and this has broken some other
code that looks at dbnames.

1: How could something like this happen?

2: How is it best fixed?

Thanks!
David

[Erland Sommarskog]

Re: Restore Messed Up Table Names

David C. Barber (david@NOSPAMdb arber.com) writes:

Using SQL Server 2000 and moving to a new computer. We did a full
backup of the existing database to tape, brought up the new computer
with a clean install using the same server name and IP address, and did
a full restore. Not only were some permissions messed up, but Crystal
Reports 10 and some Access Data Projects refused to run. I finally
discovered while running an SP_WHO that the individual database names
that we'd created (meaning not 'master' and the other standard tables)
had several dozen blanks appended onto the end of them. Looking at
dbnames in the SP_WHO made it clear that this had happened, and once I
knew what I was looking for it was apparent in Enterprise Manager as
well when I'd select a database name in the left pane. Interestingly,
VB6 applications have no trouble connecting to these tables without
modification of the connection string. Every single CR10 report so far
has had to have it's tables relinked, and this has broken some other
code that looks at dbnames.
>
1: How could something like this happen?
>
2: How is it best fixed?

I'm a little confused. You first say "existing database" in singular,
and then you say "individual database names" in plural. Your subject
talks about messed up table names, but table names do not display in
sp_who. Then again, you call master a table.

I'm sorry if I'm picky, but if I don't understand what you mean, it's
difficult to answer. But I try to address it as good as I can:

1) If the database are messed up, this is probably because you added
the spaces when you restored the databases on the new server. The
database names are not carried over from the old server. Or did you
copy master too?

2) If the table names have been altered this would be very strange.
I would even say that it is impossible.

3) What typically does gets messed up when you move databases like this
is the mapping between server logins and database users. This can easily
be examined with sp_helpuser. If you have a random mapping, then you
have this problem. The stored procedure sp_changes_user s_login can
be used to address this.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[David C. Barber]

Re: Restore Messed Up Table Names


"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns9832738 788829Yazorman@ 127.0.0.1...

David C. Barber (david@NOSPAMdb arber.com) writes:

Using SQL Server 2000 and moving to a new computer. We did a full
backup of the existing database to tape, brought up the new computer
with a clean install using the same server name and IP address, and did
a full restore. Not only were some permissions messed up, but Crystal
Reports 10 and some Access Data Projects refused to run. I finally
discovered while running an SP_WHO that the individual database names
that we'd created (meaning not 'master' and the other standard tables)
had several dozen blanks appended onto the end of them. Looking at
dbnames in the SP_WHO made it clear that this had happened, and once I
knew what I was looking for it was apparent in Enterprise Manager as
well when I'd select a database name in the left pane. Interestingly,
VB6 applications have no trouble connecting to these tables without
modification of the connection string. Every single CR10 report so far
has had to have it's tables relinked, and this has broken some other
code that looks at dbnames.

1: How could something like this happen?

2: How is it best fixed?

>
I'm a little confused. You first say "existing database" in singular,
and then you say "individual database names" in plural. Your subject
talks about messed up table names, but table names do not display in
sp_who. Then again, you call master a table.
>
I'm sorry if I'm picky, but if I don't understand what you mean, it's
difficult to answer. But I try to address it as good as I can:
>
1) If the database are messed up, this is probably because you added
the spaces when you restored the databases on the new server. The
database names are not carried over from the old server. Or did you
copy master too?
>
2) If the table names have been altered this would be very strange.
I would even say that it is impossible.
>
3) What typically does gets messed up when you move databases like this
is the mapping between server logins and database users. This can easily
be examined with sp_helpuser. If you have a random mapping, then you
have this problem. The stored procedure sp_changes_user s_login can
be used to address this.

Sorry that I wasn't more clear before. Comes of being in too much of a
hurry.

We were upgrading the server hardware for our SQL Server 2000.

We backed up the entire existing server as a full backup.

We installed the new hardware and software, named the new server to be
identical to the old server which was taken off-line entirely.

We used the same IP address with the new server box.

We did a full restore of the backed up server databases.

The resulting individual databases, except for the standard ones like master
that are created by SQL Server itself all restored with several dozen blanks
appended to the end of the existing database name, which appears to break
Crystal Reports 10, Access Data Projects, and some VB6 code, although not
the basic connection to the database using VB6.

We don't know why this happened, nor the best way(s) to fix it.

David

[Erland Sommarskog]

Re: Restore Messed Up Table Names

David C. Barber (david@NOSPAMdb arber.com) writes:

We were upgrading the server hardware for our SQL Server 2000.
>
We backed up the entire existing server as a full backup.

So you backed all files on the server with Windows backup, and you did not
backup the individual databases through SQL Servers own BACKUP command?

Did you stop SQL Server prior to starting this backup?

The resulting individual databases, except for the standard ones like
master that are created by SQL Server itself all restored with several
dozen blanks appended to the end of the existing database name, which
appears to break Crystal Reports 10, Access Data Projects, and some VB6
code, although not the basic connection to the database using VB6.

Could you post the output of this query:

SELECT len(name), datalength(name )/2, name
FROM master..sysdata bases
ORDER BY name

Is the old hardware available, so you can start it, and run the same
query there?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx