I have an instance of SQLServer which I would like to restart automatically
once a day due to memory leak issues. How can I best achieve this?
Thanks in advance for your help.rmuram wrote:
> I have an instance of SQLServer which I would like to restart
> automatically once a day due to memory leak issues. How can I best
> achieve this?
> Thanks in advance for your help.
I think before you go this route, we should discuss the memory issues. I
have not seen any reported memory leak issues with SQL Server. Some may
exist, but I have not seen them. What is often reported as a memory leak
issue is generally not a problem, but a SQL Server design. If what you're
seeing is that SQL Server gradually consumes more and more memory on the
server without returning that memory to the server, this is by design. If
you are finding that you require additional memory for other services on the
server, then you should set an upper memory limit for the server to prevent
it from consuming too much. If OTOH you are seeing a memory leak issue,
please let us know in more detail what you are seeing.
To start stop the server manually, you can look at "Starting SQL Server
Manually" and "Stopping SQL Server" in BOL.
David Gugick
Quest Software|||Hi,
>From SQL Server it is imposssible to schedule a restart of SQL
Server.But can create a BATCH file.
HTH
from
Killer
rmuram wrote:
> I have an instance of SQLServer which I would like to restart automaticall
y
> once a day due to memory leak issues. How can I best achieve this?
> Thanks in advance for your help.|||Thanks David for the reply.
My use of the descrition 'memory leak' was not not entirely correct and wh
at
I am seeing is as described you described below.
> If what you're seeing is that SQL Server gradually consumes more and more
> memory on the server without returning that memory to the server, this is
by
> design.
What happens if I set the upperlimit to the memory usage? Will it free up
memory to serve additional requests etc?
I would still like to know if there are ways to automate startup/shutdown if
anybody has any experience.
Thanks.
"David Gugick" wrote:
> rmuram wrote:
> I think before you go this route, we should discuss the memory issues. I
> have not seen any reported memory leak issues with SQL Server. Some may
> exist, but I have not seen them. What is often reported as a memory leak
> issue is generally not a problem, but a SQL Server design. If what you're
> seeing is that SQL Server gradually consumes more and more memory on the
> server without returning that memory to the server, this is by design. If
> you are finding that you require additional memory for other services on t
he
> server, then you should set an upper memory limit for the server to preven
t
> it from consuming too much. If OTOH you are seeing a memory leak issue,
> please let us know in more detail what you are seeing.
> To start stop the server manually, you can look at "Starting SQL Server
> Manually" and "Stopping SQL Server" in BOL.
>
> --
> David Gugick
> Quest Software
>
>|||Thanks for the reply.
Do you have a sample I can work from?
"doller" wrote:
> Hi,
>
> Server.But can create a BATCH file.
> HTH
> from
> Killer
> rmuram wrote:
>|||Hi rmura,
If u want to create a backfile to start and stop of sql server the u
have to follow these steps
open command prompt
write net stop mssqlsever
if will ask u to stop sql agent y/n : press y
then it will ask u to stop sql server y/n : press y
so u can create a backfile
like this
cd \
net stop mssqlserver
y
to start sql sever
add the code
net start mssqlserver
now u need to start sql agent
add these code
net start sqlserveragent
HTH
from
Killer|||Here's some info on SQL Server's memory usage:
INF: SQL Server Memory Usage
http://support.microsoft.com/defaul...b;en-us;q321363
http://www.mssqlserver.com/faq/trou...-memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmuram" <rmuram@.discussions.microsoft.com> wrote in message
news:CCC09FAF-9647-4210-9C2A-779AF6FB760C@.microsoft.com...
>I have an instance of SQLServer which I would like to restart automatically
> once a day due to memory leak issues. How can I best achieve this?
> Thanks in advance for your help.
Showing posts with label instance. Show all posts
Showing posts with label instance. Show all posts
Friday, March 30, 2012
Scheduling SQL Server start/stop
I have an instance of SQLServer which I would like to restart automatically
once a day due to memory leak issues. How can I best achieve this?
Thanks in advance for your help.rmuram wrote:
> I have an instance of SQLServer which I would like to restart
> automatically once a day due to memory leak issues. How can I best
> achieve this?
> Thanks in advance for your help.
I think before you go this route, we should discuss the memory issues. I
have not seen any reported memory leak issues with SQL Server. Some may
exist, but I have not seen them. What is often reported as a memory leak
issue is generally not a problem, but a SQL Server design. If what you're
seeing is that SQL Server gradually consumes more and more memory on the
server without returning that memory to the server, this is by design. If
you are finding that you require additional memory for other services on the
server, then you should set an upper memory limit for the server to prevent
it from consuming too much. If OTOH you are seeing a memory leak issue,
please let us know in more detail what you are seeing.
To start stop the server manually, you can look at "Starting SQL Server
Manually" and "Stopping SQL Server" in BOL.
David Gugick
Quest Software|||Hi,
>From SQL Server it is imposssible to schedule a restart of SQL
Server.But can create a BATCH file.
HTH
from
Killer
rmuram wrote:
> I have an instance of SQLServer which I would like to restart automatically
> once a day due to memory leak issues. How can I best achieve this?
> Thanks in advance for your help.|||Thanks David for the reply.
My use of the descrition 'memory leak' was notã'not entirely correct and what
I am seeing is as described you described below.
> If what you're seeing is that SQL Server gradually consumes more and more
> memory on the server without returning that memory to the server, this is by
> design.
What happens if I set the upperlimit to the memory usage? Will it free up
memory to serve additional requests etc?
I would still like to know if there are ways to automate startup/shutdown if
anybody has any experience.
Thanks.
"David Gugick" wrote:
> rmuram wrote:
> > I have an instance of SQLServer which I would like to restart
> > automatically once a day due to memory leak issues. How can I best
> > achieve this?
> > Thanks in advance for your help.
> I think before you go this route, we should discuss the memory issues. I
> have not seen any reported memory leak issues with SQL Server. Some may
> exist, but I have not seen them. What is often reported as a memory leak
> issue is generally not a problem, but a SQL Server design. If what you're
> seeing is that SQL Server gradually consumes more and more memory on the
> server without returning that memory to the server, this is by design. If
> you are finding that you require additional memory for other services on the
> server, then you should set an upper memory limit for the server to prevent
> it from consuming too much. If OTOH you are seeing a memory leak issue,
> please let us know in more detail what you are seeing.
> To start stop the server manually, you can look at "Starting SQL Server
> Manually" and "Stopping SQL Server" in BOL.
>
> --
> David Gugick
> Quest Software
>
>|||Thanks for the reply.
Do you have a sample I can work from?
"doller" wrote:
> Hi,
> >From SQL Server it is imposssible to schedule a restart of SQL
> Server.But can create a BATCH file.
> HTH
> from
> Killer
> rmuram wrote:
> > I have an instance of SQLServer which I would like to restart automatically
> > once a day due to memory leak issues. How can I best achieve this?
> > Thanks in advance for your help.
>|||Hi rmura,
If u want to create a backfile to start and stop of sql server the u
have to follow these steps
open command prompt
write net stop mssqlsever
if will ask u to stop sql agent y/n : press y
then it will ask u to stop sql server y/n : press y
so u can create a backfile
like this
cd \
net stop mssqlserver
y
to start sql sever
add the code
net start mssqlserver
now u need to start sql agent
add these code
net start sqlserveragent
HTH
from
Killer|||Here's some info on SQL Server's memory usage:
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmuram" <rmuram@.discussions.microsoft.com> wrote in message
news:CCC09FAF-9647-4210-9C2A-779AF6FB760C@.microsoft.com...
>I have an instance of SQLServer which I would like to restart automatically
> once a day due to memory leak issues. How can I best achieve this?
> Thanks in advance for your help.
once a day due to memory leak issues. How can I best achieve this?
Thanks in advance for your help.rmuram wrote:
> I have an instance of SQLServer which I would like to restart
> automatically once a day due to memory leak issues. How can I best
> achieve this?
> Thanks in advance for your help.
I think before you go this route, we should discuss the memory issues. I
have not seen any reported memory leak issues with SQL Server. Some may
exist, but I have not seen them. What is often reported as a memory leak
issue is generally not a problem, but a SQL Server design. If what you're
seeing is that SQL Server gradually consumes more and more memory on the
server without returning that memory to the server, this is by design. If
you are finding that you require additional memory for other services on the
server, then you should set an upper memory limit for the server to prevent
it from consuming too much. If OTOH you are seeing a memory leak issue,
please let us know in more detail what you are seeing.
To start stop the server manually, you can look at "Starting SQL Server
Manually" and "Stopping SQL Server" in BOL.
David Gugick
Quest Software|||Hi,
>From SQL Server it is imposssible to schedule a restart of SQL
Server.But can create a BATCH file.
HTH
from
Killer
rmuram wrote:
> I have an instance of SQLServer which I would like to restart automatically
> once a day due to memory leak issues. How can I best achieve this?
> Thanks in advance for your help.|||Thanks David for the reply.
My use of the descrition 'memory leak' was notã'not entirely correct and what
I am seeing is as described you described below.
> If what you're seeing is that SQL Server gradually consumes more and more
> memory on the server without returning that memory to the server, this is by
> design.
What happens if I set the upperlimit to the memory usage? Will it free up
memory to serve additional requests etc?
I would still like to know if there are ways to automate startup/shutdown if
anybody has any experience.
Thanks.
"David Gugick" wrote:
> rmuram wrote:
> > I have an instance of SQLServer which I would like to restart
> > automatically once a day due to memory leak issues. How can I best
> > achieve this?
> > Thanks in advance for your help.
> I think before you go this route, we should discuss the memory issues. I
> have not seen any reported memory leak issues with SQL Server. Some may
> exist, but I have not seen them. What is often reported as a memory leak
> issue is generally not a problem, but a SQL Server design. If what you're
> seeing is that SQL Server gradually consumes more and more memory on the
> server without returning that memory to the server, this is by design. If
> you are finding that you require additional memory for other services on the
> server, then you should set an upper memory limit for the server to prevent
> it from consuming too much. If OTOH you are seeing a memory leak issue,
> please let us know in more detail what you are seeing.
> To start stop the server manually, you can look at "Starting SQL Server
> Manually" and "Stopping SQL Server" in BOL.
>
> --
> David Gugick
> Quest Software
>
>|||Thanks for the reply.
Do you have a sample I can work from?
"doller" wrote:
> Hi,
> >From SQL Server it is imposssible to schedule a restart of SQL
> Server.But can create a BATCH file.
> HTH
> from
> Killer
> rmuram wrote:
> > I have an instance of SQLServer which I would like to restart automatically
> > once a day due to memory leak issues. How can I best achieve this?
> > Thanks in advance for your help.
>|||Hi rmura,
If u want to create a backfile to start and stop of sql server the u
have to follow these steps
open command prompt
write net stop mssqlsever
if will ask u to stop sql agent y/n : press y
then it will ask u to stop sql server y/n : press y
so u can create a backfile
like this
cd \
net stop mssqlserver
y
to start sql sever
add the code
net start mssqlserver
now u need to start sql agent
add these code
net start sqlserveragent
HTH
from
Killer|||Here's some info on SQL Server's memory usage:
INF: SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321363
http://www.mssqlserver.com/faq/troubleshooting-memoryleak.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmuram" <rmuram@.discussions.microsoft.com> wrote in message
news:CCC09FAF-9647-4210-9C2A-779AF6FB760C@.microsoft.com...
>I have an instance of SQLServer which I would like to restart automatically
> once a day due to memory leak issues. How can I best achieve this?
> Thanks in advance for your help.
Scheduling SQL Server start/stop
I have an instance of SQLServer which I would like to restart automatically
once a day due to memory leak issues. How can I best achieve this?
Thanks in advance for your help.
rmuram wrote:
> I have an instance of SQLServer which I would like to restart
> automatically once a day due to memory leak issues. How can I best
> achieve this?
> Thanks in advance for your help.
I think before you go this route, we should discuss the memory issues. I
have not seen any reported memory leak issues with SQL Server. Some may
exist, but I have not seen them. What is often reported as a memory leak
issue is generally not a problem, but a SQL Server design. If what you're
seeing is that SQL Server gradually consumes more and more memory on the
server without returning that memory to the server, this is by design. If
you are finding that you require additional memory for other services on the
server, then you should set an upper memory limit for the server to prevent
it from consuming too much. If OTOH you are seeing a memory leak issue,
please let us know in more detail what you are seeing.
To start stop the server manually, you can look at "Starting SQL Server
Manually" and "Stopping SQL Server" in BOL.
David Gugick
Quest Software
|||Hi,
>From SQL Server it is imposssible to schedule a restart of SQL
Server.But can create a BATCH file.
HTH
from
Killer
rmuram wrote:
> I have an instance of SQLServer which I would like to restart automatically
> once a day due to memory leak issues. How can I best achieve this?
> Thanks in advance for your help.
|||Thanks David for the reply.
My use of the descrition 'memory leak' was not not entirely correct and what
I am seeing is as described you described below.
> If what you're seeing is that SQL Server gradually consumes more and more
> memory on the server without returning that memory to the server, this is by
> design.
What happens if I set the upperlimit to the memory usage? Will it free up
memory to serve additional requests etc?
I would still like to know if there are ways to automate startup/shutdown if
anybody has any experience.
Thanks.
"David Gugick" wrote:
> rmuram wrote:
> I think before you go this route, we should discuss the memory issues. I
> have not seen any reported memory leak issues with SQL Server. Some may
> exist, but I have not seen them. What is often reported as a memory leak
> issue is generally not a problem, but a SQL Server design. If what you're
> seeing is that SQL Server gradually consumes more and more memory on the
> server without returning that memory to the server, this is by design. If
> you are finding that you require additional memory for other services on the
> server, then you should set an upper memory limit for the server to prevent
> it from consuming too much. If OTOH you are seeing a memory leak issue,
> please let us know in more detail what you are seeing.
> To start stop the server manually, you can look at "Starting SQL Server
> Manually" and "Stopping SQL Server" in BOL.
>
> --
> David Gugick
> Quest Software
>
>
|||Thanks for the reply.
Do you have a sample I can work from?
"doller" wrote:
> Hi,
> Server.But can create a BATCH file.
> HTH
> from
> Killer
> rmuram wrote:
>
|||Hi rmura,
If u want to create a backfile to start and stop of sql server the u
have to follow these steps
open command prompt
write net stop mssqlsever
if will ask u to stop sql agent y/n : press y
then it will ask u to stop sql server y/n : press y
so u can create a backfile
like this
cd \
net stop mssqlserver
y
to start sql sever
add the code
net start mssqlserver
now u need to start sql agent
add these code
net start sqlserveragent
HTH
from
Killer
|||Here's some info on SQL Server's memory usage:
INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363
http://www.mssqlserver.com/faq/troub...memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmuram" <rmuram@.discussions.microsoft.com> wrote in message
news:CCC09FAF-9647-4210-9C2A-779AF6FB760C@.microsoft.com...
>I have an instance of SQLServer which I would like to restart automatically
> once a day due to memory leak issues. How can I best achieve this?
> Thanks in advance for your help.
once a day due to memory leak issues. How can I best achieve this?
Thanks in advance for your help.
rmuram wrote:
> I have an instance of SQLServer which I would like to restart
> automatically once a day due to memory leak issues. How can I best
> achieve this?
> Thanks in advance for your help.
I think before you go this route, we should discuss the memory issues. I
have not seen any reported memory leak issues with SQL Server. Some may
exist, but I have not seen them. What is often reported as a memory leak
issue is generally not a problem, but a SQL Server design. If what you're
seeing is that SQL Server gradually consumes more and more memory on the
server without returning that memory to the server, this is by design. If
you are finding that you require additional memory for other services on the
server, then you should set an upper memory limit for the server to prevent
it from consuming too much. If OTOH you are seeing a memory leak issue,
please let us know in more detail what you are seeing.
To start stop the server manually, you can look at "Starting SQL Server
Manually" and "Stopping SQL Server" in BOL.
David Gugick
Quest Software
|||Hi,
>From SQL Server it is imposssible to schedule a restart of SQL
Server.But can create a BATCH file.
HTH
from
Killer
rmuram wrote:
> I have an instance of SQLServer which I would like to restart automatically
> once a day due to memory leak issues. How can I best achieve this?
> Thanks in advance for your help.
|||Thanks David for the reply.
My use of the descrition 'memory leak' was not not entirely correct and what
I am seeing is as described you described below.
> If what you're seeing is that SQL Server gradually consumes more and more
> memory on the server without returning that memory to the server, this is by
> design.
What happens if I set the upperlimit to the memory usage? Will it free up
memory to serve additional requests etc?
I would still like to know if there are ways to automate startup/shutdown if
anybody has any experience.
Thanks.
"David Gugick" wrote:
> rmuram wrote:
> I think before you go this route, we should discuss the memory issues. I
> have not seen any reported memory leak issues with SQL Server. Some may
> exist, but I have not seen them. What is often reported as a memory leak
> issue is generally not a problem, but a SQL Server design. If what you're
> seeing is that SQL Server gradually consumes more and more memory on the
> server without returning that memory to the server, this is by design. If
> you are finding that you require additional memory for other services on the
> server, then you should set an upper memory limit for the server to prevent
> it from consuming too much. If OTOH you are seeing a memory leak issue,
> please let us know in more detail what you are seeing.
> To start stop the server manually, you can look at "Starting SQL Server
> Manually" and "Stopping SQL Server" in BOL.
>
> --
> David Gugick
> Quest Software
>
>
|||Thanks for the reply.
Do you have a sample I can work from?
"doller" wrote:
> Hi,
> Server.But can create a BATCH file.
> HTH
> from
> Killer
> rmuram wrote:
>
|||Hi rmura,
If u want to create a backfile to start and stop of sql server the u
have to follow these steps
open command prompt
write net stop mssqlsever
if will ask u to stop sql agent y/n : press y
then it will ask u to stop sql server y/n : press y
so u can create a backfile
like this
cd \
net stop mssqlserver
y
to start sql sever
add the code
net start mssqlserver
now u need to start sql agent
add these code
net start sqlserveragent
HTH
from
Killer
|||Here's some info on SQL Server's memory usage:
INF: SQL Server Memory Usage
http://support.microsoft.com/default...;en-us;q321363
http://www.mssqlserver.com/faq/troub...memoryleak.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmuram" <rmuram@.discussions.microsoft.com> wrote in message
news:CCC09FAF-9647-4210-9C2A-779AF6FB760C@.microsoft.com...
>I have an instance of SQLServer which I would like to restart automatically
> once a day due to memory leak issues. How can I best achieve this?
> Thanks in advance for your help.
Monday, March 12, 2012
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@.discussions.microsoft.com> wrote in message
news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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 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:
> 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@.discussions.microsoft.com> wrote in message
> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
>
>
|||You might want to do some more research on your backup strategies. This may
help as well:
http://www.microsoft.com/technet/pro...n/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@.discussions.microsoft.com> wrote in message
news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...[vbcol=seagreen]
> 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 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:
> You might want to do some more research on your backup strategies. This may
> help as well:
> http://www.microsoft.com/technet/pro...n/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@.discussions.microsoft.com> wrote in message
> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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@.discussions.microsoft.com> wrote in message
news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...[vbcol=seagreen]
> 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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.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 logs
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 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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
>
>
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@.discussions.microsoft.com> wrote in message
news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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 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:
> 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@.discussions.microsoft.com> wrote in message
> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.com...
>
>
|||You might want to do some more research on your backup strategies. This may
help as well:
http://www.microsoft.com/technet/pro...n/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@.discussions.microsoft.com> wrote in message
news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...[vbcol=seagreen]
> 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 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:
> You might want to do some more research on your backup strategies. This may
> help as well:
> http://www.microsoft.com/technet/pro...n/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@.discussions.microsoft.com> wrote in message
> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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@.discussions.microsoft.com> wrote in message
news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...[vbcol=seagreen]
> 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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.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 logs
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 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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
>
>
Friday, March 9, 2012
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 HernandezIs 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@.discussions.microsoft.com> wrote in message
news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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 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:
> 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@.discussions.microsoft.com> wrote in message
> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
>
>|||You might want to do some more research on your backup strategies. This may
help as well:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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@.discussions.microsoft.com> wrote in message
news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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:
>> 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@.discussions.microsoft.com> wrote in message
>> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
>>|||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:
> You might want to do some more research on your backup strategies. This may
> help as well:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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@.discussions.microsoft.com> wrote in message
> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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:
> >
> >> 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@.discussions.microsoft.com> wrote in message
> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
> >>
> >>
> >>
>
>|||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@.discussions.microsoft.com> wrote in message
news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.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:
>> You might want to do some more research on your backup strategies. This
>> may
>> help as well:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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@.discussions.microsoft.com> wrote in message
>> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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:
>> >
>> >> 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@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
>> >>
>> >>
>> >>
>>|||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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.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:
>> You might want to do some more research on your backup strategies. This
>> may
>> help as well:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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@.discussions.microsoft.com> wrote in
>> message
>> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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:
>> >
>> >> 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@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
>> >>
>> >>
>> >>
>>
>|||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 logs
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 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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.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:
> >
> >> You might want to do some more research on your backup strategies. This
> >> may
> >> help as well:
> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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@.discussions.microsoft.com> wrote in message
> >> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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:
> >> >
> >> >> 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@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
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 HernandezIs 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@.discussions.microsoft.com> wrote in message
news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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 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:
> 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@.discussions.microsoft.com> wrote in message
> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
>
>|||You might want to do some more research on your backup strategies. This may
help as well:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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@.discussions.microsoft.com> wrote in message
news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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:
>> 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@.discussions.microsoft.com> wrote in message
>> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
>>|||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:
> You might want to do some more research on your backup strategies. This may
> help as well:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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@.discussions.microsoft.com> wrote in message
> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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:
> >
> >> 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@.discussions.microsoft.com> wrote in message
> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
> >>
> >>
> >>
>
>|||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@.discussions.microsoft.com> wrote in message
news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.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:
>> You might want to do some more research on your backup strategies. This
>> may
>> help as well:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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@.discussions.microsoft.com> wrote in message
>> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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:
>> >
>> >> 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@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
>> >>
>> >>
>> >>
>>|||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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.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:
>> You might want to do some more research on your backup strategies. This
>> may
>> help as well:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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@.discussions.microsoft.com> wrote in
>> message
>> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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:
>> >
>> >> 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@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
>> >>
>> >>
>> >>
>>
>|||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 logs
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 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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.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:
> >
> >> You might want to do some more research on your backup strategies. This
> >> may
> >> help as well:
> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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@.discussions.microsoft.com> wrote in message
> >> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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:
> >> >
> >> >> 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@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
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 HernandezIs 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@.discussions.microsoft.com> wrote in message
news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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@.discussions.microsoft.com> wrote in message
> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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@.discussions.microsoft.com> wrote in message
news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...[vbcol=seagreen]
> 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@.discussions.microsoft.com> wrote in message
> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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@.discussions.microsoft.com> wrote in message
news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...[vbcol=seagreen]
> 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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
>
>
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 HernandezIs 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@.discussions.microsoft.com> wrote in message
news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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@.discussions.microsoft.com> wrote in message
> news:CB184888-D70E-480C-BB23-2F3CE6014FBF@.microsoft.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@.discussions.microsoft.com> wrote in message
news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.com...[vbcol=seagreen]
> 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@.discussions.microsoft.com> wrote in message
> news:C1624CF6-C205-4973-B418-4CD8224F0F84@.microsoft.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@.discussions.microsoft.com> wrote in message
news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...[vbcol=seagreen]
> 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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.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@.discussions.microsoft.com> wrote in message
> news:35C74E8D-4AAC-4E2E-9E43-B4E82AD402FB@.microsoft.com...
>
>
Wednesday, March 7, 2012
Schedule problem with remote SQL instance.
Im running RS on Windows 2003 server, connecting to a remote instance of SQL.
When I try to set up a schedule for any report, i get...
"The SQL Agent service is not running. This operation requires the SQL Agent
service. (rsSchedulerNotResponding)"
I know its not finding any local sql agent on the server RS is running on,
what im wondering is if there is a way to hack it to use the SQL agent on a
remote SQL server to schedule these reports.
Thanks!i have the same problem..Did u find any solution?
"Jason Brinkley" wrote:
> Im running RS on Windows 2003 server, connecting to a remote instance of SQL.
> When I try to set up a schedule for any report, i get...
> "The SQL Agent service is not running. This operation requires the SQL Agent
> service. (rsSchedulerNotResponding)"
> I know its not finding any local sql agent on the server RS is running on,
> what im wondering is if there is a way to hack it to use the SQL agent on a
> remote SQL server to schedule these reports.
> Thanks!|||I am pretty sure the SQL Agent message IS talking about the server with the
database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chitra" <Chitra@.discussions.microsoft.com> wrote in message
news:CAB5BEB8-36DD-490E-8AC5-15E57C19AC7B@.microsoft.com...
>i have the same problem..Did u find any solution?
> "Jason Brinkley" wrote:
>> Im running RS on Windows 2003 server, connecting to a remote instance of
>> SQL.
>> When I try to set up a schedule for any report, i get...
>> "The SQL Agent service is not running. This operation requires the SQL
>> Agent
>> service. (rsSchedulerNotResponding)"
>> I know its not finding any local sql agent on the server RS is running
>> on,
>> what im wondering is if there is a way to hack it to use the SQL agent on
>> a
>> remote SQL server to schedule these reports.
>> Thanks!
When I try to set up a schedule for any report, i get...
"The SQL Agent service is not running. This operation requires the SQL Agent
service. (rsSchedulerNotResponding)"
I know its not finding any local sql agent on the server RS is running on,
what im wondering is if there is a way to hack it to use the SQL agent on a
remote SQL server to schedule these reports.
Thanks!i have the same problem..Did u find any solution?
"Jason Brinkley" wrote:
> Im running RS on Windows 2003 server, connecting to a remote instance of SQL.
> When I try to set up a schedule for any report, i get...
> "The SQL Agent service is not running. This operation requires the SQL Agent
> service. (rsSchedulerNotResponding)"
> I know its not finding any local sql agent on the server RS is running on,
> what im wondering is if there is a way to hack it to use the SQL agent on a
> remote SQL server to schedule these reports.
> Thanks!|||I am pretty sure the SQL Agent message IS talking about the server with the
database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chitra" <Chitra@.discussions.microsoft.com> wrote in message
news:CAB5BEB8-36DD-490E-8AC5-15E57C19AC7B@.microsoft.com...
>i have the same problem..Did u find any solution?
> "Jason Brinkley" wrote:
>> Im running RS on Windows 2003 server, connecting to a remote instance of
>> SQL.
>> When I try to set up a schedule for any report, i get...
>> "The SQL Agent service is not running. This operation requires the SQL
>> Agent
>> service. (rsSchedulerNotResponding)"
>> I know its not finding any local sql agent on the server RS is running
>> on,
>> what im wondering is if there is a way to hack it to use the SQL agent on
>> a
>> remote SQL server to schedule these reports.
>> Thanks!
Subscribe to:
Posts (Atom)