|
|
Posted by David C. Barber on 09/03/06 00:29
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9832738788829Yazorman@127.0.0.1...
> 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.
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
Navigation:
[Reply to this message]
|