SQL Scripts - Database Restoration

[Bottesford]

I've had a look through the various threads on here in regards to restoring databases using SQL scripts but still have a few questions.
I'm aware that it seems only possible to perform a database restore by executing a script using 'sqlcmd.exe' and not directly with Advanced Installer (has this changed since?).
Problem is a script will need hard coded paths to the database backup file and MDF restore paths. eg:

Code: Select all

    RESTORE DATABASE SAMPLE
    FROM DISK = 'C:\InstallDir\SAMPLE.bak'
    WITH MOVE 'SQL_dat' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SAMPLE.mdf',
    MOVE 'SQL_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SAMPLE_log.ldf'

Problem I have is that 'C:\InstallDir\SAMPLE.bak' is may not be the selected installation directory and SQL Server may have been installed to a drive other than C:. Without Advanced Installer's '[ProgramFilesFolder]', etc. paths in the script it may not work. Is there a way to rewrite the script during install? Any other method that I've not thought about maybe?

Thanks for your help.

[Cosmin]

Hi,

I'm aware that it seems only possible to perform a database restore by executing a script using 'sqlcmd.exe' and not directly with Advanced Installer (has this changed since?).

Please note that Advanced Installer allows you to restore databases by running SQL scripts. However, the MS SQL Server usually hang in the restoring state (this is a MS SQL Server problem). Therefore, if you are using MS SQL server it is recommended to use "sqlcmd.exe" (you can also try using the SQL Scripts page).

Problem is a script will need hard coded paths to the database backup file and MDF restore paths.

The sqlcmd.exe utility supports the -v parameter which allows you to define variables. These variables can be set to installer properties and used inside the script. Here is a sample command line:

Code: Select all

sqlcmd -E -i restoredb.sql -v path="[MY_DIR]"

where "MY_DIR is a property which contains the path you need.

Regards,
Cosmin

[Bottesford]

Thanks for that.
I actually had a go using execution of SQL scripts from within Advanced Installer (not using sqlcmd.exe) and found it to work absolutely fine. This was using SQL 2005 Express restoring a single database using the following SQL script:

Code: Select all

RESTORE DATABASE [SAMPLE] FROM  DISK = N'C:\Sample.BAK' WITH  FILE = 1,  
MOVE N'SQL_dat' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SAMPLE.mdf',  
MOVE N'SQL_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SAMPLE_log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

Then I just used find-replace on the script to change the paths to the installer generated ones. Works fine - with no hanging or any other problem (at least on our test machine anyway).