Backup status/timestamp

Hi,
I was wondering if anyone had a sql script that can pull the last backup
timestamp as displayed when you check database properties in Enterprise
manager.
Thanks
RustyThe data you're after is stored in msdb, a query like this :-
select top 1 *
from msdb..backupset
where database_name = 'pubs' and type = 'D'
order by backup_finish_date desc
Have a look in SQL BOL for an explanation to what all the columns mean.
HTH. Ryan
"Rusty" <Rusty@.discussions.microsoft.com> wrote in message
news:2183A80E-9299-431D-A9D2-26E318B13E8A@.microsoft.com...
> Hi,
> I was wondering if anyone had a sql script that can pull the last backup
> timestamp as displayed when you check database properties in Enterprise
> manager.
> Thanks
> Rusty|||Thanks!
I found this as well while googling.
http://searchsqlserver.techtarget.c...1178991,00.html
This is perfect cause I can code a Cold Fusion web page for the help desk to
monitor.
But Im having a devil of a time getting it to run. The table thats supposed
to be populated by the stored procedure keeps coming up with nothing in it.
#tmp is populating normally. #tmp2 was coming up with no rows either. I foun
d
what I thought was a bug where it refers to inserting data into #t instead o
f
#tmp. I changed #t to #tmp and now #tmp2 is showing at least a field but no
data.
Now Im stuck.
"Ryan" wrote:

> The data you're after is stored in msdb, a query like this :-
> select top 1 *
> from msdb..backupset
> where database_name = 'pubs' and type = 'D'
> order by backup_finish_date desc
> Have a look in SQL BOL for an explanation to what all the columns mean.
> --
> HTH. Ryan
>
> "Rusty" <Rusty@.discussions.microsoft.com> wrote in message
> news:2183A80E-9299-431D-A9D2-26E318B13E8A@.microsoft.com...
>
>

Backup status/timestamp

Hi,
I was wondering if anyone had a sql script that can pull the last backup
timestamp as displayed when you check database properties in Enterprise
manager.
Thanks
RustyThe data you're after is stored in msdb, a query like this :-
select top 1 *
from msdb..backupset
where database_name = 'pubs' and type = 'D'
order by backup_finish_date desc
Have a look in SQL BOL for an explanation to what all the columns mean.
--
HTH. Ryan
"Rusty" <Rusty@.discussions.microsoft.com> wrote in message
news:2183A80E-9299-431D-A9D2-26E318B13E8A@.microsoft.com...
> Hi,
> I was wondering if anyone had a sql script that can pull the last backup
> timestamp as displayed when you check database properties in Enterprise
> manager.
> Thanks
> Rusty|||Thanks!
I found this as well while googling.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1178991,00.html
This is perfect cause I can code a Cold Fusion web page for the help desk to
monitor.
But Im having a devil of a time getting it to run. The table thats supposed
to be populated by the stored procedure keeps coming up with nothing in it.
#tmp is populating normally. #tmp2 was coming up with no rows either. I found
what I thought was a bug where it refers to inserting data into #t instead of
#tmp. I changed #t to #tmp and now #tmp2 is showing at least a field but no
data.
Now Im stuck.
"Ryan" wrote:
> The data you're after is stored in msdb, a query like this :-
> select top 1 *
> from msdb..backupset
> where database_name = 'pubs' and type = 'D'
> order by backup_finish_date desc
> Have a look in SQL BOL for an explanation to what all the columns mean.
> --
> HTH. Ryan
>
> "Rusty" <Rusty@.discussions.microsoft.com> wrote in message
> news:2183A80E-9299-431D-A9D2-26E318B13E8A@.microsoft.com...
> > Hi,
> >
> > I was wondering if anyone had a sql script that can pull the last backup
> > timestamp as displayed when you check database properties in Enterprise
> > manager.
> >
> > Thanks
> > Rusty
>
>

Backup SQL 2005 - Restore In SQL 2000

Hi,
I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
properties) and need to restore it back to another machine running SQL 2000
SP4. I am getting errors and cannot find a way to do it? Can any one help
please?
Thanks
MikeOne way to do that is running DTS Packages to transfer the data
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:987F2970-3600-4207-ACCB-E397C7F2AE6D@.microsoft.com...
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from
> the
> properties) and need to restore it back to another machine running SQL
> 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike|||sonicm wrote:
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
> properties) and need to restore it back to another machine running SQL 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike
You cannot restore backwards across versions like this... You'll have
to migrate the data and schema using import/export, DTS, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup retention period Setting

In sql2005 the database backup retention has been added in sql server properties in database setting.

In 2000 we had a comfortable option to set retention based on maintenance plan,files and also our space availabilty.It has helped the dba's a lot.But it has been removed in sql 2005.

Is that sql server setting is the only retention period setting or do we have to set in anyother tabs..

Thanks

There are two places where backup retention is specified:

First, as you discovered, there is an instance-wide default setting for backup media retention.

Secondly, that value can be overridden for any backup operation by using the "BACKUP DATABASE WITH EXPIREDATE = " option. or the WITH RETAINDAYS option.

Thus, you can specify for each backup, and each backup job, what the retention should be.