I've done restores successfully this way. Profiler will help determine the
call(s) made and more details about why they failed.
Eric, assuming that you're using integrated security, are you sure that your
installer is running elevated at the point where the restore is attempted?
We've since abandoned BAK files, but here's an example component from the
original RTM AdventureWorks2008 installer from last year:
<Component Id="C_AdventureWorks2008Backup"
Feature="F_AdventureWorks2008Restore" Directory="INSTALLDIR"
Guid="{15E1D2B7-681F-41D2-8B17-A703317C359B}" Win64="$(var.PlatformIs64bit)"
KeyPath="yes">
<ReserveCost Id="RC_AdventureWorks2008Backup" RunFromSource="83886080"
RunLocal="83886080" />
<RegistryKey Root="HKLM" Key="SOFTWARE\Microsoft\Microsoft SQL
Server\100\Samples">
<RegistryValue Name="InstallAdventureWorks2008YN"
Value="[INSTALLADVENTUREWORKS2008YN]" Type="string" />
</RegistryKey>
<!--<sql:SqlString
Id="SqlString_DropAdventureWorks2008"
SqlDb="SqlDb_Master"
Sequence="5"
ExecuteOnUninstall="yes"
ContinueOnError="yes"
SQL="
USE master;
IF '[INSTALLADVENTUREWORKS2008YN]' = '1'
BEGIN
IF EXISTS (SELECT * FROM sys.databases WHERE name =
'AdventureWorks2008')
BEGIN
EXECUTE (N'ALTER DATABASE AdventureWorks2008
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;');
EXECUTE (N'DROP DATABASE AdventureWorks2008;');
END
END
" />-->
<sql:SqlString
Id="SqlString_RestoreAdventureWorks2008"
SqlDb="SqlDb_Master"
Sequence="10"
ExecuteOnInstall="yes"
ExecuteOnReinstall="yes"
ContinueOnError="no"
SQL="
IF '[INSTALLADVENTUREWORKS2008YN]' = '1'
BEGIN
USE master;
DECLARE @sql_path nvarchar(256);
SELECT @sql_path = SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files WHERE database_id = 1 AND file_id
= 1;
IF EXISTS (SELECT * FROM sys.databases WHERE name =
'AdventureWorks2008')
BEGIN
EXECUTE (N'ALTER DATABASE AdventureWorks2008
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;');
EXECUTE (N'DROP DATABASE AdventureWorks2008;');
END
EXECUTE (N'RESTORE DATABASE AdventureWorks2008
FROM DISK =
''[INSTALLDIR]Tools\Samples\AdventureWorks2008.bak''
WITH MOVE ''AdventureWorks2008_Data'' TO N''' +
@sql_path + N'AdventureWorks2008.mdf'',
MOVE ''AdventureWorks2008_Log'' TO
N''' + @sql_path + N'AdventureWorks2008.ldf'',
MOVE ''FileStreamDocuments'' TO
N''' + @sql_path + N'Documents'';');
EXECUTE (N'ALTER DATABASE AdventureWorks2008 SET NEW_BROKER;');
END
" />
</Component>
-----Original Message-----
From: Rob Mensching [mailto:[email protected]]
Sent: Thursday, January 29, 2009 09:05
To: General discussion for Windows Installer XML toolset.
Subject: Re: [WiX-users] Restoring SQL Database with Sqlstring in WiX v3
Not much that is helpful. When I've had really hard SQL issues, I've always
used the SQL profiler to dig through all the calls and find the one that is
failing.
Note: I have seen successful attaches of database files but never tried to do a
restore from backup. I always have issues restoring from backup.
-----Original Message-----
From: Eric Latendresse [mailto:[email protected]]
Sent: Thursday, January 29, 2009 08:15
To: General discussion for Windows Installer XML toolset.
Subject: [WiX-users] Restoring SQL Database with Sqlstring in WiX v3
Anyone have any suggestions on how to debug this? I have checked the SQL
server logs and there is nothing there pertaining to this WiX error.
Thank you in advance for your help with this.
This is the exact error:
Error -2147217900: failed to execute SQL string, error detail: RESTORE
DATABASE is terminating abnormally., SQL key: RestoreDB SQL string:
RESTORE DATABASE Suite FROM DISK = 'G:\Database\SuiteBlank
This is the WiX code. Creating the database works fine. the error
happens within the SQL String. Running the SQL string in SQL Query
analyzer works as well.
<Component Id="SuiteDatabaseComponent"
Guid="d6e96011-3252-4e85-80b5-b1ff64045e88">
<File Id="DatabaseBackup"
Name="SuiteBlank.bak" Source="..\..\Database\Backups\SuiteBlank.bak" />
<CreateFolder/>
<!-- installs database -->
<sql:SqlDatabase Id="db1"
Server="[SQLSERVER]"
Instance="[SQLINSTANCE]" Database="Suite"
CreateOnInstall="yes"
ConfirmOverwrite="yes"
DropOnUninstall="no" User="SQLUser">
<!-- define where the database
files are saved -->
<sql:SqlFileSpec Id="mdf"
Name="Suite_Data"
Filename="[DATABASEDIR]Suite_Data.mdf"
Size="2MB"
GrowthSize="2MB"/>
<sql:SqlLogFileSpec Id="ldf"
Name="Suite_Log"
Filename="[DATABASEDIR]Suite_Log.ldf"/>
</sql:SqlDatabase>
<sql:SqlString Id='RestoreDB'
SqlDb='db1' ContinueOnError='no' ExecuteOnInstall='yes'
SQL="RESTORE DATABASE Suite FROM DISK = '[DATABASEDIR]SuiteBlank.bak'
WITH MOVE 'Suite_Data' TO '[DATABASEDIR]Suite_Data.mdf', MOVE
'Suite_Log' TO '[DATABASEDIR]Suite_Log.ldf',REPLACE"/>
</Component>
Eric
-----Original Message-----
From: Eric Latendresse [mailto:[email protected]]
Sent: Wednesday, January 28, 2009 9:11 AM
To: General discussion for Windows Installer XML toolset.
Subject: Re: [WiX-users] Creating SQL Database with WiX v3
Is it possible the Sql Database is locked after it creates it? The .mdf
and .ldf file DO get created before the .msi is finished, but maybe
because the .msi isn't complete the files are locked? Just a thought. If
this is the case is there a way around this? TO create the blanks sql
Database, unlock it, then run the restore script?
Eric Latendresse
-----Original Message-----
From: Eric Latendresse [mailto:[email protected]]
Sent: Wednesday, January 28, 2009 8:59 AM
To: General discussion for Windows Installer XML toolset.
Subject: Re: [WiX-users] Creating SQL Database with WiX v3
Still getting the error and no way to debug? Are there any other
options? This way of restoring a database SHOULD work, right?
Eric
-----Original Message-----
From: Eric Latendresse [mailto:[email protected]]
Sent: Tuesday, January 27, 2009 8:32 AM
To: General discussion for Windows Installer XML toolset.
Subject: Re: [WiX-users] Creating SQL Database with WiX v3
Yes, The sql server is local. It creates the blank database correctly
and I can see the .mdf and .ldf files at the time of the error. The
backup file is there as well. The problem must be with the restore
script. I can run the script manually and it works fine. Is there
anything that would cause WiX to read the sqlstring wrong? Maybe the
single quotes aren't coming through as they should? Is there a WiX log
or a way to add one so that I can get a more detailed message?
Eric
-----Original Message-----
From: David Reed [mailto:[email protected]]
Sent: Monday, January 26, 2009 3:34 PM
To: General discussion for Windows Installer XML toolset.
Subject: Re: [WiX-users] Creating SQL Database with WiX v3
That's often a 404-equivalent. There's usually more info in the server
error log.
Error -2147217900
Cannot open backup device '<incorrect_path_2_file.bak>'.
Device error or device off-line. See the SQL Server error log
for more details.
Make sure the SQL Server process has permissions to that path and that
the file actually exists there.
You are trying to restore to a local server instance, right? Not a
remote server?
-----Original Message-----
From: Eric Latendresse [mailto:[email protected]]
Sent: Monday, January 26, 2009 13:21
To: General discussion for Windows Installer XML toolset.
Subject: Re: [WiX-users] Creating SQL Database with WiX v3
I tried to restore the database from a backup file during the initial
install and am getting this error below. Is there WiX log or something
where I can get some more info on how to debug this? Below is my code
and I think this should work. Anyone have any ideas? Thanks in advance
for you help.
<Component Id="SuiteDatabaseComponent"
Guid="d6e96011-3252-4e85-80b5-b1ff64045e88">
<File Id="DatabaseBackup"
Name="SuiteBlank.bak" Source="..\..\Database\Backups\SuiteBlank.bak" />
<CreateFolder/>
<!-- installs database -->
<sql:SqlDatabase Id="db1"
Server="[SQLSERVER]"
Instance="[SQLINSTANCE]" Database="Suite"
CreateOnInstall="yes"
ConfirmOverwrite="yes"
DropOnUninstall="no" User="SQLUser">
<!-- define where the database
files are saved -->
<sql:SqlFileSpec Id="mdf"
Name="Suite_Data"
Filename="[DATABASEDIR]Suite_Data.mdf"
Size="2MB"
GrowthSize="2MB"/>
<sql:SqlLogFileSpec Id="ldf"
Name="Suite_Log"
Filename="[DATABASEDIR]Suite_Log.ldf"/>
</sql:SqlDatabase>
<sql:SqlString Id='RestoreDB'
SqlDb='db1' ContinueOnError='no' ExecuteOnInstall='yes'
SQL="RESTORE DATABASE Suite FROM DISK = '[DATABASEDIR]SuiteBlank.bak'
WITH MOVE 'Suite_Data' TO '[DATABASEDIR]Suite_Data.mdf', MOVE
'Suite_Log' TO '[DATABASEDIR]Suite_Log.ldf',REPLACE"/>
</Component>
Error -2147217900: failed to execute SQL string, error detail: RESTORE
DATABASE is terminating abnormally., SQL key: RestoreDB SQL string:
RESTORE DATABASE Suite FROM DISK = 'G:\Database\SuiteBlank.bak' WITH
MOVE 'Suite_Data' TO G:\ ...\Suite_Log.ldf',REPLACE
Eric Latendresse
-----Original Message-----
From: David Reed [mailto:[email protected]]
Sent: Friday, January 23, 2009 11:44 PM
To: General discussion for Windows Installer XML toolset.
Subject: Re: [WiX-users] Creating SQL Database with WiX v3
It really depends on what those 9k scripts are doing.
If your scripts are a raft of INSERT statements or other transactions,
then you can probably switch to CSVs and bulk load (much faster), which
is what we do with AdventureWorks now for SQL Server 2008 samples. The
WiX3 code for ours is checked into CodePlex here along with the MSBuild
scripts, etc:
http://www.codeplex.com/SqlServerSamples/SourceControl/ListDownloadableC
ommits.aspx
In our experience with AdventureWorks' installers, switching to a
backup/restore is likely to bloat the size of your installer quite a bit
because the BAK files aren't nearly as compressible (if at all) as
script files are...
________________________________________
From: Eric Latendresse [[email protected]]
Sent: Friday, January 23, 2009 13:10
To: General discussion for Windows Installer XML toolset.
Subject: [WiX-users] Creating SQL Database with WiX v3
I am creating a SQL database with my installer. Right now I have
approximately 9000 scripts to be run after the database is created. This
is taking around 20mins for the install to complete. Is there anything I
can do to speed this process up? I thought about running a single
script to restore the database from a backup file of the database, but
thought I'd ask if there was a better way.
Eric Latendresse
------------------------------------------------------------------------
------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
WiX-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wix-users
------------------------------------------------------------------------
------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
WiX-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wix-users
------------------------------------------------------------------------
------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
WiX-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wix-users
------------------------------------------------------------------------
------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
WiX-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wix-users
------------------------------------------------------------------------
------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
WiX-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wix-users
------------------------------------------------------------------------
------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
WiX-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wix-users
------------------------------------------------------------------------
------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
WiX-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wix-users
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
WiX-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wix-users
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
WiX-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wix-users
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
WiX-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/wix-users
