Connect to database in non standard location

8 Jul 2006 7:14 PM

mr_doles

I am writing a Database application using Visual Studio 2005 and SQL
Server Express 2005. I am trying to create a back up and year end back

up plan within the program. The folder structure I want is as follows:

The main level is c:\program files\app name\database with the following

subfolders, 1) current - holds the current db (db.mdf), 2) backup -
holds the backed up databases (db1.bak) and 3) archive - holds the
year end databases (db-2005.mdf). I have two questions:

1) How do I setup the connection string to connect to a database that
is not located in the standard C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data location?

2) Is there a way to setup the security on the backup folder during
setup to allow the SQLServer2005MSSQLUser write access? I was getting
access denied running the BACKUP DATABASE db TO DISK sql statement.

8 Jul 2006 10:03 PM

Stephany Young

1. You need to specify the desired path when you execute the CREATE DATABASE
statement. SQL Server (Express) keeps track of where the database is so that
you DON'T need to worry about it in your connection string.

2. My advice is to steer clear of storing volatile files in the Program
Files cascade as access to this cascade is restricted.

Show quoteHide quote

"mr_doles" <toddli***@charter.net> wrote in message
news:1152386099.694303.133430@75g2000cwc.googlegroups.com...
>I am writing a Database application using Visual Studio 2005 and SQL
> Server Express 2005. I am trying to create a back up and year end back
>
> up plan within the program. The folder structure I want is as follows:
>
> The main level is c:\program files\app name\database with the following
>
> subfolders, 1) current - holds the current db (db.mdf), 2) backup -
> holds the backed up databases (db1.bak) and 3) archive - holds the
> year end databases (db-2005.mdf). I have two questions:
>
> 1) How do I setup the connection string to connect to a database that
> is not located in the standard C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data location?
>
>
> 2) Is there a way to setup the security on the backup folder during
> setup to allow the SQLServer2005MSSQLUser write access? I was getting
> access denied running the BACKUP DATABASE db TO DISK sql statement.
>

9 Jul 2006 11:21 AM

mr_doles

> 1. You need to specify the desired path when you execute the CREATE DATABASE
> statement. SQL Server (Express) keeps track of where the database is so that
> you DON'T need to worry about it in your connection string.

What I want to do is similar to the Quicken style backup and have the
ability to open archived databases. When I archive a database it will
make a copy of the database and append a date to it, then truncate the
original database for a fresh start. The archive databases will not be
created by the CREATE DATABASE statement. I read somewhere sometime
that you can put the path to the db in the connection string but I
can't find it now.

> 2. My advice is to steer clear of storing volatile files in the Program
> Files cascade as access to this cascade is restricted.

Where would be a good spot to put them then? I tested with c: and got
the access denied error as well. Is there a folder that I can backup
to without worrying about security or do I need to set the security on
Setup?