2. NSIS Discussion
  3. Attaching a database to MS SQL Express 2005

Archive: Attaching a database to MS SQL Express 2005


Andy1988
3rd August 2007 16:29 UTC

Attaching a database to MS SQL Express 2005
Hello,
I'm writing an installer script for a server application I created.
I need some dependencies like .NET 3, MSI 3.1 and MS SQL 2005.
I managed all these things to download and install automatically and silent by my installer script.

For a bit more comfort I want to attach a database directly to the SQL Server. For this task I'm currently Using the MSSQL Plugin.
The logon is working. But then I want to attach the database and the setup is crashing. The query is the following:

CREATE DATABASE GoSell2006
ON PRIMARY (FILENAME = '$INSTDIR\Datenbank\eBayERP_Data.MDF')
LOG ON (FILENAME = '$INSTDIR\Datenbank\eBayERP_Log.LDF')
FOR ATTACH;

In SQL Management Studio this query works.
But then I discovered that the attached database is write protected. I looked at the rights of the two files and it seems that the account Network service (Netzwerkdienst in german) in Windows needs read and write permissions. By default these permissions are not set by the installer. I think thats the reason why the installer crashes every time I want to attach the database.

Is there any way to set these permissions how I need them by the installer script?

Thank you!
Andreas Galauner

michaelcsikos
6th August 2007 02:10 UTC

Did you sort it out, Andy?

In the past I have created new databases from scratch using the SQL Server Command Line Tool sqlcmd.exe and database scripts for the schema, initialisation of data etc.

I used a Windows Command Script called CreateDatabase.cmd which accepted the database instance name and database name as parameters and then fired off the sql scripts. Using a cmd file means you can get it working outside of NSIS first, and you can recreate databases outside of your installer at any time.

CreateDatabase.cmd

@echo off
setlocal
rem cls

IF "%~1"=="" (
    SET a=2
    SET ErrorText=No database instance name provided.
    Goto Error
) ELSE (SET Instance=%~1)

IF "%~2"=="" (
    SET a=3
    SET ErrorText=No database name provided.
    Goto Error
) ELSE (SET Database=%~2)

Title Creating database "%Database%" on "%Instance%"

echo Creating database...
sqlcmd -S "%Instance%" -b -v Database="%Database%" SQLPath="" -i CreateDatabase.sql
SET a=%ErrorLevel%
IF %a%==0 (
    echo ...Successfully created database.
) ELSE (
    rem SET a=1
    SET ErrorText=Failed to create database.
    Goto Error
)

echo Creating database schema...
sqlcmd -S "%Instance%" -b -d "%Database%" -i DatabaseSchema.sql
IF %ErrorLevel%==0 (
    echo ...Successfully created database schema.
) ELSE (
    SET a=2
    SET ErrorText=Failed to create database schema.
    Goto Error
)

echo Running initialisation scripts...
sqlcmd -S "%Instance%" -b -d "%Database%" -i Initialisation.sql
IF %ErrorLevel%==0 (
    echo ...Successfully ran initialisation scripts.
) ELSE (
    SET a=3
    SET ErrorText=Failed to run initialisation scripts.
    Goto Error
)
...


CreateDatabase.sql

:On Error exit
--:Out null
--:SetVar SQLCMDERRORLEVEL 18

DECLARE @Database varchar(64);
DECLARE @Error int;
DECLARE @Path varchar(256);

SET @Database = '$(Database)';
SET @Path = '$(SQLPath)';
IF @Path IS NULL OR RTrim(@Path) = ''
        SET @Path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX('master.mdf', LOWER(physical_name)) - 1)
                                FROM master.sys.master_files
                                WHERE database_id = 1 AND file_id = 1);
ELSE IF Right(@Path, 1) != '\'
        SET @Path = @Path + '\';

IF EXISTS(SELECT [name] as [Database]
                FROM master.sys.databases
                WHERE [name] = @Database)
        RAISERROR ('Database already exists.', 18, 10); --      :Exit(SELECT 10)

IF EXISTS(SELECT [name]
                FROM master.sys.master_files
                WHERE physical_name = @Path + @Database + '.mdf' OR physical_name = @Path + @Database + '_log.ldf')
        RAISERROR ('Database files already exist.', 18, 11);    --      :Exit(SELECT 11)

EXECUTE('CREATE DATABASE ' + @Database + ' ON PRIMARY 
                (NAME = ' + @Database + ', FILENAME = ''' + @Path + @Database + '.mdf'', FILEGROWTH = 1024KB)
                LOG ON
                (NAME = ' + @Database + '_log, FILENAME = ''' + @Path + @Database + '_log.ldf'', FILEGROWTH = 10%)
                COLLATE SQL_Latin1_General_CP1_CI_AI');
IF @@Error != 0
        RAISERROR ('Failed to creating database.', 18, 12);     --      :Exit(SELECT 2)


And then exec the cmd file with:
nsExec::ExecToLog '"$TEMP\CreateDatabase.cmd" $SQL_INSTANCE $DB_NAME'

Andy1988
6th August 2007 11:30 UTC

Thank you!
That's exactly what I need.

Especially, because this setup has an update feature also and I can do some migration tasks with this solution.

The only thing I have to test, if the sqlcmd.exe works without any dependencies like dlls and so on.
I think it is only delivered with the SQL Management Studio and I don't want to install this.

michaelcsikos
7th August 2007 06:04 UTC

Glad I could help.

I just installed SQL Express 2005 SP1 on a clean Virtual PC without installing the Management Studio and sqlcmd.exe is available on the system, so you shouldn't have any problems.

meaningoflights
12th January 2008 20:54 UTC

Thanks also michaelcsikos. You code has been very useful for me.

Tip: In michaelcsikos script the %instancename% is the "PCName\DbInstance" name. Took me 10 mins to work that out and another 10 then to get the local PC name.


If anyone wants more info on sqlcmd, this is a good start:
http://msdn2.microsoft.com/en-us/library/ms162773.aspx


Lastly, its easier to restore a database from a bak file and create it in the one action.

Rather than Creating the DB and then either restoring it or running scripts.. this is what I mean:


The NSIS Call:

System::Call 'kernel32.dll::GetComputerNameA(t .r0,*i ${NSIS_MAX_STRLEN} r1)i.r2'
nsExec::ExecToLog '"$INSTDIR\CreateDatabase.cmd" "$0\$InstanceName" "$DbName" "$INSTDIR\DbBackFile'



The cmd file:
@echo off
setlocal
rem cls

IF "%~1"=="" (
SET a=2
SET ErrorText=No database instance name provided.
Goto Error
) ELSE (SET Instance=%~1)

IF "%~2"=="" (
SET a=3
SET ErrorText=No database name provided.
Goto Error
) ELSE (SET Database=%~2)

IF "%~3"=="" (
SET a=4
SET ErrorText=No BackUpPath name provided.
Goto Error
) ELSE (SET BackUpPath=%~3)


Title Creating database "%Database%" on "%Instance%" with script CreateDataBase.sql

echo Creating database...
sqlcmd -S "%Instance%" -b -v Database="%Database%" SQLPath="" BackUpPath="%BackUpPath%" -i CreateDataBase.sql
SET a=%ErrorLevel%
IF %a%==0 (
echo ...Successfully created database.
) ELSE (
rem SET a=1
SET ErrorText=Failed to create database.
Goto Error
)


The sql file:
:On Error exit
--:Out null
--:SetVar SQLCMDERRORLEVEL 18

DECLARE @Database varchar(64);
DECLARE @Error int;
DECLARE @Path varchar(256);
DECLARE @BackUpPath varchar(256);



SET @Database = '$(Database)';
SET @Path = '$(SQLPath)';
SET @BackUpPath = '$(BackUpPath)';


IF @Path IS NULL OR RTrim(@Path) = ''
SET @Path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX('master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
ELSE IF Right(@Path, 1) != ''
SET @Path = @Path + '';

IF EXISTS(SELECT [name] as [Database]
FROM master.sys.databases
WHERE [name] = @Database)
RAISERROR ('Database already exists.', 18, 10); -- :Exit(SELECT 10)


EXECUTE('RESTORE DATABASE [' + @Database + '] FROM DISK = ''' + @BackUpPath + '.bak''');


IF @@Error != 0
RAISERROR ('Failed to restore database.', 18, 12); -- :Exit(SELECT 2)

meaningoflights
20th January 2008 02:09 UTC

If you see that the RESTORE DATABASE command fails on VISTA.. like me after 100hrs testing, use the CREATE DATABASE command, it actually f'n works.

Fork me on GitHub