How to determine which file belongs to which filegroup and which

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<chayancse@.gmail.com> wrote in message
news:3b6adb60-0322-4f4b-973b-a9fbee6dd0b0@.e10g2000prf.googlegroups.com...
> 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
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
--
David Portas|||On Feb 7, 12:18 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <chayan...@.gmail.com> wrote in message
> news:3b6adb60-0322-4f4b-973b-a9fbee6dd0b0@.e10g2000prf.googlegroups.com...
> > 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
> 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
> --
> 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|||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
"chayancse@.gmail.com" wrote:
> On Feb 7, 12:18 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> > <chayan...@.gmail.com> wrote in message
> >
> > news:3b6adb60-0322-4f4b-973b-a9fbee6dd0b0@.e10g2000prf.googlegroups.com...
> >
> > > 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
> >
> > 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
> >
> > --
> > 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
>|||Hi
> 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
<chayancse@.gmail.com> wrote in message
news:e2777758-af6e-4263-8f06-89c3fa692a33@.m34g2000hsb.googlegroups.com...
> On Feb 7, 12:18 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
>> <chayan...@.gmail.com> wrote in message
>> news:3b6adb60-0322-4f4b-973b-a9fbee6dd0b0@.e10g2000prf.googlegroups.com...
>> > 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
>> 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
>> --
>> 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|||<chayancse@.gmail.com> wrote in message
news:e2777758-af6e-4263-8f06-89c3fa692a33@.m34g2000hsb.googlegroups.com...
> 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?
>
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.
--
David Portas|||On Feb 8, 1:30 am, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <chayan...@.gmail.com> wrote in message
> news:e2777758-af6e-4263-8f06-89c3fa692a33@.m34g2000hsb.googlegroups.com...
>
> > 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?
> 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.
> --
> 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