Generate bank script with structure and data

A good solution for building a database structure and importing the data is to use Microsoft SQL Server Management Studio itself, it gives full support for this.

Open Microsoft SQL Server Management Studio , connect to your database and right click on it

Then go to Tasks - > Generate Script, according to the photo above, if it is in Portuguese.

Click next.

Then choose what you want to export, all or just some specific things, in my case only have tables created, normally do not recommend recreating the permissions of users, since not always they are on the new server and this will generate error .

At the end, just choose where you want to save the script. I usually save in a new SQL window.

After this, just create your database with the generated script.

Before starting this step it is good to disable all the constraints of the database, so it does not give problems when importing the data, because it does not check the dependencies of FK before, to know which table to import first, and this can give problems , then just re-enable.

To disable Constraints, just run the script below:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' 

Right click on the database you created and then "import" data.

Click "next", after that it will go to a screen for you to parameterize the data of the data source that you will import the information.

In your case, select Sql Server Native Client , which in this case is a connection to a SQL Server database. On behalf of the server, pass the ip / dns of the server, or what you use to connect to it. Fill in the authentication data and finally choose the database you want to pull the information.

Now you go to the parameterization screen of the destination database, the parameterization of it and the same as the previous, only in the case with the data of the destination database, which in your case is the database created through of your script.

Choose "Copy data from one or more tables or views" and "move forward."

Select all the tables and click on "edit mappings".

Leave the option "Enable identity insertion", this option will keep the identity of the old bank when you call the data, after that click "OK"

With all the tables you want to migrate the selected information click on "next" and then "finish"

After importing the data, re-enable all constraints:

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'