Generate database script with structure and data

Question

Backup is not possible to do, because it depends on permission from France and etc. So, the solution we found here was to try to generate a script that copies not only the structure of the tables, I already do and did, but like an INSERT loading all the data for this script. Is that possible? I tried using Sql Server DTS, but it only copies structure.

Answer 1

A good solution to generate the structure of a database and import the data is to use the data itself Microsoft SQL Server Management Studio, he gives all the support for this.

1. First let’s generate the script of the entire base structure.

Open the Microsoft SQL Server Management Studio, Connect to your base and right-click on it

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

Click on next.

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

Finally, just choose where you want to save the script. Normally I saved in a new SQL window.

After that just create your database with the generated script.

2. Now let’s import the data

Before starting this step it is good to disable all constraints of the database, so there are no 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 activate again.

To disable the Constraints just run the script below:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' 

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

Click "Next", after this goes to a screen for you to parameterize the data from the data source that you will import the information.

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

Now you go to the parameter setting screen of the target database, its parameterization and equal to the previous one, only in the case with the data of the target database, which in your case is the database created through your script.

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

Select All Table and click "Edit Maps".

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

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

After imported data rehabilitates all constraints:

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'