vinaysalian
Posts: 6
Joined: Thu Sep 29, 2022 11:38 am

Not to run restore scripts if database exists

Fri Oct 21, 2022 7:21 am

Hello ,

Thank you for creating this wonderful application .
Scenario : during installation the installer should automatically check in SQL Server whether if the database exists. If exists skip running the restore script and just run the update script .

I have a created a Query for the same and saving the result in the DBExists_PROP and same returns TRUE if the database exists and FALSE or (null) if the DATABASE does not exist .( Using the Test Option we have tested the query and the same returns true or false/(null))
query.png
query.png (15.41KiB)Viewed 4546 times
Then I am adding a condition to run the restore script only if the DBExists_PROP ="false" or DBExists_PROP <> "True" but still every time we run the installer the DB Is restored or replaced .during second install.
restore.png
restore.png (21.99KiB)Viewed 4546 times
Kindly advise

Catalin
Posts: 6542
Joined: Wed Jun 13, 2018 7:49 am

Re: Not to run restore scripts if database exists

Mon Oct 24, 2022 1:42 pm

Hello,
Thank you for creating this wonderful application .
Thank you for your kind words! :)

Now, regarding your issue, that might happen because you are using a private property, as opposed to a public property.

Windows Installer Properties

Could you please try setting the property as public (all uppercase characters) and let me know if that helps? E.g.:

Code: Select all

DBEXISTS_PROP
Best regards,
Catalin
Catalin Gheorghe - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

vinaysalian
Posts: 6
Joined: Thu Sep 29, 2022 11:38 am

Re: Not to run restore scripts if database exists

Mon Oct 31, 2022 6:09 am

Dear Catalin,

Apologies for the late response. Have been on weeklong vacation. I tried using property " DBEXISTS_PROP "all in capitals but still it restores the database even if database is present. The test of the query which saves the result in the property returns either a '1' or null. So, the condition which i am using is DBEXISTS_PROP = "false " or DBEXISTS_PROP <> "true" or DBEXISTS_PROP <> 1 in my Restore Script. None of these conditions work and the database gets replaced /overwritten if the database exist . Please let me know if there is anything else which needs to be done.

The query used to check if the database exists is as follows

-- Inline SQL script
USe Master
IF OBJECT_ID('dbo.CheckPersonnelDBExists') IS NOT NULL
DROP FUNCTION CheckPersonnelDBExists
GO
create FUNCTION dbo.CheckPersonnelDBExists (@dbname varchar(128))
RETURNS varchar(10)
AS
BEGIN
declare @result varchar(10) = ''
SELECT @result = CAST(
CASE WHEN db_id(@dbname) is not null THEN '1'

END

AS varchar)
return @result

END

GO

Select dbo.PersonnelDBExists ('Personnel') as DB_EXISTS
GO

This result we save in the property DBEXISTS_PROP
sql_SCript_DBEXISTS_PROP.png
sql_SCript_DBEXISTS_PROP.png (20.48KiB)Viewed 4366 times
Testing the Query results in 1 if DB exists and Null in case the DB does not exist
dbexists.png
dbexists.png (11.61KiB)Viewed 4366 times
dbnotexists.png
dbnotexists.png (12.25KiB)Viewed 4366 times
Kindly advise

Catalin
Posts: 6542
Joined: Wed Jun 13, 2018 7:49 am

Re: Not to run restore scripts if database exists

Tue Nov 01, 2022 4:48 pm

Hello,

From what I can understand, you'd like to restore the database only when the DBEXISTS_PROP="(null)". Have you tried using this condition?

You set the property to either "1" or "(null)" but then use the DBEXISTS_PROP="false" condition.

If that doesn't help, could you please try to create a sample project reproducing this and forwarding it to me together with a test-case describing how I can test this so I can further investigate this on our end?

Best regards,
Catalin
Catalin Gheorghe - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

vinaysalian
Posts: 6
Joined: Thu Sep 29, 2022 11:38 am

Re: Not to run restore scripts if database exists

Tue Nov 08, 2022 4:35 am

Dear Catalin,

Thank you very much. As suggested ,Got this working by using DBEXISTS_PROP <> "TRUE" since the QUERY returns a TRUE value if the DB is installed. Thank you for all the help. :)

Catalin
Posts: 6542
Joined: Wed Jun 13, 2018 7:49 am

Re: Not to run restore scripts if database exists

Tue Nov 08, 2022 3:52 pm

You are always welcome! :)

Glad I was able to assist.

Best regards,
Catalin
Catalin Gheorghe - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Common Problems”