Migrate full database schema from one server to another server

Nirvesh Kumar 1 Reputation point
2021-07-13T18:21:11.827+00:00

Is there any way to migrate the whole database schema from one server to another server like View, function, sp, trigger, users, tables, etc?

I tried the option to migrate the DB schema using generate script (Instance->DB-> Task-> Generate Script -> used both options entire database and specific table, sp, view, etc.)

After migration, I have compared the objects (View, function, sp, trigger, users, tables, etc). I found some missing objects (View, function, sp, trigger, users, tables, etc) which were dependent on other user databases objects.

Also, I found some missing database system objects(View, Store procedure, tables, etc).

Can you help me, why system objects are missing (View, Store procedure, tables, etc) on user databases?

How can I migrate the system objects (View, Store procedure, tables, etc) in the same user database system folder?

Many Thanks!!
Nirvesh

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,039 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,611 Reputation points
    2021-07-13T19:21:13.627+00:00

    The simplest thing is to use SSDT to import the existing database into scripts. Then you can deploy the scripts to the new database.

  2. Erland Sommarskog 72,226 Reputation points MVP
    2021-07-13T21:44:01.577+00:00

    Beware that when you script a database, not all objects are included by default. When you arrive at the screen "Set scripting options", click Advanced to set options.

    Those options only control the type of objects. For instance triggers are not scripted by default as I recall. If you did not get all stored procedures, views it may be that you did not select all objects. (There is an option to include all dependent objects.)

    Also, I found some missing database system objects(View, Store procedure, tables, etc).

    Not sure what you mean here. These are never scripted, nor are they copied, but each database has its own set. If some appear to be missing, could it be that that you are migrating to an earlier version of SQL Server?

  3. AmeliaGu-MSFT 13,891 Reputation points Microsoft Vendor
    2021-07-14T03:11:55.357+00:00

    Hi NirveshKumar-3606,

    In addition, you can try to use Data Migration Assistant (DMA) tool to help you move your schema, data, and uncontained objects from your source server to your target server. Please refer to Data Migration Assistant.

    Or you can export a deployed data-tier application (DAC) that includes both the definitions of the objects in the database and all of the data contained in the tables, then import it to another server. But you cannot export a database that has objects that are not supported in a DAC, or contained users. Please refer to Export a Data-tier Application and DAC Support For SQL Server Objects and Versions which might help.

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.