Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    6

    Unanswered: Create copy of database and place on different server

    All,
    I am trying to create a copy of an existing database and place on a different sql server for testing purposes. Currently my method of doing this is to create a backup, then restore a database on my new sql server. But here is the problem I am running into. When I look in Enterprise manager I can see all of the stored procedures and tables and data just fine, which is how I would like it. But, when I open up Query Analyzer I am not able to run any queries because it says "invalid object name" error. I know that the object name is correct. I think that I am having a conflict between the users which were carried over from the source backup file and the users that are on my 2 new sql server. If anyone could help, I would really appreciate it. I am stuck at this point. I am guessing it is probably something simple I am just unaware that I need to do.

    Thanks,
    LinsLo

  2. #2
    Join Date
    Mar 2004
    Posts
    114
    Hi,

    Maybe you should try a detach

    Code:
    EXEC sp_detach_db 'your_db'
    And then on the second server an attach:
    Code:
    EXEC sp_attach_db @dbname = N'pubs', 
       @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 
       @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

  3. #3
    Join Date
    Aug 2004
    Posts
    54
    LinsLo,

    The problem is probably being caused because the userids are not matching up in your system tables.

    First make sure the usernames for the database exist on the server then for each user run the following sql script.

    PHP Code:
    EXEC sp_change_users_login 'Update_One''<USERNAME>''<USERNAME>' 
    That will align the users in the database with the users on the server.

    Hope this helps.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess would be that you need to use sp_change_users_login.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •