FAQ - Frequently Asked Question list - Answer
Question: How to import saved scripts into MS SQL Server database?
Import saved scripts into database
There are two methods of saving data implemented in SQL Server Recovery Toolbox:
- Saving SQL scripts to the disk, these scripts create tables and other database objects and fill tables with data.
- Executing scripts in the SQL language in the user-defined database.
It should be noted that scripts in the SQL language may differ though they are based on the same database files. It is due to the peculiarities of syntax either in queries executed during the direct connection to the server via ADO or in SQL queries executed in the environment of Query Analyzer that comes together with MS SQL Server (the Go command, the use of :, etc.). The first method is more reliable, although the second method is more flexible and much more convenient.
1. Saving data to the disk as scripts
1.1. Data representation when it is saved to the disk
If you save data to the disk, a subdirectory with the name of the source MDF file will be created in the user-defined directory. Scripts will be saved to this directory. The name of each script consists of a word and a digit. The word indicates what the script does and the digit is its number. Scripts can be of the following types:
- Types*.sql - these scripts create user-defined data types.
- Tables*.sql - these scripts create tables.
- Indexes*.sql - these scripts create indexes and primary keys.
- ForeignKeys*.sql - these scripts create foreign keys.
- Procedure*.sql - these scripts create stored procedures.
- Function*.sql - these scripts create user-defined functions.
- View*.sql - these scripts create views.
- Triggers*.sql - these scripts create triggers.
- Data*.sql - these scripts fill the table with data.
The number of a script does not convey any valuable information, it does not mean the order the scripts are executed in. It is necessary only to split data and save them to a number of small files instead of one big file. The maximum size of a file with an SQL script is specified by the user. Besides, we should pay particular attention to the numbering of Data files. First of all, data only for one table can be saved to each file of the Data type. All data for each of the tables is saved to files with sequential numbers.
Attention: Files with missing numbers mean that there is no data in some tables.
1.2. Order of executing scripts
The recommended order of executing scripts is the following:
The order scripts are executed in is based on the peculiarities of imposing constraints on existing structures and data in a table. In particular, the following issues should be taken into account:
- To create a foreign key, you should specify the primary key corresponding to it. That is why these two operations are saved to different files and you should run them in this very order.
- Tables are filled before indexes and foreign keys are created. It is necessary because the referential integrity is checked when a table where a foreign key is defined is being filled. And an error will occur if a table with a foreign key is filled before the table with the corresponding primary key. Besides, this approach has one more peculiarity. If the recovered data does not match unique indexes, a primary key, etc., the table will be filled with data anyway, but no constraint will be created in this case (an index, a primary key, etc.). If some other behavior is required, the user himself can select the order scripts are executed in.
- Creating such objects as Procedures, Functions, Views, Triggers in a database has some difficulties because these objects may have complicated and intricate dependencies. For example, a Procedure may refer to another Procedure, a View may refer to a Function and a Trigger may refer to all objects at once. That is why it is impossible to get this tangle straight with the help of the program. The user should manually find the dependencies and execute SQL scripts in the necessary order. Or the user can execute all scripts in this group several times until no error.
1.3. Install.bat file
The program creates the file install.bat on the disk for the automatic execution of all scripts. This file will execute all scripts saved by the program. The order the scripts are executed in will be similar to the order of executing scripts on the database. For this batch file to run, the isqlw tool included in the client part of MS SQL Server must be installed on the computer. You should specify the following parameters in the command line for the file install.bat to run: "Server name", "Database name", "Username", "Password". For example: install.bat SQLServer SQLDatabase SQLUser SQLUserPassword
- The parameters are separated with a space. If some parameter contains a space, it should be enclosed in double quotation marks.
- You cannot change the order of the parameters.
- If password is blank then please set a double quotes: "".
If you run the batch file install.bat without any parameters, a small instruction how to run this batch file will be shown.