Installing SQLExpress and Db restore using InnoSetup

[Bernd Maierhofer]

Is anybody using InnoSetup for installing and deploying a MSSQL Express
database? I used SQLDMO but would like to update this. Additionally we have
sometimes issues with win7.

Has anybody a working script for installing all prerequisites, detecting an
existing server, attching/restoring a database and is willing to share?

Below is part of my script, maybe someone can make use of it.

brgds Bernd

[CODE]
...
procedure CreateDatabase;
var
SQLServerName: string;
DBName: string;
UserName, Password: string;
SQLServer, DB: variant;
sSql,s: string;
begin
ProgressPage.SetText('Anbinden der Datenbank', 'Verbinden zum Server
...');
ProgressPage.SetProgress(0, 0);
ProgressPage.Show;
try
ProgressPage.SetProgress(1, 10);
// Daten holen
SQLServerName := GetData('Server');
DBName := GetData('DB');
Username := GetData('User');
Password := GetData('Password');

{ Create the main SQLDMO COM Automation object }
ProgressPage.SetProgress(3, 10);

try
SQLServer := CreateOleObject('SQLDMO.SQLServer');
except
MsgBox('Microsoft SQL server Connectivity Tools sind nicht
installiert.'#13#10 +
'Installieren Sie entweder MS-SQL-Server wenn Sie nut.s lokal
benutzen oder '#13#10 +
'die Connectivity Tools (=native client).'#13#10'(' +
GetExceptionMessage +
')', mbInformation, MB_OK);
ProgressPage.Hide;
exit;
end;

{ Connect to the Microsoft SQL Server }
try
SQLServer.LoginSecure := True;
SQLServer.Connect(SQLServerName);
except
MsgBox('SQLServer ' + SQLServerName + ' wurde nicht gefunden.'#13#10
+
'(' + GetExceptionMessage + ')', mbInformation, MB_OK);
ProgressPage.Hide;
exit;
end;
ProgressPage.SetProgress(4, 10);
ProgressPage.SetText('Anbinden der Datenbank', 'Beginne ...');
try
if WizardSetupType(False) = 'local' then
begin
s := ExpandConstant('{app}') + '\Database\';
DB := CreateOleObject('SQLDMO.Database');
DB := SQLServer.Databases('master');
sSql := 'RESTORE DATABASE [nuts] FROM ' + 'DISK = N''' +
s + 'nuts_deploy.bak'' WITH FILE = 1, ' +
'MOVE N''nuts_data'' TO N''' + s + 'nuts.mdf'', ' +
'MOVE N''nuts_log'' TO N''' + s + 'nuts.ldf'', NOUNLOAD,
REPLACE, STATS = 10 ';
DB.ExecuteImmediate(ssql);
end;
except
MsgBox('Restore der Datenbank ' + DBName + ' schlug fehl.'#13#10 + s
+#13#10'(' + GetExceptionMessage + ')', mbInformation, MB_OK);
ProgressPage.Hide;
exit;
end;
ProgressPage.SetText('Anbinden der Datenbank', 'Fertig. Nächste
Schritte ...');

ProgressPage.SetProgress(6, 10);

try
s := 'USE [master]; CREATE LOGIN [' + UserName + '] WITH PASSWORD =
''' +
Password + ''', DEFAULT_DATABASE = [' + DBName + ']; ' +
' USE [' + DBName + ']; CREATE USER [' + UserName + '] FOR LOGIN
[' + UserName + '] ;' +
' EXEC sys.sp_addrolemember @rolename = N''db_owner'', @membername
= N''' +
UserName + '''; ';
DB.ExecuteImmediate(s);
except
// sollte der user schon da sein --> ignorieren
end;
try
s := 'use ' + DBName + '; exec sp_change_users_login ''Auto_Fix'',
''' + UserName + '''; ' +
'exec sp_changedbowner ''' + UserName + ''';';
DB.ExecuteImmediate(s);
except
// sollte der user schon da sein --> ignorieren
end;
ProgressPage.SetProgress(10, 10);
finally
ProgressPage.Hide;
end;
end;