The subject of our article is how to move the database to the sub-versions of SQL Server and we will talk about a few important points. Generate Script will move and we will talk about important points. In fact, the process runs smoothly, but if the Database size exceeds a certain level, the Database memory may fail during this process.
Now we will move to a lower SQL version of a Database that runs in SQL 2017 as the Lab environment.
Let’s look at our SQL Server 2017 database.
We have a database like in the picture. In this database, we have our tables full of Foreign Key s Indexes. As you know, it is actually a live database with data.
We will reduce our database to SQL 2012. And now we start the process.
Right-click on the Database, point to Task and select Generate Scripts. And welcome screen comes,
Next click and pass.
Script Entire Database and All database object options are selected in the Chose Object section.
Shall I take all the database elements, etc., to be called? That’s not the way we’re going.
Our first job is to get rid of unnecessary downloads. Of course, if you will identify unnecessary downloads. Do you need a log or do you need to go back to look at these are always a question mark?
In your own database, remove the tables that you would like to have while not importing the data. When you transfer, you re-create it.
After selecting everything except Log, we take another step by pressing the Next button.
And we’re coming to where we really are. Here we have a lot of settings, but you will see the next picture.
First, we have 2 options in the Output Type section. This says “ Save Scripts to a specific location ”.
Other option “ Publish to Web Service ” If you have a cloud service like Azure or you want to move the script you created through a web service, you will need to use this option.
We choose the first one and come to the Advanced section.
There are a lot of options available here, usually, we’ll pass by doing super next.
There are certain points that we will touch here.
Script For Server Version: Where will the Script we create work? that is, we select the target SQL server version to which we will move the database. (The current version and sub-versions are only available).
Script For Database Engine Edition: Again, according to the version of the target server is Standard, we select the standard.
Types Of Data to Script: In this section, we have 3 options;
Scheme Only: Enables you to retrieve the entire template, ie the columns of the tables, in an empty way without taking the data in the database. By default Schema only comes
Data Only: The opposite of the top of the Buddha only receives data.
Scheme And Data: The combination of both of the superiors, the pilgrim, is the option to deal me with an al-scrip.
NOTE: I think you all have Schema and Data. Our key point here is that you can get schema and data at the same time depending on the size of the database, but we will take the database we use for this test separately.
Script Foreign Key: A Foreign key is an object that is important for the relationship between our tables and we need to take this together with schema so that the table can also define the relationships.
Script Indexes: We can say that the indexes we have created in the tables are added to the script.
Script Primary Keys: Primary keys should be added to the script, which can be said as the basic parts of the structure of the table. So the default is true.
For all of these settings to the descriptions in Microsoft’s own blog can look.
We have made the above-mentioned settings as shown in the figure. We have selected Server Version 2012 as our foreign key indexes.
You can find the settings you need by looking at the details from the links above. For example, transferring your compression settings in the database in the same way.
We said ok and we moved on
I select the Save to New Query Windows option. First, I see the script and save it.
We see a summary of our settings. And we’re coming.
It’s important not to make mistakes in this part. If a table fails the store procedure, the Script is garbage.
For example, if there is an encrypted store procedure that I live in, it will fail in all Store Procedures.
If you remove only the encrypted SP, the problem will be corrected.
We have created a script of 12 thousand lines. And we are connected to our SQL 2012 target system on the left.
Direct CTRL + A CTRL + C by copying and opening a query on our 2012 server CTRL + V are doing.
Don’t tell me to press F5 right away and you’ll stop reading errors. This script requires a database to run.
Right-click the new database and open a database called LORD SITH. And now we hit f5.
We receive 1 error. It’s about not having the same linked server defined on my room target server (I haven’t set up the lab environment yet) We have also moved the entire schema of the database.
After this process, we create another script with the same steps from our 2017 server because we haven’t received our data yet.
Alternatively, we only create Data Only by selecting Data Only. (You can do that by looking at the steps from above again, I’ll just throw the import result.)
NOTE: You will need Memory depending on the size of the database.
If you throw enough memory or CPC Saver Single File option to proceed with the extension .sql file to the target server because the data discarding the SSMS also live much trouble while the trip.
The script will process line by line when it runs. It’s really troubling when you get an error here.
And we pull Select from a table.
I hope it has been a useful article.