Server install script

[Ty]

This one is stumping me.. please help.

I put together a DB with lots of tables, sprocs, etc..

Now I want to wrap it up into one nice little .SQL which will generate
everything for a user. To that end, I went into Enterprise Manager, and
selected "Generate SQL script".

All is well until someone wants to execute it on a machine where SQL is not
installed in the same DIR where I have SQL installed. So.. how do I generate
a CREATE script which is smart enough to create the DB in the same physical
path where SQL is installed for that particular user?

Have I lost you yet? Here is a snippet of the DDL:

<paste>

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MyNewDB')

DROP DATABASE [MyNewDB]

GO

CREATE DATABASE [MyNewDB] ON (NAME = N' MyNewDB _Data', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL\data\ MyNewDB_Data.MDF' , SIZE
= 34, FILEGROWTH = 10%) LOG ON (NAME = N' MyNewDB_Log', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL\data\ MyNewDB.LDF' , SIZE =
344, FILEGROWTH = 10%)

COLLATE SQL_Latin1_General_CP1_CI_AS

GO

</paste>

OK.. how do I make the areas in BOLD write to <%SQLSERVERINSTALLPATH%>, or
something like that?

Thanks!

/Ty

[John Bell]

Hi

Using create database with no on clause will create a mdf and ldf in the
default locations. This is not necessarily where the SQL Server program was
installed, and is probably the better location.

You can then use sp_helpfile to find out the actual locations if you want to
add new files, or use
ALTER DATABASE to change the files there were created.
CREATE DATABASE Test2
USE TEST2
sp_helpfile

ALTER DATABSE Test2
MODIFY FILE (NAME=Test2, SIZE=34MB, FILEGROWTH=10MB )

Also I would always fix the FILEGROWTH to a value in MBs as the growth will
be exponential if left as a percentage.

Your initial size of the log file seems excessive especially when compared
to the initial size of the data file.

John

"Ty" <tybala on the server at hotmail.com> wrote in message
news:53******************************@news.teranew s.com...

This one is stumping me.. please help.

I put together a DB with lots of tables, sprocs, etc..

Now I want to wrap it up into one nice little .SQL which will generate
everything for a user. To that end, I went into Enterprise Manager, and
selected "Generate SQL script".

All is well until someone wants to execute it on a machine where SQL is not installed in the same DIR where I have SQL installed. So.. how do I generate a CREATE script which is smart enough to create the DB in the same physical path where SQL is installed for that particular user?

Have I lost you yet? Here is a snippet of the DDL:

<paste>

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MyNewDB')
DROP DATABASE [MyNewDB]

GO

CREATE DATABASE [MyNewDB] ON (NAME = N' MyNewDB _Data', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL\data\ MyNewDB_Data.MDF' , SIZE = 34, FILEGROWTH = 10%) LOG ON (NAME = N' MyNewDB_Log', FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL\data\ MyNewDB.LDF' , SIZE =
344, FILEGROWTH = 10%)

COLLATE SQL_Latin1_General_CP1_CI_AS

GO

</paste>

OK.. how do I make the areas in BOLD write to <%SQLSERVERINSTALLPATH%>, or
something like that?

Thanks!

/Ty