I've been struggling with trying to import an entire database from sql2k to
05 and keeping all the indexes, keys, etc. intact. If I just run a straight
dts, then only the data gets imported. So I referred to my trust wrox SSIS
book and it showed me how to imports a dts package from sql2k into sql 2005
and then run it. So I did. Of course it failed. now I can't access any
object in the sql2005 server via SSMS. I rebooted the machine and now I
cant open the server in SSMS at all. 'Login failed for user
domain\Administrator'. MS SQL Server Error 18456. It seems that all the
user accounts in sql server got corrupted or something. I would have
thought MS would have made it easier to migrate 2k dbs to 2005. any advise?

Thanks.

Hello,

If you want to move entire database from SQL 2000 to SQL 2005, I suggest
that you refer to the following article:

314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546

You could backup the database, and restore them on SQL 2005. Also, you
could use Detach/Attach method.

Optionally, You could use "copy database wizard" in SQL 2005 by right
clicking a database->Taskes->Copy Database

As for the current issue about login issue, you may want to check if you
could log on as local admin of the server, and then remove/re-add the
problemtic logins such as domain admin.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks Peter,

1) I will look into the copy database wizard.

2) I've already tried the backup and restore method as well as the attach
and detach method. These worked, however, there's no way to determine the
health of the database after being moved to sql server 2005 (from sql server
2000). For example, I might have some design issues or corrupt indexes,
keys, etc. in the database that simply get moved to the new 05 server. If I
run a data transformation package via SSIS, I assume that SQL Server 2005
will rebuild the new database correctly according to 2005 standards, and if
it can't, it will fail until I fix all the issues. This is my reason for
running a package. For example, sometime when I needed to copy a db from
one 2k server to another 2k server, the dts would fail because an old stored
procedure referenced a table column that no longer existed. the dts found
the problem where a detach-attach would not. When I run the Copy Database
Wizard, will it check everything in the database (such as data integrity) to
make sure things get rebuilt correctly similar to how the dts packages do?

3) I can't log into the server using any login. Before I rebooted the
machine and SSMS was still connected to the server, when I clicked on a
database I would get an error saying something about not being able to
connect to the database because login1 failed, login2 failed, login sa
failed, login sysadmin failed, etc. This is why I think all logins became
corrupt. I recommend passing this on to the development team to look into.
it could be a serious issue on someone's production machine.

"Peter Yang [MSFT]" <petery (AT) online (DOT) microsoft.com> wrote

OK I got past the server login corruption issue where I ran a repair on the
sql server instance. I was then able to login as sa and add in any missing
logins. one login that was missing was domain/Administrator. This is a
test machine and I login as administrator. Therefore, when I setup sql
server to connect via Windows Authentication, it was looking for the user
domain/Administrator which was no longer assigned to this instance of sql
server. after adding it back in, my connect issues seem to have been
resolved. however, my issues/questions in the post above still remain
regarding coping a sql2k db to a sql05 server and having all db structure
and integrity checked in the copy process.



"Peter Yang [MSFT]" <petery (AT) online (DOT) microsoft.com> wrote

OK I ran the copy database wizard in SQL 2005 and it allowed me to make the
choices I wanted such as don't import users and roles. It did import all
indexes and keys. and when there was an error such as an old SP referencing
a column name that no longer existed, it stopped the process and wrote the
error telling me which SP and column in question to a log file. This is
what I needed. after resolving all issues, it copied the db and all seemed
to be in good health.


"moondaddy" <moondaddy (AT) nospam (DOT) nospam> wrote

Great to see copy database wizard works.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.