Automatically install SQL Server database

server| Data | database | Automatic installation During this time, the project at hand is nearing closure, and the project packaging problem, which has been less noticed, has been slowly highlighted, not only to create a project installation package that can be installed, but also to install and restore the database data for a database instance that is missing from the running environment. Here is the first about MSDE packaged into the installer's experience to share, then we talk less.

When your project is basically finished testing, ready to release, the production of a user to use the Simple automatic installation package becomes the code after the completion of another problem to be solved, the project development is complete, from the installation, the user should have an initial experience.

Here's a brief description of how to add Microsoft SQL Server Desktop Engine (MSDE) automatic installation merge module MSM in your project:



1. First you need to download the MSDE2000 SP3 installation package. You can download it from the links below, url:http://www.microsoft.com/sql/downloads/2000/sp3.asp




2. Download complete, you can install or zip to the local hard drive, will be able to see: Msi, MSM, setup these 3 directories and setup files, including the MSM folder in the east is this article to introduce the MSDE2000 merge module.




3. Add an installation project to your project (assuming the MySetup1, path is C:\MySetup1), add the project output in the normal steps (project output), select the output file (primary output), and the content file files) two items.




4. Add the merge module (merge Moudle), choose Browse, assign to your MSDE MSM folder, select all the files under MSM and msm\1033 (REPL.MSM, Repl_res. MSM, Dmo_res. MSM and DMO.MSM can not be selected, open.




5. Set the SearchPath in the properties of the installation project, specifying the path of your MSM (two additional: Path\msm and path\msm\1033).




6. Set up additional properties about the installer let us not mention this, it is possible to build your installation works, when the compiler passed, you can see in the C:\MySetup1\Debug\ your packaging project Mysetup.msi.




7. This is the end of the project package, we need to modify the installation package so that it can automatically install an instance of MSDE after the program is installed (assuming the instance name is: MyServer). Now we need to use a MS tool orca, download address: http://support.microsoft.com/default.aspx?scid=kb; en-us;255905 installed Orca, you can use this tool to modify our Mysetup1.msi.




8. Open the installation package file with Orca Mysetup.msi, find the property this Table,add Row in the Propetry fill in Sqlinstancename,value fill in the instance name MyServer. For other parameters, see: http://support.microsoft.com/?id=810826 and http://support.microsoft.com/default.aspx?scid=kb;en-us; 281983




9. All parameters related to the properties of the password, I did not succeed, is SQLSAPWD, this parameter is used with Sqlsecuritymode, but I have not set the success, the password is always empty, somehow!




10. Select InstallExecuteSequence This table, find setpropsqlmsdeinstalled This action, modify 206 to 102; Find Removeexistingproducts, Modify 1525 to 6601. Save, quit!





At this point, run your installation package on a machine that does not have a SQL environment, and after the program is installed, automatically install the MyServer instance of MSDE and automatically start the instance of SQL Server after the machine is restarted.


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

The previous article describes how to package with the desktop version of SQL Server to install the simple steps of the installer, and here you also want to: publish the program to a computer that already has a SQL environment, automatically use SQL osql to restore the specified database to your SQL Server date.




First, create a temporary directory in C:\, such as C:\TempBD, copy Osql.exe to the directory, copy your database backup (TRUCKDB) to the directory, and create Restore.bat and Restore.txt files separately under the directory, which reads 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








Second, add a installer class to your project: Select the Project master project, add Installer class, and the name is assumed to be installer1. Select the code page for the Instller1 and add the following code:




Public Overrides Sub Install (ByVal statesaver as System.Collections.IDictionary)



' Rewrite 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 Installation path



strtemp = Strtemp.remove (Strtemp.lastindexof ("\"), Len (strtemp)-Strtemp.lastindexof ("\"))







' Copy datebase to computer.



If Creatdir (strtemp) = False Then



' Failed, anti-installation



Me.uninstall (statesaver)



Exit Sub



Else







End If













If installdb (strtemp) = False Then



' Failed, anti-installation



Me.uninstall (statesaver)



Exit Sub



Else







End If



' Delete database temporary files



Deletedir ("C:\TempDB")



Deletedir (strtemp + "\tempdb")










End Sub






Public Overrides Sub Uninstall (ByVal statesaver as System.Collections.Idictionary)



' Perform an anti-installation



' Extract installation path with Reflection



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 the 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 of the specified folder to the destination 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 invoke the automatic batch process.



' 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 to your project, named MySetup1, add the project output in the normal steps (project output), select the output file (primary output) and the content file (contents files) Two, Add folders to application folder, the folder name is tempdb, and then add files to the folder tempdb: OSQL.EXE,RESTORE.BAT,RESTORE.TXT,TRUCKDB (Database file). Set the properties of your folder to AlwaysCreate true. Compile your setup project.




At this point, the resulting installation package will automatically invoke the Installer class method after installing the program, and restore your TRUCKDB database.




Note that when TRUCKDB is generated, it should be backed up and saved to the "C:\Program Files\Microsoft SQL Server\mssql\data\" for easy recovery.