A manual for importing SQL scripts to a database

Question:

How to import saved SQL scripts to an SQL Server database?

Answer:

There are two methods of saving recovered data generated by SQL Server Repair Toolbox:

  1. You can save data as SQL scripts on your hard drive and execute these scripts when needed. This will create tables and objects (indexes, procedures, functions and such), then import data to these tables.
  2. You can also run these SQL scripts using SQL Server Repair Toolbox by configuring a connection string to a particular data source (SQL Server or another database).

SQL scripts can differ regardless of the fact that they may be based on the same database files. This is related to certain syntax peculiarities in requests that are executed after establishing a server connection via ADO, as well as the execution of SQL queries in Query Analyzer shipped with Microsoft SQL Server (use of “:”, Go commands, etc.).

  1. Conversion of recovered data to SQL scripts and saving them on the hard drive
    1. Viewing of saved data

      If you chose to save data to the hard drive, the program will automatically create a subfolder that will, in turn, be created in the folder specified by the user. All the scripts will be saved there. The names of the scripts being created comply with naming rules and consist of letters and digits, where words denote the script’s role, and digits refer to its number. There are numerous script types, for example:

      • Types*.sql – user-defined data types.
      • Tables*.sql – tables.
      • Indexes*.sql – primary keys and indexes.
      • ForeignKeys*.sql – foreign keys.
      • Procedure*.sql – stored procedures.
      • Function*.sql – user-defined functions.
      • View*.sql – views.
      • Triggers*.sql – triggers.
      • Data*.sql – data tables.

      The serial numbers of scripts are used for separating data and saving it to a multitude of small documents instead of one large file. It should be noted that each new file of the “Data” type can include data from one table only.

      Attention: numbers may be missing in some files, which means that data is missing in some tables or it’s far from being complete.

    2. Script execution sequence

      You can see the recommended execution sequence in the illustration below:

      The sequence of script execution depends on existing restrictions for the current data and table structures. Please pay special attention to these factors:

      1. Specify a corresponding primary key if you want to create a foreign one. Thanks to this, these operations will be saved to various files that must be executed in this sequence.
      2. Prior to creating indexes and foreign keys, the program fills tables with data. This is required, as the referential integrity check is performed while populating the table where the foreign key is defined. When tables with foreign keys are populated to the tables with corresponding primary keys, an error occurs. Apart from this, this method has another characteristic. If the recovered data conflicts with the primary key, unique index and such, the table fill be populated anyway, but no restrictions will be created (index, primary key, ect.). If users need a different type of behavior, they can choose a specific script execution sequence.
      3. Creation of such objects as Procedures, Functions, Views, Triggers in the database has its peculiarities, since these objects may have very complex and intertwined dependencies. Procedure can refer to other Procedure objects, View can refer to Function, and Trigger can refer to all types of objects. Therefore, the program will not help untangle this knot of dependencies. Users need to manually identify all dependencies and launch SQL in the right sequence. Another possibility is to launch all the scripts of the group several times until there are no more error messages.
    3. The Install.bat file

      The install.bat file is created to ensure automatic script execution. This file launches all the scripts that were previously saved by this program. The script execution sequence will be the same as when scripts are executed on a database. This batch file requires the isqlw tool to be installed, which is included in the client part of MS SQL Server. Please specify such parameters as Server name, Database name, User name, Password in the command line. For example: install.bat SQLServer SQLDatabase SQLUser SQLUserPassword.

      Note:
      1. Parameters should be specified through spaces. If one of the parameters contains a space, you need to specify it in double quotes.
      2. Can’t swap parameters.

      If the install.bat file is started without parameters, you will see a small instruction on launching this file.

    4. InstallTrusted.bat

      If you authorize in Microsoft SQL Server via a Windows account, use the InstallTrusted.bat file. InstallTrusted.bat has 2 parameters: Server name, Database name.

      Example: installtrusted.bat SQLServer SQLDatabase Detailed information about authorization via a Windows account can be found here: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?redirectedfrom=MSDN&view=sql-server-ver15