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 exper
iencing database corruptions these days. Those database names just started looking suspicious to me.
I would greatly appreciate any insights or pointers.
Bing
The 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:

> 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...
> several different ways:
> 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.
>
>
|||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
|||Good points., Wayne. Just like you said, a disk drive just went belly up not long after I noticed a lot errors I've never seen in the SQL server logs.
Thanks,
Bing
"Wayne Snyder" wrote:

> 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...
> several different ways:
> 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.
>
>

inconsistent database naming scheme?

In my physical database directory, I found the databases were named in sever
al 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 pa
rts of a name? I do not remember how the names of these databases originall
y looked like. We're exper
iencing 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:

> 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 th
e
> database name as the first part of the file name. The remainder of the fi
le
> name is up to you. Personally, I add a number to make the file name uniqu
e
> 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...
> several different ways:
> and .ldf are generally standard suffix for database names. What about oth
er
> parts of a name? I do not remember how the names of these databases
> originally looked like. We're experiencing database corruptions these day
s.
> Those database names just started looking suspicious to me.
>
>|||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|||Good points., Wayne. Just like you said, a disk drive just went belly up no
t long after I noticed a lot errors I've never seen in the SQL server logs.
Thanks,
Bing
"Wayne Snyder" wrote:

> 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. G
et
> 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...
> several different ways:
> and .ldf are generally standard suffix for database names. What about oth
er
> parts of a name? I do not remember how the names of these databases
> originally looked like. We're experiencing database corruptions these day
s.
> Those database names just started looking suspicious to me.
>
>

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 ActiveDirectory Data in a SQL query

Hi, I am hoping someone may have tried this before.......

Our application takes user details from Active directory, and stores the Guid in the database against an autonumber field for an easy to use userid. Any time the application wants to know anything about the user, it gets the information from Active Directory based upon the stored Guid.

I am writing a query to be used in generating reports, so I don't want to use .NET, Only SQL. I would like to be able to extract the username from Active Directory using SQL, so that the user's name, and not just their ID can be used in the report.

So far I have been able to extract all of my users names and their Guids from Active Directory using SQL, and I can extract the user Guid from our database. The problem I am having is comparing the 2! Visually they look the same, however the datatypes are different. If I convert the ActiveDirectory Guid to varchar I get gobbledegook, and if I convert the stored database Guid to varbinary then it's value is changed.

The query as it stands is below:

SELECT convert(varchar(50), [Name]) as FullName,objectGUID,ADSPath
FROM openquery(ADSI, 'SELECT name, objectGuid, ADSPath
FROM ''<LDAP Path>'' WHERE objectClass = ''User''')
WHERE objectGuid in(Select ADObjectGUID FROM users WHERE UserId='1')

I am working with SQL Server 2000 - as many of our clients are still using this system, so solutions based on SQL Server 2005 would not be practical. (I beleive there are ways of running .NET code from SQL 2005 which would solve this problem)

Any ideas anyone has would be much appreciated

Thanks

Gillian

Have you tried converting them to the uniqueidentifier datatype in SQL Server? That is the GUID datatype in SQL Server.|||

Thanks Cam - that did the trick.

For anyone trying to do the same kind of thing, the query looks like:

SELECT CONVERT(varchar(50), Rowset_2.name) AS FullName, Users.UserID

FROM OPENQUERY(ADSI, 'select name, objectGuid, ADSPath

from''<LDAP Path inserted here>''

whereobjectClass = ''User''') Rowset_2

INNER JOIN Users ON CONVERT (uniqueIdentifier, Rowset_2.objectGuid) = Users.ADObjectGUID

Cheers

Gillian