A part of DBA’s and developer’s role is to migrate SQL Server databases between hosts or migrate from a local development environment to a live hosted environment. It requires copying all database objects such as database schema, tables, stored procedures, functions, indexes, etc. For this, different methods can be used depending on a requirement such as a database backup restore, object SQL scripts, BACPAC files, etc.
Sometimes a .bak cannot be used due to differing versions. In a situation like this, the following needs to be performed: script the database schema and data and then run that script against the destination database.
Any DBA or developer spends much of a working day staring at SQL code and very often you have been asked to review someone else’s code, or perhaps do some performance tuning on a set of stored procedures. To make things worse, reviewing someone else’s code that is not formatted by your or company’s reference can slow your progress even worse if each SQL scripts you need to reformat manually.
In this article, we will show how to script SQL Server databases, format SQL script code to compline with your or company’s formatting standard, and deploy that formatted SQL scripts on multiple SQL Servers by using tools from ApexSQL Fundamentals Toolkit for SQL Server pack.
ApexSQL Fundamentals Toolkit for SQL Server pack consists of the following tools:
- ApexSQL Analyze
- ApexSQL Complete
- ApexSQL Decrypt
- ApexSQL Propagate
- ApexSQL Refactor
- ApexSQL Script
- ApexSQL Search
SQL scripting
ApexSQL Script is a migration tool for SQL Server databases. This tool can create SQL scripts by converting SQL database objects and data into single or multiple deployment scripts. Besides this, ApexSQL Script can create .NET solutions, or executable installers, too. With ApexSQL Script you can script only particular objects from a single or multiple SQL databases at once.
Start ApexSQL Script and open a new project window. Choose a SQL Server and the type of authentication with user credential and click the Connect button. From the list, select one or more databases, and click the Load button:
In the Main grid, select SQL objects that want to be scripted. To script the structure of the objects without data, use the Structure view:
Furthermore, to include data in the scripts, use the Data view:
In our example, only the structure of all objects in a chosen database will be scripted.
- Note: ApexSQL Script offers a possibility to script specific SQL objects only. See the How to create DDL and DML SQL scripts for specific SQL database objects only and the How to narrow the data sub-set for object scripting articles
Once the SQL objects under the Structure view are selected, click the Script button:
The Script wizard will be initialed on which the scripting mode and output type can be chosen.
In the first window of the Script wizard, the scripting mode needs to be chosen:
- Structure – script structure of the selected objects (DDL scripting)
- Data – script data of the selected objects (DML scripting)
- Structure and data – combinate of the Structure (DDL) and Data (DML) scripting mode
As mentioned earlier, we are interested in DDL scripting only, so in this step, the Structure scripting mode will be selected. Next, on the Output type window, select the Script folder option:
- SQL script – will put all SQL objects and data into a single script file per chosen database
- Script Folder – will create a folder with every object scripted in a separate SQL file. All objects will be arranged through subfolders by object type
- Source Control – will create a database folder in a source control repository, all objects will be scripted as a separate SQL file and arranged through subfolders by object type
- C# solution – the C# solution packed will be created with all scripted SQL objects
- Executable installer – all scripted objects will be a package as an executable installer
On the Dependencies window, by default, the dependent objects will be included, if there are any. Since we script the whole database, the dependencies grid, it will probably be empty:
On the Script folder options window, choose the location for the scripts and click the Create button:
Once, the scripting process finishes, the results window will appear with information about which objects are scripted:
As can be seen, under the chosen location, all objects are scripted and placed under the appropriate sub-folder:
SQL formatting
As it is mentioned earlier, imagine that you get a task to review all the above created scripts in order to do some performance tuning before the scripts have been executed on multiple SQL Servers.
Now, before starting reviewing and optimizing scripts, all of them need to be formatted to apply your or company’s formatting standard.
A well-formatted SQL code is easily readable and reviewing such code can be much easier and faster than a non-formatted SQL code.
In a situation like this, ApexSQL Refactor can be very handy. ApexSQL Refactor is an add-in that integrates into SSMS and Visual Studio, has over 200 SQL query formatting options for creating SQL formatting styles and features for applying a style on one or bulk T-SQL scripts at once.
Start SSMS and from ApexSQL main menu, choose the Format SQL scripts command:
The Format SQL scripts window will appear. Here, navigate to the folder where are all script objects located and select a sub-folder which scripts want to be formatted. From the Profile drop-down box, choose your formatting profile:
If the Overwrite box is not checked, when the OK button is clicked, for each script under the selected sub-folder, the new scripts will be created and formatted, while original scripts will stay intact.
A new name for every script will be created by using a template, that is set in the File name template box of the Format SQL scripts window:
To format scripts without creating a new once, check the Overwrite box:
Once all scripts are formatted you are ready to review them.
Run multiple SQL scripts on multiple SQL Server databases
Now, when the SQL scripts are formatted and reviewed, the next step is to execute some of the scripts on multiple SQL servers. For this purpose, the ApexSQL Propagate can be used.
Run ApexSQL Propagate and from the Home tab, click the New button:
From the New project window, under the Scripts tab, click the Create new script list button:
The Script list window will appear, here click the Add button:
In the Select script file window navigate to the scripts that want to be executed and click the Open button:
All scripts will be listed in the Script list window:
Additionally, the order of scripts for execution can be set by using the Move up/down buttons:
Once the order of the scripts is set, click the Save button. In the Save scripts list as window, set the location and the name for the newly created execution script list and click the Save button:
The newly created list will be loaded in the Scripts tab:
Now, to set on which databases these scripts will be run, go to the Databases tab and click on the New database list button. The Database list window will be shown, and from here click the Add database(s) button:
Under the Add database window, click the Add server(s) button:
The Connection to SQL Server window will appear:
Add as many SQL Server as needed and check the databases on which scripts need to be executed:
When the databases are chosen, click the OK button. The list of databases will be loaded to the Databases tab:
Once the scripts and databases are set, click the Load button. The main window will be shown, and on its left side, the Script and Database panels with created lists will be displayed:
If you need some scripts to be modified before executing, select a script in the Scripts panel, and in the Script preview panel, the code will appear, where it can be edited:
Now, click the Execute button from the Home tab to execute the scripts against selected SQL databases:
The results will be displayed in the Results window:
Now, check the database to see if the objects from the scripts are created:
Conclusion
With ApexSQL Fundamentals Toolkit for SQL Server pack, you can migrate the SQL Server databases on multiple SQL servers simply and easily using features from this pack. This will save you a lot of time and keep you from the trouble that can occur during manual migration.
January 12, 2021
