Moving a SQL Database to Another Server

Question:
I have a SQL database on our production NT server and need to move the database to another NT server. I used the Database/object transfer in Enterprise Manager to copy the database to the new server and it appeared to be successful. The tables are updated using Microsoft Access.

However, when we try to use the MDB on the new server, I get an error saying that I cannot paste a record to the table. The permission for the tables are the same as the old server.

Any ideas what might be causing this?

Another question: in your opinion, what would be the best method to copy this database to a new server?

Answer:
>The tables are updated using Microsoft Access.

Could it be that the update has the old machine name (and therefore the old server name) embedded in the update statement somehow?by a DSN or some other means?communicating the old server name to the migrated database, and so it chokes because the new server doesn’t know from the old server name?

If the database is smallish, I’d just copy the files over and do an sp_attach_db. If it is too big for that, just try and restore it.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: