locked
combining multiple legacy SQL Server 2000 databases into one when migrating them to 2008: possible using restore utility? RRS feed

  • Question

  • We have multiple legacy databases in SQL Server 2000.  There are inter-database pseudo foreign-keys  (e.g. CUSTOMERS.zipcode and SUPPLIERS.zipcode in the SALES database validates against ZIPS.zip5 column in the ZIPMASTER database).  N.B. These are not real declarative foreign keys but procedurally enforced relationships.

    What we would like to do, when migrating these databases to SQL Server 2008, is to combine them into one database, so that declarative referential integrity would become possible where in the past we had to use stored procs, and yet we want to keep the database objects logically distinct by having separate schemas claim ownership of them.

    Thus, under SQL Server 2000 we had a SALES database, but in SQL Server 2008 we would have a SALES schema. 

    In SQL Server 2000 we had a ZIPMASTER database, but in SQL Server 2008 we would have a ZIPMASTER schema.

    Is there any way to restore a SQL Server database into an arbitrary SQL Server 2008 database and specify which schema should claim ownership of the objects being restored?

    restore   from SALES backup file into   FOO with SALESCHEMA claiming ownership of the restored objects

    restore from ZIPMASTER backup file into FOO (again) with ZIPMASTERSCHEMA claiming ownership of the restored objects

    So that if there are objects with the same name in SALES and ZIPMASTER in SQL Server 2000, they could nonetheless be imported into the same database in SQL Server 2008 because the schema prefix "fully qualifies" the objects so they would remain distinct and not clobber each other.

    I hope my question is clear. Please advise if not.

    Thanks






    Tuesday, June 12, 2012 4:17 PM

Answers

All replies

  • Hi lloydmalvern,

    Thank you for your question. 

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Thanks,
    Maggie

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Friday, June 15, 2012 8:37 AM
  •  Hello ,
     
     Try to use Import and Export wizard and choose the schema / destination tables. 
      
     Reference article: - Run the SQL Server Import and Export Wizard http://technet.microsoft.com/en-us/library/ms140052(v=sql.110).aspx
      
     Thank You
      Jayaprakash JO - MSFT

    • Marked as answer by Maggie Luo Sunday, July 8, 2012 5:17 PM
    Wednesday, June 27, 2012 9:28 PM