question

NirveshKumar-3606 avatar image
0 Votes"
NirveshKumar-3606 asked AmeliaGu-msft commented

Migrate full database schema from one server to another server

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi NirveshKumar-3606,
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.