Check if im backing up my databases

Is there a way I can find out if my databases are being backed up daily on a
daily basis on a server ?
I want to be able to run a script and list all databases not being backed up
dailyHassan
If you build database maintenance plan and check all user database , you
are sure that all databases are being backed up
as well as theit log files (I assume with FULL recovery mode)
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Something like
select d.name as [Database],
max(b.backup_finish_date) as [Last Backup],
case
when datediff(hh,isnull(max(b.backup_finish_date),'19000101'),getdate()) >
24
then 'N' else 'Y' end as [Recent Full Backup]
from master.dbo.sysdatabases d
left join msdb.dbo.backupset b
on d.name = b.database_name and b.type = 'D'
group by d.name
order by d.name
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Hi
ALTER DATABASE DataBase SET RECOVERY FULL
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
> Something like
> select d.name as [Database],
> max(b.backup_finish_date) as [Last Backup],
> case
> when datediff(hh,isnull(max(b.backup_finish_date),'19000101'),getdate()) >
> 24
> then 'N' else 'Y' end as [Recent Full Backup]
> from master.dbo.sysdatabases d
> left join msdb.dbo.backupset b
> on d.name = b.database_name and b.type = 'D'
> group by d.name
> order by d.name
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>> Is there a way I can find out if my databases are being backed up daily
>> on a daily basis on a server ?
>> I want to be able to run a script and list all databases not being backed
>> up daily
>|||Sorry, wrong post
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1uO608AGHA.4036@.TK2MSFTNGP10.phx.gbl...
> Hi
> ALTER DATABASE DataBase SET RECOVERY FULL
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
>> Something like
>> select d.name as [Database],
>> max(b.backup_finish_date) as [Last Backup],
>> case
>> when datediff(hh,isnull(max(b.backup_finish_date),'19000101'),getdate())
>> > 24
>> then 'N' else 'Y' end as [Recent Full Backup]
>> from master.dbo.sysdatabases d
>> left join msdb.dbo.backupset b
>> on d.name = b.database_name and b.type = 'D'
>> group by d.name
>> order by d.name
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>> Is there a way I can find out if my databases are being backed up daily
>> on a daily basis on a server ?
>> I want to be able to run a script and list all databases not being
>> backed up daily
>>
>

Check if im backing up my databases

Is there a way I can find out if my databases are being backed up daily on a
daily basis on a server ?
I want to be able to run a script and list all databases not being backed up
daily
Hassan
If you build database maintenance plan and check all user database , you
are sure that all databases are being backed up
as well as theit log files (I assume with FULL recovery mode)
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>
|||Something like
select d.name as [Database],
max(b.backup_finish_date) as [Last Backup],
case
when datediff(hh,isnull(max(b.backup_finish_date),'1900 0101'),getdate()) >
24
then 'N' else 'Y' end as [Recent Full Backup]
from master.dbo.sysdatabases d
left join msdb.dbo.backupset b
on d.name = b.database_name and b.type = 'D'
group by d.name
order by d.name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>
|||Hi
ALTER DATABASE DataBase SET RECOVERY FULL
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
> Something like
> select d.name as [Database],
> max(b.backup_finish_date) as [Last Backup],
> case
> when datediff(hh,isnull(max(b.backup_finish_date),'1900 0101'),getdate()) >
> 24
> then 'N' else 'Y' end as [Recent Full Backup]
> from master.dbo.sysdatabases d
> left join msdb.dbo.backupset b
> on d.name = b.database_name and b.type = 'D'
> group by d.name
> order by d.name
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>
|||Sorry, wrong post
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1uO608AGHA.4036@.TK2MSFTNGP10.phx.gbl...
> Hi
> ALTER DATABASE DataBase SET RECOVERY FULL
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
>

Check if im backing up my databases

Is there a way I can find out if my databases are being backed up daily on a
daily basis on a server ?
I want to be able to run a script and list all databases not being backed up
dailyHassan
If you build database maintenance plan and check all user database , you
are sure that all databases are being backed up
as well as theit log files (I assume with FULL recovery mode)
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Something like
select d.name as [Database],
max(b.backup_finish_date) as [Last Backup],
case
when datediff(hh,isnull(max(b. backup_finish_date),'19000101'),getdate(
)) >
24
then 'N' else 'Y' end as [Recent Full Backup]
from master.dbo.sysdatabases d
left join msdb.dbo.backupset b
on d.name = b.database_name and b.type = 'D'
group by d.name
order by d.name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Hi
ALTER DATABASE DataBase SET RECOVERY FULL
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
> Something like
> select d.name as [Database],
> max(b.backup_finish_date) as [Last Backup],
> case
> when datediff(hh,isnull(max(b. backup_finish_date),'19000101'),getdate(
)) >
> 24
> then 'N' else 'Y' end as [Recent Full Backup]
> from master.dbo.sysdatabases d
> left join msdb.dbo.backupset b
> on d.name = b.database_name and b.type = 'D'
> group by d.name
> order by d.name
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>|||Sorry, wrong post
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1uO608AGHA.4036@.TK2MSFTNGP10.phx.gbl...
> Hi
> ALTER DATABASE DataBase SET RECOVERY FULL
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
>

Check for a file before executing DTS

Thanks in advance for any help offered!

I am having a text file sent from another location on a daily basis. Suppposely @. 2:00 am. I created a DTS to bring the data into a local table (5:00AM). The data I am bringing in replaces the old data. So what happens in the DTS package is all data is dropped from the table and then the new data is inserted.

I then have another job run that runs later in the day before the next incoming data arrives (7:00PM). This job deletes the old text file so that it is not appended the next time the text file is sent to me.

The problem is that for the last two days, the server sending the text file did not send the files before my local jobs run (up to 9:00AM and 7:00AM). Thus my 7:00PM job has deleted the old text file. Then later my job that calls the DTS runs (5:00AM); it then drops all data and then tries to load new data that is not there because my 7:00PM job deleted the text file and the their 2:00AM has not delevered the new text file.

My question is; what is the best way to script a job that checks to see if the text file exist before dropping the existing table?

I know that the whole process could be handled better if both the supplier of the data and me the end user could be more flexible. The problem is that the sender is going to send the data in the manner (time and method) that requires the least amount of work for them. I just need to deal with it.
Thanks,
LeeWhat type of text file ? How are you processing it now in the your dts script ? What steps are currently being used in your dts script ?|||hi

I had the same problem with a daily based data import DTS I created. Here's the way I solved it:

Open your DTS in design view. Add an ActiveX component, written in vb script:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Set MyFile = CreateObject("Scripting.FileSystemObject")

If MyFile.FileExists(local_server_path) Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
___________________________________________
___________________________________________

'local_server_path' is your file path on the server.

Use the 'onSuccess' event to start the rest of your DTS.

Hope it helps.

Check database healthiness on a daily basis?

How do people usually do to make sure all the databases are in good status?
We have about 50 databases. I was intended to write 'dbcc checkdb' for each
one in a T-SQL script and have it run every day. Is there a better way to
do that?
Thanks in advance for any advices.
BingHi,
Yes, that will be a better option to check and confirm that your database is
good. DBCC CHECKDB will run for a long time if your database is
big. In that case probably you can do this activity weekly once during non
peak hours (weekends).
Along with this you can also run UPDATE STATISTICS daily on those tables
which have high DML access (Insert/ Update/ Delete).
Monthly once check the fragmentation of table using DBCC
SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
THis will remove the fragmentation and increase the performance.
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> How do people usually do to make sure all the databases are in good
status?
> We have about 50 databases. I was intended to write 'dbcc checkdb' for
each one in a T-SQL script and have it run every day. Is there a better way
to do that?
> Thanks in advance for any advices.
> Bing|||Thanks so much for your instance response, Hari. Not just for this one. Yo
u have answered a lot of my questions I posted previously. They are all ver
y helpful. I really appreciate your knowledgement and your kindness of will
ing to help others.
I'll try what you suggested.
Bing
"Hari Prasad" wrote:

> Hi,
> Yes, that will be a better option to check and confirm that your database
is
> good. DBCC CHECKDB will run for a long time if your database is
> big. In that case probably you can do this activity weekly once during non
> peak hours (weekends).
> Along with this you can also run UPDATE STATISTICS daily on those tables
> which have high DML access (Insert/ Update/ Delete).
> Monthly once check the fragmentation of table using DBCC
> SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
> THis will remove the fragmentation and increase the performance.
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> status?
> each one in a T-SQL script and have it run every day. Is there a better w
ay
> to do that?
>
>|||Bing,
Something else to keep in mind if you have some large db's or ones that are
24 x 7. You can restore a full backup on another machine and run the DBCC's
there. If it is corrupted on the primary it will be corrupted there as well
and you don't have to disrupt anyone while doing the check.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> Thanks so much for your instance response, Hari. Not just for this one.
You have answered a lot of my questions I posted previously. They are all
very helpful. I really appreciate your knowledgement and your kindness of
willing to help others.[vbcol=seagreen]
> I'll try what you suggested.
> Bing
> "Hari Prasad" wrote:
>
database is[vbcol=seagreen]
non[vbcol=seagreen]
for[vbcol=seagreen]
way[vbcol=seagreen]|||Good point. Thanks for the advice, Andrew.
Bing
"Andrew J. Kelly" wrote:

> Bing,
> Something else to keep in mind if you have some large db's or ones that ar
e
> 24 x 7. You can restore a full backup on another machine and run the DBCC
's
> there. If it is corrupted on the primary it will be corrupted there as we
ll
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
> for
> way
>
>|||Hi Andrew - just curious... what would the reason be for moving this data to
another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
it just takes page locks?
Thanks for your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> Bing,
> Something else to keep in mind if you have some large db's or ones that
are
> 24 x 7. You can restore a full backup on another machine and run the
DBCC's
> there. If it is corrupted on the primary it will be corrupted there as
well
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
tables[vbcol=seagreen]
table.[vbcol=seagreen]
> for
better[vbcol=seagreen]
> way
>|||Besides the little bit of blocking CHECKDB is very resource intensive. It
can use a lot of CPU and potentially a lot of I/O. So if you have a large DB
to check you can hinder performance of other users if the load is great
enough. If you had a 500GB db and wanted to run CHECKDB it could take a
long time and potentially affect lots of users since it would most likely
run outside of a typical maintenance window. By running it on a nother
machine you might not care how long it takes and how much CPU, I/O etc it
takes. And you could specify the TABLOCK option to make it faster and more
comprehensive than on the production system. This is not something that
everyone needs to do but just wanted to mention it is an option for those
that need it.
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23yqn58idEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi Andrew - just curious... what would the reason be for moving this data
to
> another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
> it just takes page locks?
> Thanks for your help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> are
> DBCC's
> well
one.[vbcol=seagreen]
all[vbcol=seagreen]
of[vbcol=seagreen]
during[vbcol=seagreen]
> tables
> table.
good[vbcol=seagreen]
checkdb'[vbcol=seagreen]
> better
>

Check database healthiness on a daily basis?

How do people usually do to make sure all the databases are in good status?
We have about 50 databases. I was intended to write 'dbcc checkdb' for each one in a T-SQL script and have it run every day. Is there a better way to do that?
Thanks in advance for any advices.
Bing
Hi,
Yes, that will be a better option to check and confirm that your database is
good. DBCC CHECKDB will run for a long time if your database is
big. In that case probably you can do this activity weekly once during non
peak hours (weekends).
Along with this you can also run UPDATE STATISTICS daily on those tables
which have high DML access (Insert/ Update/ Delete).
Monthly once check the fragmentation of table using DBCC
SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
THis will remove the fragmentation and increase the performance.
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> How do people usually do to make sure all the databases are in good
status?
> We have about 50 databases. I was intended to write 'dbcc checkdb' for
each one in a T-SQL script and have it run every day. Is there a better way
to do that?
> Thanks in advance for any advices.
> Bing
|||Thanks so much for your instance response, Hari. Not just for this one. You have answered a lot of my questions I posted previously. They are all very helpful. I really appreciate your knowledgement and your kindness of willing to help others.
I'll try what you suggested.
Bing
"Hari Prasad" wrote:

> Hi,
> Yes, that will be a better option to check and confirm that your database is
> good. DBCC CHECKDB will run for a long time if your database is
> big. In that case probably you can do this activity weekly once during non
> peak hours (weekends).
> Along with this you can also run UPDATE STATISTICS daily on those tables
> which have high DML access (Insert/ Update/ Delete).
> Monthly once check the fragmentation of table using DBCC
> SHOWCONTIG(Table_name), if fragmented you could DBCC REINDEX the table.
> THis will remove the fragmentation and increase the performance.
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:DDCE66EE-CFB0-4C9D-B215-AB7983D4021E@.microsoft.com...
> status?
> each one in a T-SQL script and have it run every day. Is there a better way
> to do that?
>
>
|||Bing,
Something else to keep in mind if you have some large db's or ones that are
24 x 7. You can restore a full backup on another machine and run the DBCC's
there. If it is corrupted on the primary it will be corrupted there as well
and you don't have to disrupt anyone while doing the check.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> Thanks so much for your instance response, Hari. Not just for this one.
You have answered a lot of my questions I posted previously. They are all
very helpful. I really appreciate your knowledgement and your kindness of
willing to help others.[vbcol=seagreen]
> I'll try what you suggested.
> Bing
> "Hari Prasad" wrote:
database is[vbcol=seagreen]
non[vbcol=seagreen]
for[vbcol=seagreen]
way[vbcol=seagreen]
|||Good point. Thanks for the advice, Andrew.
Bing
"Andrew J. Kelly" wrote:

> Bing,
> Something else to keep in mind if you have some large db's or ones that are
> 24 x 7. You can restore a full backup on another machine and run the DBCC's
> there. If it is corrupted on the primary it will be corrupted there as well
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
> for
> way
>
>
|||Hi Andrew - just curious... what would the reason be for moving this data to
another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
it just takes page locks?
Thanks for your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> Bing,
> Something else to keep in mind if you have some large db's or ones that
are
> 24 x 7. You can restore a full backup on another machine and run the
DBCC's
> there. If it is corrupted on the primary it will be corrupted there as
well[vbcol=seagreen]
> and you don't have to disrupt anyone while doing the check.
> --
> Andrew J. Kelly SQL MVP
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:74A64E31-18DF-4BFF-ACC6-284FFC3914CD@.microsoft.com...
> You have answered a lot of my questions I posted previously. They are all
> very helpful. I really appreciate your knowledgement and your kindness of
> willing to help others.
> database is
> non
tables[vbcol=seagreen]
table.[vbcol=seagreen]
> for
better
> way
>
|||Besides the little bit of blocking CHECKDB is very resource intensive. It
can use a lot of CPU and potentially a lot of I/O. So if you have a large DB
to check you can hinder performance of other users if the load is great
enough. If you had a 500GB db and wanted to run CHECKDB it could take a
long time and potentially affect lots of users since it would most likely
run outside of a typical maintenance window. By running it on a nother
machine you might not care how long it takes and how much CPU, I/O etc it
takes. And you could specify the TABLOCK option to make it faster and more
comprehensive than on the production system. This is not something that
everyone needs to do but just wanted to mention it is an option for those
that need it.
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:%23yqn58idEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi Andrew - just curious... what would the reason be for moving this data
to[vbcol=seagreen]
> another DB? Is it to save CPU cycles? Isn't CHECKDB non-disruptive since
> it just takes page locks?
> Thanks for your help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unMcp8YdEHA.228@.TK2MSFTNGP11.phx.gbl...
> are
> DBCC's
> well
one.[vbcol=seagreen]
all[vbcol=seagreen]
of[vbcol=seagreen]
during[vbcol=seagreen]
> tables
> table.
good[vbcol=seagreen]
checkdb'
> better
>