SQL Script for Database Restore.

[machinegreeneus]

I am using Advanced Installer Project version 4.8.1 The problem that i am facing is that i have selected to create an sql server database and that is being done but now after creating that database i need to restore using a backup file that i already have and have written an sql script which when i test it from the query analyser its working fine. Now the problem is that when i include the restore script in the project it executes and completes the setup but when i go to check the contents of the database, its in Restoring mode and cannot access any objects from that database. I have restarted my machine but it still remains in the restoring mode. Can someone help.

[gigi]

Hi,

When performing RESTORE using one of Microsoft's tools (in your case SQLCMD.exe) the process is completed successfully. When using the ODBC driver for MS SQL Server (like Advanced Installer) the RESTORE process hangs and your database status freezes in "Restoring" state, making it unaccessible.

We have searched for a solution be unfortunately we couldn't discover the problem. If you have any suggestions on why this happens or a workaround please contact us as we are interested in solving this problem as soon as possible.

In the meantime you should continue using a custom action for restoring your database (using SLQCMD.exe), but other necessary SQL scripts could very well be deployed using the AI SQL Scripts functionality (for a more powerful Windows Installer integration).

Regards,
Gigi
______________
Gheorghe Rada
Advanced Installer Team
http://www.advancedinstaller.com

[amoedo]

Hi,

I am facing the same problem. Do you have any more information about it?

In my SQL Server logs I have found this message: "The database .... is marked RESTORING and is in a state that does not allow recovery to be run"

Thanks,
Marco

[Ionut]

Hi Marco,

We could not find any solution for this problem (which is related to the ODBC driver for SQL Server, as mentioned above).

The only workaround (for now) would be to use "SQLCMD.exe" as a Custom Action to restore the database. If you need more details on how to achieve this, please let me know.

Regards,
Ionut

[kumarkiranc]

i need more details on how to achieve this

The only workaround (for now) would be to use "SQLCMD.exe" as a Custom Action to restore the database.



Thanks
Kiran

[Ionut]

Hi Kiran,

First, you need to create a File Search in the Search page for "sqlcmd.exe" and name it for instance SQLCMD. Right-click on the search and select "Add Location -> Folder", specifying the following parameters:

- Path : [ProgramFilesFolder]Microsoft SQL Server
- Depth : 5

If you are using SQL Server 2000/MSDE, search for "osql.exe" instead of "sqlcmd.exe". Use the "Test Search" toolbar button in order to make sure the Search is configured correctly.

In the Custom Actions page, create a new "EXE with Working Dir" Custom Action under Install, with the following parameters:

- Working Dir: (any)
- Full Path:

Code: Select all

"[SQLCMD]" -S .\InstanceName -U user -P password -i "[#RestoreScript.sql]"

- Execution Properties: Synchronous execution, ignore return code
- Execution Options: Deferred with no impersonation
- Execution Condition: (Not Installed) AND SQLCMD

Replace SQLCMD with the Property you have used to name your Search and instead of "RestoreScript.sql" select the SQL script file from your package (use the "Edit" button at the right of the "Full Path" field).

Hope this helps.

Regards,
Ionut

[kumarkiranc]

Hi,

Iam restoreing database into SQL Express Edition with Advanced Services.

iam searching the file osql.exe

in custom action >installfiles
i selected exe with working dir

full path



backup file i kept in windows temp.. folder

in application directory i craeted one regular folder DATABASE


if i give the full path like



backup file i kept in DATABASE dir in application

it works fine.

now how can i handle in adavce installer

Thanks
Kiran

[Cosmin]

Hi,

it works fine.

now how can i handle in adavce installer

I'm not sure I understand what you mean. Can you please give more details about this?

Regards,
Cosmin