473,239 Members | 1,598 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

Restore Messed Up Table Names

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
Sep 2 '06 #1
3 1951
David C. Barber (da***@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 2 '06 #2

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
David C. Barber (da***@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
Sep 3 '06 #3
David C. Barber (da***@NOSPAMdbarber.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..sysdatabases
ORDER BY name

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

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Sep 3 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Hai-Chu Hsu | last post by:
Hi, I want to restore my databases from the old SQL Server installation into a new installation of SQL Server. My new installation of SQL Server has different data path from the old...
0
by: barbara | last post by:
I am using this procedure from net for getting the restore script. It lists the latest full backup file name and logs after that point. Is there any way to modify this script to take either date...
0
by: tram | last post by:
I am setting up a job to automatically restore the standby database. This is to test our tape backups. I need to passon the latest full backup and log file names to the tape system to extract the...
1
by: APK | last post by:
Hi, I am trying to restore a tablespace from a tablespace backup image using following command. db2 " restore db G2P01 tablespace ( G2P01TB37 ) online from /db2_backups taken at 20040213070850...
0
by: papab2000 | last post by:
Before editing my db I did an export and have that file on my local machine. I messed up and deleted a table somehow and am trying to import the back up back in but it keeps telling me the data...
2
by: vj_dba | last post by:
Hi Group, I have a problem in restoring my tablespace, my database is running in ARCHIVAL logging, I created a tablespace, did some transaction, then took the ONLINE BACKUP of the tablespace....
14
by: blueboy | last post by:
Hi, I am planning to automate a nighty restore of a DB on another server can someone point me in the right direction with the SQL script to modify the logical file names to the correct path and...
0
by: zhif | last post by:
I tried to test this process on my personal laptop. Could you help me to take a look where is the problem as below? 1. I created a source database, name: db100 <-- db2 create db db100...
2
by: modeler | last post by:
Hello, I am trying to restore a DB2 9.1 fp2 backup to another DB2 9.1 fp2 machine, both are on Windows. C:\Program Files\IBM\SQLLIB\BIN>db2 -tvf C:\db2_data\test.db2 UPDATE COMMAND OPTIONS...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.