|
|
Posted by Erland Sommarskog on 09/02/06 09:21
David C. Barber (david@NOSPAMdbarber.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_users_login can
be used to address this.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.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
Navigation:
[Reply to this message]
|