define Hierarchy: writing

Showing posts with label writing. Show all posts

Friday, March 30, 2012

How to determine which file belongs to which filegroup and whichdatabase

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

Monday, March 26, 2012

How to determine programatically what datafiles are associated with a database

I'm writing a data collection script to gather database and data file
information. This information will be used for reporting and analysis. I
want to be able to tell what data files are associated with each of the
databases. sysfiles doesn't include the database name and sysaltfiles
doesn't include all of the datafiles.
Query I'm using:
use master
select f.file_name, f.name, ...
from master.dbo.sysaltfiles a ,
dbo.sysfiles f ,
master.dbo.sysdatabases db
where a.name=f.name
and a.fileid = f.fileid
and a.dbid = db.dbid
and db.name = 'master'
I can probably infer the database name from the name column but I'd rather
not go there!
Please don't tell me to go somewhere and point and click. I'm monitoring
almost 70 databases and this data needs to be collected on a daily basis.
Message posted via http://www.webservertalk.comevelyn,
use sp_msforeachdb:
exec sp_msforeachdb '
select db.name, f.name
from master.dbo.sysaltfiles a ,
?.dbo.sysfiles f ,
master.dbo.sysdatabases db
where a.name=f.name
and a.fileid = f.fileid
and a.dbid = db.dbid '
hth
dean
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:eebc4237a7204ec3a8b8ca5fab4890a5@.SQ
webservertalk.com...
> I'm writing a data collection script to gather database and data file
> information. This information will be used for reporting and analysis. I
> want to be able to tell what data files are associated with each of the
> databases. sysfiles doesn't include the database name and sysaltfiles
> doesn't include all of the datafiles.
> Query I'm using:
> use master
> select f.file_name, f.name, ...
> from master.dbo.sysaltfiles a ,
> dbo.sysfiles f ,
> master.dbo.sysdatabases db
> where a.name=f.name
> and a.fileid = f.fileid
> and a.dbid = db.dbid
> and db.name = 'master'
> I can probably infer the database name from the name column but I'd rather
> not go there!
> Please don't tell me to go somewhere and point and click. I'm monitoring
> almost 70 databases and this data needs to be collected on a daily basis.
> --
> Message posted via http://www.webservertalk.com|||Try this:
exec sp_MSforeachdb
'
use [?]
select so.name as Table_Name,
sfg.groupname as GroupName,
sf.name as LogicalName,
sf.filename as PhysicalFileName
from dbo.sysobjects so
inner join sysindexes si
on so.id = si.id
inner join sysfilegroups sfg
on si.groupid = sfg.groupid
inner join sysfiles sf
on sf.groupid = sfg.groupid
group by so.name,
sfg.groupname,
sf.name,
sf.filename
'|||For this query add brackets around the question mark. i.e. [?]|||What does sp_msforeachdb do? It is not in my online book. I hesitate to
run something in 70 production databases without knowing the possible
ramifications.
Message posted via http://www.webservertalk.com|||I figured out what the procedure does.
Perhaps I'm not clear I want the database name and all associated data file
names. The following query gives me table names.
select so.name as Table_Name,
sfg.groupname as GroupName,
sf.name as LogicalName,
sf.filename as PhysicalFileName
from dbo.sysobjects so
inner join sysindexes si
on so.id = si.id
inner join sysfilegroups sfg
on si.groupid = sfg.groupid
inner join sysfiles sf
on sf.groupid = sfg.groupid
group by so.name,
sfg.groupname,
sf.name,
sf.filename
Message posted via http://www.webservertalk.com|||it is undocumented and unsupported, meaning that ms might choose not to
include it in future versions of sql server. however, it is widely used. you
can check the definition (in master db) with:
exec sp_helptext 'sp_msforeachdb'
nothing fancy (builds a cursor internallly), but very handy.
dean
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:f3affb3bea874a3fbf0edb3bcc8b8868@.SQ
webservertalk.com...
> What does sp_msforeachdb do? It is not in my online book. I hesitate to
> run something in 70 production databases without knowing the possible
> ramifications.
> --
> Message posted via http://www.webservertalk.com|||Hi
The database may have two or more log files. You also need to see them
Look at this script helps you.
SELECT RTRIM(filename)
FROM sysfiles
WHERE fileid = (SELECT MIN(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename)
FROM sysfiles
WHERE fileid > (SELECT MIN(fileid) FROM sysfiles) AND
fileid < (SELECT MAX(fileid) FROM sysfiles)
UNION ALL
SELECT RTRIM(filename) FROM sysfiles
WHERE fileid = (SELECT MAX(fileid) FROM sysfiles)
"Evelyn Schwartz via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:960a4cbd69a241458a0b54a9fd0b14ea@.SQ
webservertalk.com...
> I figured out what the procedure does.
> Perhaps I'm not clear I want the database name and all associated data
file
> names. The following query gives me table names.
> select so.name as Table_Name,
> sfg.groupname as GroupName,
> sf.name as LogicalName,
> sf.filename as PhysicalFileName
> from dbo.sysobjects so
> inner join sysindexes si
> on so.id = si.id
> inner join sysfilegroups sfg
> on si.groupid = sfg.groupid
> inner join sysfiles sf
> on sf.groupid = sfg.groupid
> group by so.name,
> sfg.groupname,
> sf.name,
> sf.filename
> --
> Message posted via http://www.webservertalk.com

Wednesday, March 7, 2012

How to Delete Records that are Linked with Relationships

Hello,
I am writing to ask if someone can tell me what the
command is to delete rows in an SQL 2000 database that are
linked through a foreign key relationship.
For example, I have a row in a "Persons" table that has a
primary key "Person ID". "Person ID" is then a foreign
key in two other tables. I would like to be able to
delete a person row from the "Persons" table and then
automatically have all associated rows based on
that "Person ID" in the other two tables deleted.
Thanks in advance!
MikeIn the design for the Persons table, open the relationship and make sure
'cascade delete' is on. This should do what you're asking...
Hope this helps...
"Mike Rogan" <mrogan@.carolinawebdev.com> wrote in message
news:046101c35559$bd142450$a401280a@.phx.gbl...
> Hello,
> I am writing to ask if someone can tell me what the
> command is to delete rows in an SQL 2000 database that are
> linked through a foreign key relationship.
> For example, I have a row in a "Persons" table that has a
> primary key "Person ID". "Person ID" is then a foreign
> key in two other tables. I would like to be able to
> delete a person row from the "Persons" table and then
> automatically have all associated rows based on
> that "Person ID" in the other two tables deleted.
> Thanks in advance!
> Mike