MS SQL Server - Newbie question, database file locations.

Author

Newbie question, database file locations.

Steve Jones

2006-11-09, 5:16 am

Hi,

I've done a default install of Server Express 2005.

I'm trying create my first database from scratch using SQL statements in a
C# application.

Things are going OK (mostly) and I can create my database and add/delete
tables OK but I'm puzzled by the following:

My database connection string is:

@"Data Source=. \\SQLEXPRESS;Integra
ted Security=True;Connec
t
Timeout=30;User Instance=True";

If I create my database using the simple SQL command:

"CREATE DATABASE mydatabase;"

The database files are created in my Documents and Settings:

C:\Documents and Settings\...
...\Local Settings\Application
Data\
...Microsoft\Microsoft SQL ServerData\SQLEXPRES
S\
...mydatabase.mdf

I'd like to have the database files created in a common or default location
and not in Documents and Settings. I don't want to have to specify
this location in the C# SQL statment by using the FILENAME clause.

Anyone know how I can do this?

Also, although my database and tables are created OK, when I open SQL
Server Management Studio Express other databases are listed but not the
ones I created using my simple SQL statment. I can go and attach it myself
OK and everything seems fine but is it possible to get it listed
(attached?) automatically?

Thanks, Steve.

Uri Dimant

2006-11-09, 5:16 am

Steve
It is because you have specified this default location for the data files
when you installed SQL Server 2005 , have not you?

If you connect to query builder and run CREATE DATABASE ... statement where
will the db be created?

HKEY_LOCAL_MACHINE\S
OFTWARE\Mi_crosoft\M
SSQLServer\MSSQLServ
er\DefaultData
HKEY_LOCAL_MACHINE\S
OFTWARE\Mi_crosoft\M
SSQLServer\MSSQLServ
er\DefaultLog

For named Instance:

HKEY_LOCAL_MACHINE\S
OFTWARE\Mi_crosoft\M
icrosoft SQL Server\[Instance
Name]\MSSQLServer\De
faultData
HKEY_LOCAL_MACHINE\S
OFTWARE\Mi_crosoft\M
icrosoft SQL Server\[Instance
Name]\MSSQLServer\De
faultLog

"Steve Jones" <steve@istech.demon.co.uk> wrote in message
news:eiuuga$jr5$1$83
0fa795@news.demon.co.uk...
> Hi,
>
> I've done a default install of Server Express 2005.
>
> I'm trying create my first database from scratch using SQL statements in a
> C# application.
>
> Things are going OK (mostly) and I can create my database and add/delete
> tables OK but I'm puzzled by the following:
>
> My database connection string is:
>
> @"Data Source=. \\SQLEXPRESS;Integra
ted Security=True;Connec
t
> Timeout=30;User Instance=True";
>
> If I create my database using the simple SQL command:
>
> "CREATE DATABASE mydatabase;"
>
> The database files are created in my Documents and Settings:
>
> C:\Documents and Settings\...
> ...\Local Settings\Application
Data\
> ...Microsoft\Microsoft SQL ServerData\SQLEXPRES
S\
> ...mydatabase.mdf
>
>
> I'd like to have the database files created in a common or default
> location
> and not in Documents and Settings. I don't want to have to specify
> this location in the C# SQL statment by using the FILENAME clause.
>
> Anyone know how I can do this?
>
>
> Also, although my database and tables are created OK, when I open SQL
> Server Management Studio Express other databases are listed but not the
> ones I created using my simple SQL statment. I can go and attach it myself
> OK and everything seems fine but is it possible to get it listed
> (attached?) automatically?
>
>
> Thanks, Steve.

Adi

2006-11-09, 5:16 am

Hi Steve

When you ran CREATE DATABASE statement, you can give it parameters
about the files that you want to have (logical and physical names,
size, growth rate etc'). If you don't supply the parameters, the
server will use defaults. If you don't want to use the defaults then
you'll have to specify the parameters in the create database statement.
Here is an example:

CREATE DATABASE MyDB
ON
( NAME = MyDB_Dat,
FILENAME = 'c:\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = MyDB_LOG,
FILENAME = 'd:\saledat.ldf',
SIZE = 1,
MAXSIZE = 5,
FILEGROWTH = 1 )

Adi

Steve Jones wrote:
> Hi,
>
> I've done a default install of Server Express 2005.
>
> I'm trying create my first database from scratch using SQL statements in a
> C# application.
>
> Things are going OK (mostly) and I can create my database and add/delete
> tables OK but I'm puzzled by the following:
>
> My database connection string is:
>
> @"Data Source=. \\SQLEXPRESS;Integra
ted Security=True;Connec
t
> Timeout=30;User Instance=True";
>
> If I create my database using the simple SQL command:
>
> "CREATE DATABASE mydatabase;"
>
> The database files are created in my Documents and Settings:
>
> C:\Documents and Settings\...
> ...\Local Settings\Application
Data\
> ...Microsoft\Microsoft SQL ServerData\SQLEXPRES
S\
> ...mydatabase.mdf
>
>
> I'd like to have the database files created in a common or default location
> and not in Documents and Settings. I don't want to have to specify
> this location in the C# SQL statment by using the FILENAME clause.
>
> Anyone know how I can do this?
>
>
> Also, although my database and tables are created OK, when I open SQL
> Server Management Studio Express other databases are listed but not the
> ones I created using my simple SQL statment. I can go and attach it myself
> OK and everything seems fine but is it possible to get it listed
> (attached?) automatically?
>
>
> Thanks, Steve.

Steve Jones

2006-11-09, 5:16 am

Hi,

The problem is "User Instance=True" in the connection string. Remove this
and your database files will be created in:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

SQL Server Management Studio Express will list the database as well.

Cheers.

Uri Dimant

2006-11-09, 5:16 am

Steve
Are you a founder of www.sqlservercentral.com ? Just two Steve Jones here
,I think one of them must a founder :-))

"Steve Jones" <steve@istech.demon.co.uk> wrote in message
news:eiv0de$jr5$2$83
0fa795@news.demon.co.uk...
> Hi,
>
>
> The problem is "User Instance=True" in the connection string. Remove this
> and your database files will be created in:
>
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
>
> SQL Server Management Studio Express will list the database as well.
>
> Cheers.

Sponsored Links