inconsistent database naming scheme?

In my physical database directory, I found the databases were named in several different ways:
1. db1.mdf
db1.ldf
2. db2_Data.MDF
db2_Log.LDF
3. db3.mdf
db3_Log.LDF
4. msdbdata.mdf
msdblog.ldf
5. model.mdf
modellog.ldf
How could this happen? I read the online books and just found that .mdf and .ldf are generally standard suffix for database names. What about other parts of a name? I do not remember how the names of these databases originally looked like. We're experiencing database corruptions these days. Those database names just started looking suspicious to me.
I would greatly appreciate any insights or pointers.
BingThe names of the files has absolutely nothing to do with corruption in the
database. Usually corruption is the result of faulty hardware these days.
The only standard per say for data files is the Primary data file has an
extension of .mdf and any secondary ones have an extension of .ndf and of
coarse Log files are .ldf.
--
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing|||> How could this happen?
Physical database file names can be any legal file name supported by your
file system:
CREATE DATABASE MyDatabase
ON(NAME='MyDatabase', FILENAME='C:\ICanNameThisAnything.abcde')
LOG ON(NAME='MyDatabase_Log', FILENAME='C:\ICanNameThisAnythingToo.defgh')
Andrew noted the suggested suffixes and it's a good practice to specify the
database name as the first part of the file name. The remainder of the file
name is up to you. Personally, I add a number to make the file name unique
and also include the filegroup name for files in user-defined filegroups.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing|||Thanks for the information.
Bing
"Dan Guzman" wrote:
> > How could this happen?
> Physical database file names can be any legal file name supported by your
> file system:
> CREATE DATABASE MyDatabase
> ON(NAME='MyDatabase', FILENAME='C:\ICanNameThisAnything.abcde')
> LOG ON(NAME='MyDatabase_Log', FILENAME='C:\ICanNameThisAnythingToo.defgh')
> Andrew noted the suggested suffixes and it's a good practice to specify the
> database name as the first part of the file name. The remainder of the file
> name is up to you. Personally, I add a number to make the file name unique
> and also include the filegroup name for files in user-defined filegroups.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> > In my physical database directory, I found the databases were named in
> several different ways:
> >
> > 1. db1.mdf
> > db1.ldf
> >
> > 2. db2_Data.MDF
> > db2_Log.LDF
> >
> > 3. db3.mdf
> > db3_Log.LDF
> >
> > 4. msdbdata.mdf
> > msdblog.ldf
> >
> > 5. model.mdf
> > modellog.ldf
> >
> > How could this happen? I read the online books and just found that .mdf
> and .ldf are generally standard suffix for database names. What about other
> parts of a name? I do not remember how the names of these databases
> originally looked like. We're experiencing database corruptions these days.
> Those database names just started looking suspicious to me.
> >
> > I would greatly appreciate any insights or pointers.
> >
> >
> > Bing
>
>|||Additionally, if you are getting corruptions..
Ensure you are running your dbccs and watching them closely. corruption is
generally a preview for an upcoming ( but now transient) hardware error. Get
your hardware guys involved and check the disks, controllers, etc.
You may wish to keep your database backups a little longer until you have
found the problem and corrected it...You do not want to get into a
situation where all of your database backups are copies of a corrupted
database
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing

Including Sql Express DB with Winforms Application

We are thinking of using a Sql Server Express database (.mdb.mdf) as a data resource in a Windows Forms application that we will be distributing to clients. When the application is built, the mdb file is copied to the bin directory.

In order for clients to access this database, will they need to have an instance of the SQLEXPRESS server installed on their computer? Or since the .mdb.mdf file is being accessed from a .Net 2.0 application, there is no need to install a separate db component?

If I do have to separately install the db component on the client end,
is this the way to go about it? This? Or is there a different/better way?

If your file really is .mdb, this is an Access file, and your program will connect to it with out any trouble.

SQL Express files are .mdf

For SQL Express, just drop your data file into your resources directory, and it will be published with your project. (This works with any file you want to include in your project, like graphic files used in your reports, etc.)

Set your Build Action to "Content" and make sure you select "Copy if newer", and not "Copy Always" as this will erase any data you have everytime you test your program.

- Richard.

|||

When you do distribute you can package up the SQL Engine (SQL Express) into the installer... but yes the clients do need the engine installed on there system. Now depending on how you are access the database you have several options. With express you have two ways of connecting to the database, the first is to just install the engine and attach the files to that engine, then you can connect to the database from the client as well as from other clients.... ie remote clients (After turning on the remote Access) using the one database server. The other option is to use a User Instance of the database. With this method the database is attached to the engine by the SQL Native client at runtime. This means that when you start the application that you have developed, the SQL Native client connects using the Connection string you provide (With the Attach option) and attaches the DB.... when the app is finished with it it will detatch the database. One problem with this is that generally it is set to single user and only the security context of the application running the App will be able to access the database.

So in summary if only the client needs to connect to the DB at the one time the best option would be to dist the sql engine and client app seperatly and allow the sql native client to do it's job and attach the db.... but if more then one client needs to access the same database at the one time You should attach the db your self and use the standard client server setup.

Hope this helps..

|||Richard - Thanks, you are right. I meant to say "mdf"|||Thanks Glenn.

In my situation, we will have an instance of SQL Express installed on the client machine, with only that one client connecting to the database. So you think that I should connect to the DB with a connection string that attaches the DB, and when the APP is finished it will detach the DB? What is the format of such a string. Right now I am using a connection string of the following format:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db.mdf;Database=DBName;Integrated Security=False;User Instance=False;User ID=USERNAME;Password=PASSWORD;Connect Timeout=30;

As I understand it, this connection string will attach the DB to the database with the name provided in the "Database" parameter. In the future, the DB will already be attached to the server under that name. How should the connection string be modified so that when the APP is finished it will detach the DB? Will this happen when the application closes? Or does this happen before and after every single database interaction (thus, while the application is open, you could be attaching and detaching the database thousands of times)? If so, what kind of extra overhead does this constant attaching and detaching cause?

Additionally, we would like to prevent any access to the database unless it is with the Username and Password (ie: we do not want them to access the database directly, rather through the application that we provide. In the application itself, the connection string will be encrypted). Is there a way to prevent the user from connecting to the database through the SA account or the Admin account for the computer, to ensure that all connections are done only using the username and password?

include .mdf, .ndf, and .ldf files in exclusion list

Hi,
In the past I have always included .mdf, .ndf, and .ldf files in the
exclusion list for
both Antivirus and backup agents.
This message is primarily in regards to an open file backup agent.
I now work at a place that has an open file back agent.
Prior to my arriving the previous dba sucessfully tested a backup to tape
and restore using this agent.
Then DR plan was written to make use of this paradigm.
Fast forward 6 months
New product in production, many open file errors on .mdf, .ndf, and .ldf
files causing entrire backup streams to be delayed and eventually failing.
Immediately resolved when file extensions are added to exlusion list.
Does my old school thought process (exclude the files) lack a basis in the
new landscape involving SQL 2K5 and new backup agents?
Can anyone point to a MS document that recommends this as best practise?
I have seen Microsoft KB article 309422 regarding anit virus files.
Any input apprciated.
Hi Joe
I would exclude the files from any filter agent as this can lead to
performance problems, therefore it would be true regardless of SQL server
version. You will need to look at the SLA and find out what level of recovery
is required and how that could be met.
John
"Joe" wrote:

> Hi,
> In the past I have always included .mdf, .ndf, and .ldf files in the
> exclusion list for
> both Antivirus and backup agents.
> This message is primarily in regards to an open file backup agent.
> I now work at a place that has an open file back agent.
> Prior to my arriving the previous dba sucessfully tested a backup to tape
> and restore using this agent.
> Then DR plan was written to make use of this paradigm.
> Fast forward 6 months
> New product in production, many open file errors on .mdf, .ndf, and .ldf
> files causing entrire backup streams to be delayed and eventually failing.
> Immediately resolved when file extensions are added to exlusion list.
> Does my old school thought process (exclude the files) lack a basis in the
> new landscape involving SQL 2K5 and new backup agents?
> Can anyone point to a MS document that recommends this as best practise?
> I have seen Microsoft KB article 309422 regarding anit virus files.
> Any input apprciated.

include .mdf, .ndf, and .ldf files in exclusion list

Hi,
In the past I have always included .mdf, .ndf, and .ldf files in the
exclusion list for
both Antivirus and backup agents.
This message is primarily in regards to an open file backup agent.
I now work at a place that has an open file back agent.
Prior to my arriving the previous dba sucessfully tested a backup to tape
and restore using this agent.
Then DR plan was written to make use of this paradigm.
Fast forward 6 months
New product in production, many open file errors on .mdf, .ndf, and .ldf
files causing entrire backup streams to be delayed and eventually failing.
Immediately resolved when file extensions are added to exlusion list.
Does my old school thought process (exclude the files) lack a basis in the
new landscape involving SQL 2K5 and new backup agents?
Can anyone point to a MS document that recommends this as best practise?
I have seen Microsoft KB article 309422 regarding anit virus files.
Any input apprciated.Hi Joe
I would exclude the files from any filter agent as this can lead to
performance problems, therefore it would be true regardless of SQL server
version. You will need to look at the SLA and find out what level of recover
y
is required and how that could be met.
John
"Joe" wrote:

> Hi,
> In the past I have always included .mdf, .ndf, and .ldf files in the
> exclusion list for
> both Antivirus and backup agents.
> This message is primarily in regards to an open file backup agent.
> I now work at a place that has an open file back agent.
> Prior to my arriving the previous dba sucessfully tested a backup to tape
> and restore using this agent.
> Then DR plan was written to make use of this paradigm.
> Fast forward 6 months
> New product in production, many open file errors on .mdf, .ndf, and .ldf
> files causing entrire backup streams to be delayed and eventually failing.
> Immediately resolved when file extensions are added to exlusion list.
> Does my old school thought process (exclude the files) lack a basis in the
> new landscape involving SQL 2K5 and new backup agents?
> Can anyone point to a MS document that recommends this as best practise?
> I have seen Microsoft KB article 309422 regarding anit virus files.
> Any input apprciated.