How to determine which file belongs to which filegroup and which database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chayancse@gmail.com - 07 Feb 2008 05:42 GMT

Hi,

I am writing a C++ program that will take backup of .mdf files. I will
be provided with .mdf files. And for that mdf file, I need to know
the database name and file group name associated with this .mdf file.

How can I do it? Please help me to sort this out.

Thanks
Iftekhar

Reply to this Message

David Portas - 07 Feb 2008 06:18 GMT

> Hi,
>
[quoted text clipped - 6 lines]
> Thanks
> Iftekhar

You don't need that information to do backups because the BACKUP command
will take care of everything for you. Don't attempt to access or backup the
MDF/LDF files directly because you can't guarantee a good backup that way
without taking the database offline.

The information you are looking for is in these three system tables:

sys.database_files
sys.filegroups
sys.data_spaces

Signature

David Portas

Reply to this Message

chayancse@gmail.com - 07 Feb 2008 07:04 GMT

On Feb 7, 12:18 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:

> <chayan...@gmail.com> wrote in message
>
[quoted text clipped - 24 lines]
> --
> David Portas

Dear David,

Thanks for your reply. The system tables you have mentioned will help
me to determine the physical files associated with a database. But i
need to do the reverse task. I need to determine the logical database
name from a physical file name. Can you help me with this?

Also, is there any way to directly backup .mdf files without knowing
the database name?

Thanks
Iftekhar

Reply to this Message

Ben Nevarez - 07 Feb 2008 07:28 GMT

Hi Iftekhar,

As David mentioned, a backup of the files of an online database is not a
valid backup.

Use the T-SQL BACKUP command instead.

Hope this helps,

Ben Nevarez

> On Feb 7, 12:18 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
[quoted text clipped - 39 lines]
> Thanks
> Iftekhar

Reply to this Message

Uri Dimant - 07 Feb 2008 07:50 GMT

> I need to determine the logical database
> name from a physical file name. Can you help me with this?

It can be a problem. I have seen ( only one time) when the person rename the
physical file name and it was not associated with the name of database

> On Feb 7, 12:18 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
[quoted text clipped - 40 lines]
> Thanks
> Iftekhar

Reply to this Message

David Portas - 07 Feb 2008 19:30 GMT

> Dear David,
>
[quoted text clipped - 5 lines]
> Also, is there any way to directly backup .mdf files without knowing
> the database name?

That doesn't make much sense to me. You should be backing up DATABASES not
FILES. Why would you want to backup a file without knowing what database it
belonged to?

Find the database name here:

SELECT
DB_NAME(database_id) database_name,
physical_name
FROM master.sys.master_files;

Now use BACKUP to backup the database. Don't attempt to backup the file. If
you don't understand the difference then please study the topics on Backup
and Restore in Books Online.

Signature

David Portas

Reply to this Message

chayancse@gmail.com - 17 Mar 2008 07:12 GMT

On Feb 8, 1:30 am, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:

> <chayan...@gmail.com> wrote in message
>
[quoted text clipped - 27 lines]
> --
> David Portas

Thanks David. My task has been done. It's really unusual to backup
files instead of database. But I am developing a file backup system.
That will backup all normal files. If any of them are database files,
still they must be backed up.

Anyway, it's your post that actually helped me. So thanks a lot.

Thanks
Iftekhar

Reply to this Message