Connect to database in non standard location

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • mr_doles

    #1

    Connect to database in non standard location

    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 SQLServer2005MS SQLUser write access? I was getting
    access denied running the BACKUP DATABASE db TO DISK sql statement.

  • Stephany Young

    #2
    Re: Connect to database in non standard location

    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.


    "mr_doles" <toddlisa2@char ter.netwrote in message
    news:1152386099 .694303.133430@ 75g2000cwc.goog legroups.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 SQLServer2005MS SQLUser write access? I was getting
    access denied running the BACKUP DATABASE db TO DISK sql statement.
    >

    Comment

    • mr_doles

      #3
      Re: Connect to database in non standard location

      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?

      Comment

      Working...