scheduled backups with the wizard not running"

Hello all,

I am very new to SQL. We are running SQL 2000. We are using one instance to
run Project Web Access and I need to setup a disaster recovery. I have
donwloaded the disaster recovery from the project server site. I have a
pretty good idea how go about backing up the databases in case the server
melts into a puddle. I have setup syncronized database transaction log
marking to have a consistent recovery point.

The problem I am running into is that I am trying to use the SQL wizard to
backup my databases and transaction logs at night. I have made sure to setup
the schedule transactions, but it wont run at night.

Can someone please shine light on my problem.

Thank,s

Joe Hernandez

Is SQL Agent actually running?   By the way it does little good to backup
the log only once a night.  Themain purpose of transaction log backups is to
minimize the amount of data loss.  If you only backup the logs at night you
run the risk of loosing up to 24 hours worth of changes.  You might want to
start with no more than an hour apart for the logs.

--
Andrew J. Kelly  SQL MVP


"Joe Hernandez" < JoeHernandez@discuss
ions.microsoft.com> wrote in message
news:CB184888-D70E-480C-BB23- 2F3CE6014FBF@microso
ft.com...
> Hello all,
>
> I am very new to SQL. We are running SQL 2000. We are using one instance
> to
> run Project Web Access and I need to setup a disaster recovery. I have
> donwloaded the disaster recovery from the project server site. I have a
> pretty good idea how go about backing up the databases in case the server
> melts into a puddle. I have setup syncronized database transaction log
> marking to have a consistent recovery point.
>
> The problem I am running into is that I am trying to use the SQL wizard to
> backup my databases and transaction logs at night. I have made sure to
> setup
> the schedule transactions, but it wont run at night.
>
> Can someone please shine light on my problem.
>
> Thank,s
>
> Joe Hernandez

Andrew,
Thanks for your answer

How do I check that the SQL agent is running?

The reason why I setup the transaction log to backup once a day, is because
I have the automatic log and data marking happening at night, and I am tryin
g
to back up the databases at night as well. If I backup the transaction log
every hour and the actual databases at night, I understood that I can only
restore up to the point where I backed up the database? Ithought too often
backup of the transaction log creates an overhead on the SQL performance.

Please advice

Joe Hernandez

"Andrew J. Kelly" wrote:

> Is SQL Agent actually running?   By the way it does little good to backup
> the log only once a night.  Themain purpose of transaction log backups is 
to
> minimize the amount of data loss.  If you only backup the logs at night yo
u
> run the risk of loosing up to 24 hours worth of changes.  You might want t
o
> start with no more than an hour apart for the logs.
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "Joe Hernandez" < JoeHernandez@discuss
ions.microsoft.com> wrote in message
> news:CB184888-D70E-480C-BB23- 2F3CE6014FBF@microso
ft.com... 
>
>
>

You might want to do some more research on your backup strategies. This may
help as well:
http://www.microsoft.com/technet/pr...in/sqlops0.mspx

But here are some general comments about what I see so far.  First off I am
not quite sure what you mean by "Mark" the backups.  There is nothing
special required to issue a backup in SQL Server other than the BACKUP
command.  A FULL backup is required in order to do any restores or even to
do a LOG backup.  But doing additional FULL Backups will not in any way
invalidate the LOG backups or the process.  They work together. The Log
chain is constant and spans FULL backups.  When you need to Restore a DB you
always start from the last FULL backup and then only need to apply the LOG
backups since that one.  But the act of doing a FULL backup does not in any
way invalidate the other log backups.  Backups in general are very
lightweight, especially the log backups.  You should not be afraid to backup
the logs during the day. Actually that is when they are most useful.

To check to see if SQL Agent is running you can open Enterprise Manager and
go to the Management Node.  Then when you expand that you will see the SQL
Agent node.  If you see a Green arrow it is running. Right click on it to
start or stop it.

--
Andrew J. Kelly  SQL MVP


"Joe Hernandez" < JoeHernandez@discuss
ions.microsoft.com> wrote in message
news:C1624CF6-C205-4973-B418- 4CD8224F0F84@microso
ft.com...
> Andrew,
> Thanks for your answer
>
> How do I check that the SQL agent is running?
>
> The reason why I setup the transaction log to backup once a day, is
> because
> I have the automatic log and data marking happening at night, and I am
> trying
> to back up the databases at night as well. If I backup the transaction log
> every hour and the actual databases at night, I understood that I can only
> restore up to the point where I backed up the database? Ithought too often
> backup of the transaction log creates an overhead on the SQL performance.
>
> Please advice
>
> Joe Hernandez
>
> "Andrew J. Kelly" wrote:
> 

in the instace of SQL I am running 3 databases for projectwebaccess. one for
sharepoint service, projectserver and one for reporting services.
According to the disaster recovery for project server because I am using 3
different databases they say it is necessary to use marked transactions in
the transaction log of each database to facilitate recovery, I guess the ide
a
is that by applying marked transactions to all the databases at the same tim
e
I create a consistent point in all databases, they right: "Recovery of
related databases to any time earlier than the point of failure can only be
accomplished by recovering to a marked transaction.

Thanks for the info on the SQL agent, it was not running. I since then
turned it on and was able to run a schedule back up

Joe H

"Andrew J. Kelly" wrote:

> You might want to do some more research on your backup strategies. This ma
y
> help as well:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx[/url
]
>
> But here are some general comments about what I see so far.  First off I a
m
> not quite sure what you mean by "Mark" the backups.  There is nothing
> special required to issue a backup in SQL Server other than the BACKUP
> command.  A FULL backup is required in order to do any restores or even to
> do a LOG backup.  But doing additional FULL Backups will not in any way
> invalidate the LOG backups or the process.  They work together. The Log
> chain is constant and spans FULL backups.  When you need to Restore a DB y
ou
> always start from the last FULL backup and then only need to apply the LOG
> backups since that one.  But the act of doing a FULL backup does not in an
y
> way invalidate the other log backups.  Backups in general are very
> lightweight, especially the log backups.  You should not be afraid to back
up
> the logs during the day. Actually that is when they are most useful.
>
> To check to see if SQL Agent is running you can open Enterprise Manager an
d
> go to the Management Node.  Then when you expand that you will see the SQL
> Agent node.  If you see a Green arrow it is running. Right click on it to
> start or stop it.
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "Joe Hernandez" < JoeHernandez@discuss
ions.microsoft.com> wrote in message
> news:C1624CF6-C205-4973-B418- 4CD8224F0F84@microso
ft.com... 
>
>
>

Hello,

I think what they are talking about is you may need to keep all your
databases in a consistent state: Meaning if you restore one database and
replay transaction logs to a point and time - you may need to ensure you do
the same on the others to ensure they line up.

Not familiar with Project server so without researching it I can only gather
this is what they mean.

I consult for many, many clients who ALL use SQL - everyone of them I have
set up with full backups at night, and frequent transaction log dumps during
the day to facilitate the best recovery options.

For other clients who demand a little more I set up log shipping; which is a
great DR method and takes only a few hours to set up.

Cheers

Ian


"Joe Hernandez" < JoeHernandez@discuss
ions.microsoft.com> wrote in message
news:35C74E8D-4AAC-4E2E-9E43- B4E82AD402FB@microso
ft.com...
> in the instace of SQL I am running 3 databases for projectwebaccess. one
> for
> sharepoint service, projectserver and one for reporting services.
> According to the disaster recovery for project server because I am using 3
> different databases they say it is necessary to use marked transactions in
> the transaction log of each database to facilitate recovery, I guess the
> idea
> is that by applying marked transactions to all the databases at the same
> time
> I create a consistent point in all databases, they right: "Recovery of
> related databases to any time earlier than the point of failure can only
> be
> accomplished by recovering to a marked transaction.
>
> Thanks for the info on the SQL agent, it was not running. I since then
> turned it on and was able to run a schedule back up
>
> Joe H
>
> "Andrew J. Kelly" wrote:
> 

If you are updating data across more than one db at a time you should wrap
them in a transaction.  Then if you restore to a point in time (nothing
special needed just proper log backups) on each db you should have
consistency between them.

--
Andrew J. Kelly  SQL MVP


"Ian McQuade" <imcquade@altara.com> wrote in message
news:eomnZE%23CGHA.3992@TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I think what they are talking about is you may need to keep all your
> databases in a consistent state: Meaning if you restore one database and
> replay transaction logs to a point and time - you may need to ensure you
> do the same on the others to ensure they line up.
>
> Not familiar with Project server so without researching it I can only
> gather this is what they mean.
>
> I consult for many, many clients who ALL use SQL - everyone of them I have
> set up with full backups at night, and frequent transaction log dumps
> during the day to facilitate the best recovery options.
>
> For other clients who demand a little more I set up log shipping; which is
> a great DR method and takes only a few hours to set up.
>
> Cheers
>
> Ian
>
>
> "Joe Hernandez" < JoeHernandez@discuss
ions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43- B4E82AD402FB@microso
ft.com... 
>
>

Ian,

Thanks for your explanation. So it sounds like I am on the right track that
as long as I keep backups of my databases and transaction log, I should be
able to restore in the event of hardware and/or software malfunction. Good
because I am having a hard time running the automated script to mark all log
s
and databases at the same time.
How hard is it to setup log shipping, do you know of a good place to go to
get good notes on that?

thanks for your help

Joe Hernandez

"Ian McQuade" wrote:

> Hello,
>
> I think what they are talking about is you may need to keep all your
> databases in a consistent state: Meaning if you restore one database and
> replay transaction logs to a point and time - you may need to ensure you d
o
> the same on the others to ensure they line up.
>
> Not familiar with Project server so without researching it I can only gath
er
> this is what they mean.
>
> I consult for many, many clients who ALL use SQL - everyone of them I have
> set up with full backups at night, and frequent transaction log dumps duri
ng
> the day to facilitate the best recovery options.
>
> For other clients who demand a little more I set up log shipping; which is
 a
> great DR method and takes only a few hours to set up.
>
> Cheers
>
> Ian
>
>
> "Joe Hernandez" < JoeHernandez@discuss
ions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43- B4E82AD402FB@microso
ft.com... 
>
>
>