define Hierarchy: backup

Showing posts with label backup. 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

Wednesday, March 28, 2012

How to determine when the last successful full backup occurred?

Does anyone know how one might, from within a SQL query, determine when the
last successful full backup occurred. I can see this information within
Enterprise Manager (or Management Studio in this case) but I'd like to take
that information and use it to execute a cleanup task to delete all
transaction log backups prior to that last full backup.Look in the backupfile table in msdb database to find the backup
information.
If it is a scheduled job, look in the sysjobhistory table in msdb database.
The run_status value of 1 denotes a successful job completion.
Anith|||here is some code that i use for determining the recent full and differentia
l
backups from the msdb. all you have to do is supply the name of the database
,
and you can use that to get back the pertinent information
select @.backup_set_full = max(bs.backup_set_id)
from msdb.dbo.backupset bs
where bs.database_name = @.dbname
and bs.type = 'D' --for full Database backup
and bs.server_name = @.@.servername
select @.backup_set_diff = max(bs.backup_set_id)
from msdb.dbo.backupset bs
where bs.database_name = @.dbname
and bs.type = 'I' --for latest differential ('I'ncremental)
and bs.backup_set_id > @.backup_set_full
and bs.server_name = @.@.servername
using the backup_set_id, you can then run the following to determine the
physical file name:
select @.physical_device_name_bak=
'"'+convert(varchar(200),bmf.physical_device_name)+'"'
from msdb.dbo.backupset bs, msdb.dbo.backupmediafamily bmf
where bs.media_set_id = bmf.media_set_id
and ((bs.backup_set_id = @.backup_set_full)) -- need full
and bs.server_name = @.@.servername
HTH
Thomas LaRock
Database Administrator
ING Investment Management
"Michael D'Angelo" wrote:

> Does anyone know how one might, from within a SQL query, determine when th
e
> last successful full backup occurred. I can see this information within
> Enterprise Manager (or Management Studio in this case) but I'd like to tak
e
> that information and use it to execute a cleanup task to delete all
> transaction log backups prior to that last full backup.
>
>|||Thanks for the help, I was able to come up with the following query to do
it...
DECLARE @.last datetime
DECLARE @.dbid int
DECLARE @.name nvarchar(255)
DECLARE @.path nvarchar(1024)
CREATE TABLE #temp_db
(name nvarchar(255),
dbid int)
INSERT INTO #temp_db
SELECT name,dbid FROM master..sysdatabases
WHILE ((SELECT COUNT(*) FROM #temp_db) > 0)
BEGIN
SET @.dbid = (SELECT MIN(dbid) FROM #temp_db)
SET @.name = (SELECT name FROM #temp_db WHERE dbid = @.dbid)
SET @.last = (
SELECT CAST(MAX(backup_start_date) as datetime)
FROM backupset
WHERE type = 'D'
AND database_name = @.name)
SET @.path = N'X:\pathtobackupdir' + @.name
PRINT 'Removing data from ' + @.path + ' before ' + CAST(@.last as
nvarchar(1024))
EXECUTE master.dbo.xp_delete_file 0,@.path,N'trn',@.last
DELETE FROM #temp_db WHERE dbid = @.dbid
END
DROP TABLE #temp_db
"Thomas LaRock" <thomas.larock@.discussions.microsoft.com> wrote in message
news:54E3BE74-D1FF-4101-823B-FD75FDB70F8F@.microsoft.com...
> here is some code that i use for determining the recent full and
> differential
> backups from the msdb. all you have to do is supply the name of the
> database,
> and you can use that to get back the pertinent information
> select @.backup_set_full = max(bs.backup_set_id)
> from msdb.dbo.backupset bs
> where bs.database_name = @.dbname
> and bs.type = 'D' --for full Database backup
> and bs.server_name = @.@.servername
> select @.backup_set_diff = max(bs.backup_set_id)
> from msdb.dbo.backupset bs
> where bs.database_name = @.dbname
> and bs.type = 'I' --for latest differential ('I'ncremental)
> and bs.backup_set_id > @.backup_set_full
> and bs.server_name = @.@.servername
>
> using the backup_set_id, you can then run the following to determine the
> physical file name:
> select @.physical_device_name_bak=
> '"'+convert(varchar(200),bmf.physical_device_name)+'"'
> from msdb.dbo.backupset bs, msdb.dbo.backupmediafamily bmf
> where bs.media_set_id = bmf.media_set_id
> and ((bs.backup_set_id = @.backup_set_full)) -- need full
> and bs.server_name = @.@.servername
> HTH
>
> --
> Thomas LaRock
> Database Administrator
> ING Investment Management
>
> "Michael D'Angelo" wrote:
>

Wednesday, March 7, 2012

how to delete the day before bak file created by sql

the below script in sql backup runs fine, however i need it to delete the 1
day prior bak first, before it starts the next day. its like this for
redundicy. but i dont have the space to perform the next day job because the
day before is on the drive. we bakup to tape every night. so we have a copy
of the bak file. i just need to buy some time before i replace the drive
sizes.
EXECUTE master..xp_sqlmaint '-D
PracticeManager -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia
DISK -BkUpDB -UseDefDir -BkExt "BAK" -VrfyBackup -DelBkUps
1DAYS -UpdOptiStats 15'
could i just create a new job to just delbkups 1 days ... and arange it to
go first (1) and on success goto (2)
please help
Am sure this can be done in a round about way. Can use xp_cmdshell to delete
the file before initiating the backup call. this way your old backup is
deleted and the backup process can succeed.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"AA" <jgrace@.digitelusa.net> wrote in message
news:%23haziuX1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> the below script in sql backup runs fine, however i need it to delete the
1
> day prior bak first, before it starts the next day. its like this for
> redundicy. but i dont have the space to perform the next day job because
the
> day before is on the drive. we bakup to tape every night. so we have a
copy
> of the bak file. i just need to buy some time before i replace the drive
> sizes.
> EXECUTE master..xp_sqlmaint '-D
> PracticeManager -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB -UseDefDir -BkExt "BAK" -VrfyBackup -DelBkUps
> 1DAYS -UpdOptiStats 15'
>
> could i just create a new job to just delbkups 1 days ... and arange it to
> go first (1) and on success goto (2)
>
> please help
>
|||In this situation we run a job that uses xp_cmdshell to delete the backup
file before our xp_sqlmaint is run. Might be a nice feature if the backup
job could offer to delete old backups before running a new backup.
Chris Wood
Alberta Department of Energy
CANADA
"AA" <jgrace@.digitelusa.net> wrote in message
news:%23haziuX1EHA.2716@.TK2MSFTNGP14.phx.gbl...
> the below script in sql backup runs fine, however i need it to delete the
1
> day prior bak first, before it starts the next day. its like this for
> redundicy. but i dont have the space to perform the next day job because
the
> day before is on the drive. we bakup to tape every night. so we have a
copy
> of the bak file. i just need to buy some time before i replace the drive
> sizes.
> EXECUTE master..xp_sqlmaint '-D
> PracticeManager -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia
> DISK -BkUpDB -UseDefDir -BkExt "BAK" -VrfyBackup -DelBkUps
> 1DAYS -UpdOptiStats 15'
>
> could i just create a new job to just delbkups 1 days ... and arange it to
> go first (1) and on success goto (2)
>
> please help
>
|||Chris Wood wrote:
> In this situation we run a job that uses xp_cmdshell to delete the backup
> file before our xp_sqlmaint is run. Might be a nice feature if the backup
> job could offer to delete old backups before running a new backup.
Hi,
It is not a good idea to remove the previous backup before you know the
new backup is ok. If the new backup fails you don't have a spare one.
Jo.
|||Very true. But in the case of not enough disk space for more than 1 backup
and the backup file backed up to some other media it could still be a nice
option of the backup plan.
Chris Wood
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:e4fS8hu1EHA.2572@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
>
> Chris Wood wrote:
backup[vbcol=seagreen]
backup
> Hi,
> It is not a good idea to remove the previous backup before you know the
> new backup is ok. If the new backup fails you don't have a spare one.
> Jo.

Friday, February 24, 2012

how to delete distribution database

Hi Friends,
SQL 2000
I resotred a backup of distribution db as d21 and now when I am tryiong to
delete this db , it does not let me to do so, saying , database in
replication.
How can I delete the d21 database ?
The repplication is on and going now.
Thanks,
PatrickHi
Have you run sp_removedbreplication ?
John
"Patrick" wrote:

> Hi Friends,
> SQL 2000
> I resotred a backup of distribution db as d21 and now when I am tryiong to
> delete this db , it does not let me to do so, saying , database in
> replication.
> How can I delete the d21 database ?
> The repplication is on and going now.
>
> Thanks,
> Patrick
>
>|||Yes I did, and stil it doesn't let me to delete the d21 database.
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:4B80A39F-2A2D-4C5D-B395-1F8925305BC5@.microsoft.com...
> Hi
> Have you run sp_removedbreplication ?
> John
> "Patrick" wrote:
>|||Hi
You may want to look at step 2 in
http://support.microsoft.com/kb/190797/EN-US/
John
"Patrick" wrote:

> Yes I did, and stil it doesn't let me to delete the d21 database.
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:4B80A39F-2A2D-4C5D-B395-1F8925305BC5@.microsoft.com...
>
>