Finding latest full backup file name

We are instituting a procedure to restore the latest full backup (*.BAK
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
--
Larry Menzin
American Techsystems Corp.If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:
> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automated
> fashion. Is there a way to determine the pathname and filename of the latest
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||Just what I was looking for. Thanks a lot.
--
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:
> > We are instituting a procedure to restore the latest full backup (*.BAK
> > extension) of our production database to a training database in an automated
> > fashion. Is there a way to determine the pathname and filename of the latest
> > full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> > command manipulation to get this information from file timestamps? I would
> > then use T-SQL to restore the filename over the existing training database.
> > Any ideas?
> >
> > --
> > Larry Menzin
> > American Techsystems Corp.

Finding latest full backup file name

We are instituting a procedure to restore the latest full backup (*.BAK
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
Larry Menzin
American Techsystems Corp.
If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:

> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automated
> fashion. Is there a way to determine the pathname and filename of the latest
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.
|||Just what I was looking for. Thanks a lot.
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:

Finding latest full backup file name

We are instituting a procedure to restore the latest full backup (*.BAK
extension) of our production database to a training database in an automated
fashion. Is there a way to determine the pathname and filename of the latest
full backup of a database (from msdb perhaps). or do I need to use DOS Dir
command manipulation to get this information from file timestamps? I would
then use T-SQL to restore the filename over the existing training database.
Any ideas?
Larry Menzin
American Techsystems Corp.If you are not using multiple devices, may be this can help.
use msdb
go
select top 1
a.[name],
a.[database_name],
a.type,
a.backup_start_date,
a.backup_finish_date,
c.physical_device_name
from
dbo.backupset as a
inner join
dbo.backupmediaset as b
on a.media_set_id = b.media_set_id
inner join
dbo.backupmediafamily as c
on b.media_set_id = c.media_set_id
where
a.[database_name] = 'northwind'
and a.type = 'D'
order by
a.backup_finish_date desc
AMB
"Larry Menzin" wrote:

> We are instituting a procedure to restore the latest full backup (*.BAK
> extension) of our production database to a training database in an automat
ed
> fashion. Is there a way to determine the pathname and filename of the late
st
> full backup of a database (from msdb perhaps). or do I need to use DOS Dir
> command manipulation to get this information from file timestamps? I would
> then use T-SQL to restore the filename over the existing training database
.
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||Just what I was looking for. Thanks a lot.
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> If you are not using multiple devices, may be this can help.
> use msdb
> go
> select top 1
> a.[name],
> a.[database_name],
> a.type,
> a.backup_start_date,
> a.backup_finish_date,
> c.physical_device_name
> from
> dbo.backupset as a
> inner join
> dbo.backupmediaset as b
> on a.media_set_id = b.media_set_id
> inner join
> dbo.backupmediafamily as c
> on b.media_set_id = c.media_set_id
> where
> a.[database_name] = 'northwind'
> and a.type = 'D'
> order by
> a.backup_finish_date desc
>
> AMB
>
> "Larry Menzin" wrote:
>

Finding a tape drive

I am writing an interface for the SQL backup in my application. I am
currently trying to determine if a tape drive exists on the system so the
system can add it as a backup device. If I use enterprise manager to add
the tape drive as a device my application can see the device but still can't
see the tape drive directly. My system can see the hard drives and DVD
drive on my system. I have tried sp_helpdevices, xp_availablemedia, and
sysdevices.
Should I be looking somewhere else or does the user have to add the tape
from Enterprise manager first?
Regards,
JohnDepending on what interface you're developing, you can use SQL-DMO in
various languages to iterate through devices on your SQL Server and
tapes as well.
Here's a few helpful links that I found useful when I developed a SQL
Db management interface for the web.
http://msdn.microsoft.com/library/d...br />
3tlx.asp
http://sqldev.net/sqldmo.htm|||Thanks for the replay.
I have been implementing this via the SqlClient in C# using the basic
"BACKUP <db> TO <device>" interface. I obtained a list of devices from the
sys_devices or xp_availablemedia and xp_subdir which all seems to work fine
except I don't see the tape device unless it's added as a device.
I have never worked with SQL-DMO and the example seems to have problem
compiling which I assume is because it was written for an older version of
C# / .NET. What is the advantage of using the SQL-DMO as opposed to just
using the SqlClient built into C#?
Regards,
John
"sze" <szeying.tan@.gmail.com> wrote in message
news:1122596876.912374.115650@.z14g2000cwz.googlegroups.com...
> Depending on what interface you're developing, you can use SQL-DMO in
> various languages to iterate through devices on your SQL Server and
> tapes as well.
> Here's a few helpful links that I found useful when I developed a SQL
> Db management interface for the web.
> http://msdn.microsoft.com/library/d... />
b_3tlx.asp
> http://sqldev.net/sqldmo.htm
>|||you may have problems compiling if you did not reference sqldmo.dll in
your .NET project.
it should be somewhere in your sql server installation, something like
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ ...
as for advantages, i don't really know. SQL-DMO is an API for SQL
Server administration. SQLClient is a data provider for C#... i'm
usually partial to using an API when i want to interface with a
particular application... since the API encapsulates all objects
available in the particular application.. and in this case, all objects
available in SQL Server (this means everything you can do in Enterprise
Manager). but of course, it's really your call.|||Thanks again, the problem was the strong naming I already knew I needed to
link into it. I found a link for the strong naming problem on the microsoft
site so it works now.
Regards,
John|||EXECUTE master.dbo.xp_get_tape_devices seems to do the trick.
Regards,
Johnsql

Find the Data Bases which can not be Backed up are some resons

hi all.,
How can I find the Data Base which can't backed up for reasons
like Data Base is in the middle of the Restore before
i start Backup Process .
Regards.,
Sri
This probably isn't the best method to use but via Enterprise Manager
under Management you can view the maintenance plan history for the
backup plan and look for the red X by the database name that indicates
a failure for the operation against that database.
You might try selecting directly from the
msdb.dbo.sysdbmaintplan_history table if your plan logs to the
database.
Otherwise there should be log files in the LOG directory.
HTH -- Mark D Powell --
|||Hii Mark.,
Thanks for ur reply .
Can I Get the DataBase which couldn't be backuped using executing
Query or procedure .
I am new to Db management , hence pardon me if any mistakes .
Regards.,
Sridhar
Mark D Powell wrote:
> This probably isn't the best method to use but via Enterprise Manager
> under Management you can view the maintenance plan history for the
> backup plan and look for the red X by the database name that indicates
> a failure for the operation against that database.
> You might try selecting directly from the
> msdb.dbo.sysdbmaintplan_history table if your plan logs to the
> database.
> Otherwise there should be log files in the LOG directory.
> HTH -- Mark D Powell --

Find the Data Bases which can not be Backed up are some resons

hi all.,
How can I find the Data Base which can't backed up for reasons
like Data Base is in the middle of the Restore before
i start Backup Process .
Regards.,
SriThis probably isn't the best method to use but via Enterprise Manager
under Management you can view the maintenance plan history for the
backup plan and look for the red X by the database name that indicates
a failure for the operation against that database.
You might try selecting directly from the
msdb.dbo.sysdbmaintplan_history table if your plan logs to the
database.
Otherwise there should be log files in the LOG directory.
HTH -- Mark D Powell --|||Hii Mark.,
Thanks for ur reply .
Can I Get the DataBase which couldn't be backuped using executing
Query or procedure .
I am new to Db management , hence pardon me if any mistakes .
Regards.,
Sridhar
Mark D Powell wrote:
> This probably isn't the best method to use but via Enterprise Manager
> under Management you can view the maintenance plan history for the
> backup plan and look for the red X by the database name that indicates
> a failure for the operation against that database.
> You might try selecting directly from the
> msdb.dbo.sysdbmaintplan_history table if your plan logs to the
> database.
> Otherwise there should be log files in the LOG directory.
> HTH -- Mark D Powell --

Find the Data Bases which can not be Backed up are some resons

hi all.,
How can I find the Data Base which can't backed up for reasons
like Data Base is in the middle of the Restore before
i start Backup Process .
Regards.,
SriThis probably isn't the best method to use but via Enterprise Manager
under Management you can view the maintenance plan history for the
backup plan and look for the red X by the database name that indicates
a failure for the operation against that database.
You might try selecting directly from the
msdb.dbo.sysdbmaintplan_history table if your plan logs to the
database.
Otherwise there should be log files in the LOG directory.
HTH -- Mark D Powell --|||Hii Mark.,
Thanks for ur reply .
Can I Get the DataBase which couldn't be backuped using executing
Query or procedure .
I am new to Db management , hence pardon me if any mistakes .
Regards.,
Sridhar
Mark D Powell wrote:
> This probably isn't the best method to use but via Enterprise Manager
> under Management you can view the maintenance plan history for the
> backup plan and look for the red X by the database name that indicates
> a failure for the operation against that database.
> You might try selecting directly from the
> msdb.dbo.sysdbmaintplan_history table if your plan logs to the
> database.
> Otherwise there should be log files in the LOG directory.
> HTH -- Mark D Powell --sql

Find the Data Bases which can not be Backed up are some resons

hi all.,
How can I find the Data Base which can't backed up for reasons
like Data Base is in the middle of the Restore before
i start Backup Process .

Regards.,

SriThis probably isn't the best method to use but via Enterprise Manager
under Management you can view the maintenance plan history for the
backup plan and look for the red X by the database name that indicates
a failure for the operation against that database.

You might try selecting directly from the
msdb.dbo.sysdbmaintplan_history table if your plan logs to the
database.

Otherwise there should be log files in the LOG directory.

HTH -- Mark D Powell --|||Hii Mark.,

Thanks for ur reply .

Can I Get the DataBase which couldn't be backuped using executing
Query or procedure .

I am new to Db management , hence pardon me if any mistakes .

Regards.,

Sridhar

Mark D Powell wrote:
> This probably isn't the best method to use but via Enterprise Manager
> under Management you can view the maintenance plan history for the
> backup plan and look for the red X by the database name that indicates
> a failure for the operation against that database.
> You might try selecting directly from the
> msdb.dbo.sysdbmaintplan_history table if your plan logs to the
> database.
> Otherwise there should be log files in the LOG directory.
> HTH -- Mark D Powell --

Find out who deleted rows?

Yesterday, I had an occurence where someone (one of our developers :) ) deleted many vital rows in a database. I was able to recover via a backup but I'd like to see what was run to delete those rows, when and who if possible? I have the transaction log and the _log.ldf.

Can I read those somehow to find out this information? I have since added auditing to said tables, but I'd really like to go back and see what happened?

I do know about Lumigent, but is there a different production/solution?

Thoughts?

Thank youDid you add triggers? Write all transaction to a history table...|||Brett.

See above, "I have since added auditing to said tables, but I'd really like to go back and see what happened?"....

Thanks for the thoughts though....

Anybody else on the read log file or transaction log files?

find backup directory

Hello!
MSQL2000:
I want to make db-backup (maintenance) but haven't enough space on the
discs that are physically in the machine. I have enough on the network, but
i can't choose an network-drive, when
i want to specify the backup directory.

What to do?
/Bjrn"bdjensen" <b.d.jensenMAPS_ON@.gmx.net> wrote in message news:<3fb22d61$0$95067$edfadb0f@.dread11.news.tele.dk>...
> Hello!
> MSQL2000:
> I want to make db-backup (maintenance) but haven't enough space on the
> discs that are physically in the machine. I have enough on the network, but
> i can't choose an network-drive, when
> i want to specify the backup directory.
> What to do?
> /Bjrn

Enterprise Manager only displays the local drives, but you can just
type in the UNC path: \\Server\Share\Folder.

Simon|||Create a backup device in enterprise manager. You can backup to network
locations this way.|||"bdjensen" <b.d.jensenMAPS_ON@.gmx.net> wrote in message
news:3fb22d61$0$95067$edfadb0f@.dread11.news.tele.d k...
> Hello!
> MSQL2000:
> I want to make db-backup (maintenance) but haven't enough space on the
> discs that are physically in the machine. I have enough on the network,
but
> i can't choose an network-drive, when
> i want to specify the backup directory.

Note, as others have said, you can use UNC paths. However, SQL Server Agent
must be running in a user context that can map those drives w/o human
intervention.

> What to do?
> /Bjrn