Q:How to import the saved SQL scripts into database?
There are two ways to save data, supported by SQL Server Recovery Kit:
- You can save SQL scripts to hard drive, they will create database objects, tables, for example, and insert data to these tables.
- You can also launch scripts in databases, defined by users, these scripts are written in SQL language.
Please note, that SQL scripts can be different, in spite of the fact, that they base on the same files of database. It happens because of syntax particularities both in queries, that are executed for direct server connection by means of ADO and in SQL queries, performed in the Query Analyzer environment that is supplied with MS SQL Server (the use of ":", the Go command, etc.). The first way is more reliable, what about the second way, it is more convenient.
Converting data to scripts and save it to disk
1. Saved data mapping
If you've chosen to save data on disk, SQL Server Recovery Kit will create a subdirectory, that includes name of the source MDF file, this subdirectory is created in the user-defined directory and all scripts will be placed there. All scripts are named by rule, names consist of a word and a digit. Word indicates the script's role, digit indicates its number. There are many script types, for example:
- Types*.sql - scripts will create data types, defined by users.
- Tables*.sql - scripts will create tables.
- Indexes*.sql - scripts will create primary keys and indexes.
- ForeignKeys*.sql - scripts will create foreign keys.
- Procedure*.sql - scripts will create stored procedures.
- Function*.sql - scripts will create functions, defined by users.
- View*.sql - scripts will create views.
- Triggers*.sql - scripts will create triggers.
- Data*.sql - scripts will insert data into tables.
Script sequence number does not contain any useful data, it does not point at script execution sequence or any other information. These numbers are used only to split data and save it to a variety of small documents instead of one large file. Users can define the maximum size of file with SQL script. Moreover, users should take notice of the numbering of Data files. It should be noted, that each file of the Data type can contain data for one table only. Files with sequence numbers contain all data for each table.
Note: If numbers are missing for some files, it means that some tables do not contain any data.
2. Script execution sequence
In this figure, you can see the recommended order of script execution:
Script execution sequence depends on existing restrictions for existing data and structures of the table. Please pay particular attention to these factors:
- Please specify corresponding primary key, if you'd like to create a secondary key. Owing to this fact, these operations are saved to different files, which should be launched in this sequence.
- Before the creation of indexes and secondary keys, the program fills tables. It is obligatory, because referential integrity check is performed, when a table with defined secondary key is being filled. When tables with secondary keys are filled before tables with corresponding primary keys, there's an error. Moreover, this method features another particularity. If recovered data conflicts with the primary key, unique indexes, etc., in this case, the table will be filled with data anyway, but restrictions will not be created (index, primary key, etc.). If users require another behavior, they can select script execution sequence themselves.
- Procedures, Functions, Views and Triggers object creation in a database has some difficulties, because these objects may have sophisticated and tricky dependencies. Procedures may refer to other Procedures, Views may refer to Functions, Triggers may refer to all objects. That is why, a program cannot help to solve the tangle. Users should find all dependencies manually and launch SQL scripts in the needed sequence. Another possibility consists in executing of all scripts in this group several times, until all error messages will disappear.
3. Install.bat file
Install.bat file is created to ensure automated execution for all scripts. This file executes all scripts, that were previously saved by the tool. Script execution sequence will be the same, as when applying to database. This batch file requires the isqlw utility to be installed, it is a part of MS SQL Server client part. Please specify Server name, Database name, Username and Password parameters for the file install.bat in the command line. For example: install.bat SQLServer SQLDatabase SQLUser SQLUserPassword
- Parameters should be separated with a space. If a parameter contains space itself, you should enclose it with double quotation marks.
- Parameter sequence cannot be modified.
- Please import data into NEW EMPTY database. Database must not contain any tables, indexes, stored procedures and so on before data import.
- If install.bat file will be executed without parameters, you can see a brief guide, how to launch this file.
If you want to log in to Microsoft SQL Server via your Windows account, then use InstallTrusted.bat file. InstallTrusted.bat file has two parameters: Server name, Database name.
Example of use: installtrusted.bat SQLServer SQLDatabase For details on logging in via Windows account see here: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-2017