Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    2

    Unanswered: Transfer SQL Server Database

    I am trying to transfer one SQL database to another. I decided to test internally first and I can't get this to work.

    If I am logged in as sa, I can use the import wizard in SQL Server 7 to successfully transfer any database to another database. However, if I create a new database called testdb and then create a user called testuser and make testuser the dbo of testdb and then try to use the import wizard to import data from another database and select to "Transfer objects and data between SQL Server 70 databases" that both exist on the same machine (using sa to connect to source database and testuser to connect to destination databasea), I get errors in SQL saying the following:
    Window Title: "Transfer SQL Server Objects"
    Error Message: "[Microsoft][ODBC SQL Server Driver][SQL Server] Only members of the sysadmin role or the database owner may set database options."

    I don't know what database options are trying to be set or why I would get this error since I am logged in as dbo for the testdb destination database and sa for the source database.

    I have tried this same procedure with the following selections turned on and off, but I get the same error either way.
    "Transfer database users and database roles", "Transfer SQL Server Logins", "Transfer Object-Level Permissions"

    Basically, what I am I doing wrong? Is there a better way to do this? I am trying to test this process internally so that I will be able to use the same set of steps eventually to transfer a database developed internally to a hosting company.

    Thanks,
    LaDonna

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Try running the profiler to see what it is trying to do.

  3. #3
    Join Date
    Feb 2002
    Posts
    9
    I would backup then restore..

  4. #4
    Join Date
    Feb 2002
    Posts
    2
    how do you backup and restore to a new machine and keep user permissions intact (if you are dbo for the selected database but not sa)?

Posting Permissions

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