dumptrdate column gone in sql server 2000?

Hello,
I am running an environment that has Sybase and SQL Server 2000, and i've
been using the dumptrdate column from sysdatabases on all SYbase servers, but
it appears that it has been replaced by a Reserved column. Is there an
alternative to this in SQL server? I want to be able to query the database
from command line and see the last transaction log backup time. Viewing it in
taskpad works great, but i really need it in a command line. Any help is
greatly apprciated.
Thanks!
FSHow about the backup history tables in msdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"FS" <FS@.discussions.microsoft.com> wrote in message
news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
> Hello,
> I am running an environment that has Sybase and SQL Server 2000, and i've
> been using the dumptrdate column from sysdatabases on all SYbase servers, but
> it appears that it has been replaced by a Reserved column. Is there an
> alternative to this in SQL server? I want to be able to query the database
> from command line and see the last transaction log backup time. Viewing it in
> taskpad works great, but i really need it in a command line. Any help is
> greatly apprciated.
> Thanks!
> FS|||Thanks, i'll give this a try.
"Tibor Karaszi" wrote:
> How about the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
> > Hello,
> >
> > I am running an environment that has Sybase and SQL Server 2000, and i've
> > been using the dumptrdate column from sysdatabases on all SYbase servers, but
> > it appears that it has been replaced by a Reserved column. Is there an
> > alternative to this in SQL server? I want to be able to query the database
> > from command line and see the last transaction log backup time. Viewing it in
> > taskpad works great, but i really need it in a command line. Any help is
> > greatly apprciated.
> >
> > Thanks!
> > FS
>|||That worked, thanks.
Do you know how to modify the query to give me the last entry only?
so i have 9 databases that i want to generate a report for to list the last
transaction log dump. I tried putting a "where backup_start_date >= '<date
time>'
is there a better way?
"FS" wrote:
> Thanks, i'll give this a try.
>
> "Tibor Karaszi" wrote:
> > How about the backup history tables in msdb?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "FS" <FS@.discussions.microsoft.com> wrote in message
> > news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
> > > Hello,
> > >
> > > I am running an environment that has Sybase and SQL Server 2000, and i've
> > > been using the dumptrdate column from sysdatabases on all SYbase servers, but
> > > it appears that it has been replaced by a Reserved column. Is there an
> > > alternative to this in SQL server? I want to be able to query the database
> > > from command line and see the last transaction log backup time. Viewing it in
> > > taskpad works great, but i really need it in a command line. Any help is
> > > greatly apprciated.
> > >
> > > Thanks!
> > > FS
> >
> >|||Here are a couple of options, depending on how much information you need:
SELECT database_name, MAX(backup_start_date)
FROM dbo.backupset
GROUP BY database_name
SELECT b1.database_name, *
FROM dbo.backupset AS b1
INNER JOIN(
SELECT database_name, MAX(backup_start_date) AS max_date
FROM dbo.backupset
WHERE type = 'L'
GROUP BY database_name
) AS b2
ON b1.database_name = b2.database_name
AND b1.backup_start_date = b2.max_date
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"FS" <FS@.discussions.microsoft.com> wrote in message
news:AEBE819C-493A-4556-AF29-83505FF135E3@.microsoft.com...
> That worked, thanks.
> Do you know how to modify the query to give me the last entry only?
> so i have 9 databases that i want to generate a report for to list the last
> transaction log dump. I tried putting a "where backup_start_date >= '<date
> time>'
> is there a better way?
> "FS" wrote:
>> Thanks, i'll give this a try.
>>
>> "Tibor Karaszi" wrote:
>> > How about the backup history tables in msdb?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://sqlblog.com/blogs/tibor_karaszi
>> >
>> >
>> > "FS" <FS@.discussions.microsoft.com> wrote in message
>> > news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
>> > > Hello,
>> > >
>> > > I am running an environment that has Sybase and SQL Server 2000, and i've
>> > > been using the dumptrdate column from sysdatabases on all SYbase servers, but
>> > > it appears that it has been replaced by a Reserved column. Is there an
>> > > alternative to this in SQL server? I want to be able to query the database
>> > > from command line and see the last transaction log backup time. Viewing it in
>> > > taskpad works great, but i really need it in a command line. Any help is
>> > > greatly apprciated.
>> > >
>> > > Thanks!
>> > > FS
>> >
>> >|||That first select statement worked perfectly. Thank you!
"Tibor Karaszi" wrote:
> Here are a couple of options, depending on how much information you need:
> SELECT database_name, MAX(backup_start_date)
> FROM dbo.backupset
> GROUP BY database_name
> SELECT b1.database_name, *
> FROM dbo.backupset AS b1
> INNER JOIN(
> SELECT database_name, MAX(backup_start_date) AS max_date
> FROM dbo.backupset
> WHERE type = 'L'
> GROUP BY database_name
> ) AS b2
> ON b1.database_name = b2.database_name
> AND b1.backup_start_date = b2.max_date
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:AEBE819C-493A-4556-AF29-83505FF135E3@.microsoft.com...
> > That worked, thanks.
> > Do you know how to modify the query to give me the last entry only?
> > so i have 9 databases that i want to generate a report for to list the last
> > transaction log dump. I tried putting a "where backup_start_date >= '<date
> > time>'
> > is there a better way?
> >
> > "FS" wrote:
> >
> >> Thanks, i'll give this a try.
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> > How about the backup history tables in msdb?
> >> >
> >> > --
> >> > Tibor Karaszi, SQL Server MVP
> >> > http://www.karaszi.com/sqlserver/default.asp
> >> > http://sqlblog.com/blogs/tibor_karaszi
> >> >
> >> >
> >> > "FS" <FS@.discussions.microsoft.com> wrote in message
> >> > news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
> >> > > Hello,
> >> > >
> >> > > I am running an environment that has Sybase and SQL Server 2000, and i've
> >> > > been using the dumptrdate column from sysdatabases on all SYbase servers, but
> >> > > it appears that it has been replaced by a Reserved column. Is there an
> >> > > alternative to this in SQL server? I want to be able to query the database
> >> > > from command line and see the last transaction log backup time. Viewing it in
> >> > > taskpad works great, but i really need it in a command line. Any help is
> >> > > greatly apprciated.
> >> > >
> >> > > Thanks!
> >> > > FS
> >> >
> >> >
>