Databases in SQL Server are transplanted to each other ., Port sqlserver

Databases in SQL Server are transplanted to each other ., Port sqlserver

When sorting out BS template materials a few days ago, Shi Ge asked us to publish several websites on the server. I was responsible for the background release of Yonghe cash register system. This system has its own characteristics, before loading the login page, you have to query information in the database. If there is no information in the database, the entire program cannot run, and you do not understand the requirements and business logic of the system, we had to get the database to the server for publishing. At this time, the problem arises. In the database on the server, the SQLServer2008 R2 version is used. I use SQLServer2012. The isolated database cannot be backward compatible, And I port the data in the database one by one, what about this swollen wooden office?

I found some information about database migration on the Internet.

1. Back up the database and restore it on the target machine.

The bak format is used for backing up databases. This method works between SQL server of the same version. However, after I have backed up SQL Server, it cannot be restored to SQL Server R2.

2. Separate the attached database.

This method separates the target database from the object Resource Manager of SQLServer, copies it to another machine, and appends the database. This method also works between the same versions, or the version difference is not big, but it still doesn't work for me.

3. generate an SQL script

This is the last method in my experiment. This method is to generate an SQL script (with data in the database attached) and then execute it on the target machine, because you can select the Script for Server Version option when generating an SQL Script, here we make up for the data incompatibility problem caused by different SQLServer versions. The following are the detailed steps.

Right-click the database to generate the script -- task -- generate the script, as shown in the following figure.

Here, you can edit the generated path and click Advanced options.

Select the version of the exported SQL script, as well as the Architecture and Data to be exported.

Then, according to the prompts, the next step and next step will be successful.

Click "OK". Execute the exported SQL script on the target machine, create a database, then create a table, and insert data. This is the final result. In fact, there are still many ways to export data. I just introduced one of them. SQLServer is really powerful and we need to explore every function of SQLServer.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.