Automatic Installation of SQL Server databases

During this period, the project on hand is close to the end, but the packaging problems that were not paid much attention to in the past are gradually highlighted. This not only refers to the creation of a installable project installation package, there are also information about the installation of database instances in the absence of runtime environments and the installation and recovery of database data. Here, we will share with you the experiences of packing MSDE into the installation program.

When your project is basically tested and ready for release, creating a simple automatic installation package for users will become another problem to be solved after coding, and whether the project development is complete or not, you should have an initial experience during installation.

The following describes how to add the automatic installation and merging module of Microsoft SQL Server 2000 Desktop Engine (MSDE) to the project:

 

 

1. First, you need to download the SP3 installation package of msde2000. Can be downloaded from the following link, URL: http://www.microsoft.com/ SQL /downloads/2000/sp3.asp

 

 

 

2. after the download is complete, you can install or decompress the package to the local hard disk by using a zip file. You can see the three directories MSI, MSM, and setup and other files, the figure in the MSM folder is the msde2000 merging module described in this article.

 

 

 

3. add an installation project (for example, mysetup1, path: C:/mysetup1) to your project, and add project output according to the normal steps ), select the output file (primary output) and content file (content files.

 

 

 

4. add the Merge Module (merge Moudle), select Browse, specify to your MSDE's MSM folder, and select all the files (REPL. MSM, repl_res.msm, dmo_res.msm, and DMO. (Optional.

 

 

 

5. Set the searchpath in the properties of the installation project to specify the path where your MSM is located (in this case, you need to add two: path/MSM and path/MSM/1033 ).

 

 

 

6. if you set other attributes of the installer, we will not mention it for the moment. This will allow you to compile your installation project. After the compilation is successful, you can go to C: /mysetup1/debug/See your package project mysetup. MSI.

 

 

 

7. This is a project package. Next we need to modify the installation package so that it can automatically install an instance of MSDE after installing the program (assuming the Instance name is myserver ). Now we need a Ms tool orca: http://support.microsoft.com/default.aspx? SCID = KB; en-US; 255905 after orca is installed, you can use this tool to modify our mysetup1.msi.

 

 

 

8. Use Orca to open the installation package file mysetup. MSI, find the table property, add row, and enter sqlinstancename in propetry, and enter the Instance name myserver in value. For other parameters, see: http://support.microsoft.com /? Id = 810826 and http://support.microsoft.com/default.aspx? SCID = KB; en-US; 281983

 

 

 

9. I have not succeeded in all the parameters related to the password, that is, sqlsapwd. this parameter is used with sqlsecuritymode, but I have never set it successfully and the password is always blank, somehow!

 

 

 

10. Select the table installexecutesequence, find the setpropsqlmsdeinstalled action, change 206 to 102, find removeexistingproducts, and change 1525 to 6601. Save and exit!

 

 

 

 

In this case, run your installation package on a machine without an SQL environment. After the program is installed, the myserver instance of MSDE is automatically installed, and after the machine is restarted, automatically Start an SQL server instance.

========================================================== ====================================

The previous article introduced how to pack the program to the installer together with the desktop version of SQL Server. Here I also want to know how to release the program to a computer that already has an SQL environment, osql of SQL is automatically used to restore the specified database to the date of your SQL Server.

 

 

 

First, create a temporary directory in C:/, for example, the C:/tempbd logs file. The content is as follows:

 

1. Restore. BAT file content:

Osql-e-s-I c:/tempdb/restore.txt

 

 

2. restore.txt file content:

Use master

If exists (select * From sysdevices where name = 'truckdb ')

Exec sp_dropdevice 'truckdb'

Else

Exec sp_addumpdevice 'disk', 'truckdb', 'c:/program files/Microsoft SQL Server/MSSQL/data/truckdb. MDF'

 

 

 

Restore database truckdb

From disk = 'C:/tempdb/truckdb'

With replace

 

 

 

 

 

 

 

Next, add an installer class to your project: select the project master project and add the installer class. The name is assumed to be installer1. Select the code page of instller1 and add the following code:

 

 

 

Public overrides sub install (byval statesaver as system. Collections. idictionary)

 

 

'Override the install method

 

 

 

Dim file as system. Io. File

 

 

If file. exists ("C:/program files/Microsoft SQL Server/MSSQL/data/truckdb_data.mdf") = true then exit sub

 

 

Mybase. Install (statesaver)

 

 

 

 

 

Dim checkeddir as system. Io. Directory

 

 

 

 

 

If checkeddir. exists ("C:/program files/Microsoft SQL Server/MSSQL/Data") = false then

 

 

Checkeddir. createdirectory ("C:/program files/Microsoft SQL Server/MSSQL/Data ")

 

 

End if

 

 

 

 

 

Dim fullpath as string

 

 

Dim ASM as system. reflection. Assembly = system. reflection. Assembly. getexecutingassembly ()

 

 

Dim strconfigloc as string

 

 

Strconfigloc = ASM. Location

 

 

 

 

 

 

Dim strtemp as string

 

 

Strtemp = strconfigloc

 

 

'Extract the installation path

 

 

Strtemp = strtemp. Remove (strtemp. lastindexof ("/"), Len (strtemp)-strtemp. lastindexof ("/"))

 

 

 

 

 

'Copy datebase to computer.

 

 

If creatdir (strtemp) = false then

 

 

'Installation failed.

 

 

Me. Uninstall (statesaver)

 

 

Exit sub

 

 

Else

 

 

 

 

 

End if

 

 

 

 

 

 

 

 

 

 

 

If installdb (strtemp) = false then

 

 

'Installation failed.

 

 

Me. Uninstall (statesaver)

 

 

Exit sub

 

 

Else

 

 

 

 

 

End if

 

 

'Delete temporary database files

 

 

Deletedir ("C:/tempdb ")

 

 

Deletedir (strtemp "/tempdb ")

 

 

 

 

 

 

 

 

End sub

 

 

 

 

 

Public overrides sub uninstall (byval statesaver as system. Collections. idictionary)

 

 

'Perform reverse installation

 

 

'Use reflection to extract the installation path

 

 

Mybase. Uninstall (statesaver)

 

 

Dim ASM as system. reflection. Assembly = system. reflection. Assembly. getexecutingassembly ()

 

 

Dim strconfigloc as string

 

 

Strconfigloc = ASM. Location

 

 

 

 

 

Dim strtemp as string

 

 

Strtemp = strconfigloc

 

 

Strtemp = strtemp. Remove (strtemp. lastindexof ("/"), Len (strtemp)-strtemp. lastindexof ("/")

 

 

'Delete database files and temporary files

 

 

Deletedir (strtemp "/tempdb ")

 

 

Deletedir ("C:/tempdb ")

 

 

End sub

 

 

 

 

 

Private function deletedir (byval path as string) as Boolean

 

 

'Delete the specified folder

 

 

Dim dir as system. Io. Directory

 

 

If dir. exists (PATH) = true then dir. Delete (path, true)

 

 

End Function

 

 

 

 

 

Private function creatdir (byval path as string) as Boolean

 

 

'Create a specified folder

 

 

Dim files as system. Io. File

 

 

Dim dirs as system. Io. Directory

 

 

Try

 

 

If dirs. exists ("C:/tempdb") = false then dirs. createdirectory ("C:/tempdb ")

 

 

'Copy creat dB files

 

 

Copyfile (path "/tempdb", "C:/tempdb ")

 

 

 

 

 

Return true

 

 

Catch

 

 

Return false

 

 

End try

 

 

 

 

 

End Function

 

 

 

 

 

Private sub copyfile (byval sourcedirname as string, byval destdirname as string)

 

 

'Copy all files in the specified folder to the target folder (single-layer ).

 

 

Dim dir as system. Io. Directory

 

 

Dim file as system. Io. File

 

 

Dim Spath, opath as string

 

 

Dim I as integer

 

 

For I = 0 to Dir. getfiles (sourcedirname). Length-1

 

 

Spath = dir. getfiles (sourcedirname). getvalue (I). tostring

 

 

Opath = Microsoft. VisualBasic. Right (Spath, Len (Spath)-len (sourcedirname ))

 

 

File. Copy (Spath, destdirname opath, true)

 

 

Next

 

 

End sub

 

 

 

 

 

Private function installdb (byval path as string) as Boolean

 

 

'Install the database and call automatic batch processing.

 

 

'Dim checkeddir as system. Io. Directory

 

 

'If checkeddir. exists ("C:/program files/Microsoft SQL Server/MSSQL/data") = false then

 

 

'Checkeddir. createdirectory ("C:/program files/Microsoft SQL Server/MSSQL/Data ")

 

 

'End if

 

 

Try

 

 

Shell ("C:/tempdb/restore. bat", appwinstyle. Hide, true)

 

 

Catch

 

 

End try

 

 

 

 

 

End Function

 

 

 

 

 

 

Then, add an installation project named mysetup1 to your project, add the project output according to the normal steps, and select the output file (primary output) and content files, and then add the folder to application folder. The folder name is tempdb, and then add files to the folder tempdb: osql.exe,restore.bat,restore.txt and truckdb (database files ). Set the alwayscreate of properties in your folder to true. Compile your setup project.

 

 

 

The generated installation package will automatically call the installer class method after the program is installed to restore your truckdb database.

 

 

 

Note: When generating truckdb, it should be backed up and saved to "C:/program files/Microsoft SQL Server/MSSQL/data/" for easy recovery.