Database mirroring setup without downtime

Does anyone know if you can mirror a database while users are banging away on
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.
Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.

Database mirroring setup without downtime

Does anyone know if you can mirror a database while users are banging away o
n
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and i
t
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have t
o
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.

Database mirroring setup without downtime

Does anyone know if you can mirror a database while users are banging away on
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.

database maintenance plans for 2005 Express

Hello:
I know how to create database maintenance plans (automated backups) in SQL
Server 2000 and 2005.
But, I have never done so in SQL Server 2005 Express Edition.
I tried right-clicking on maintenance plans in 2005 Express so that I could
choose to create new plans, but no such selection is available to choose.
So, how do you create maintenance plans in SQL Server 2005 Express Edition?
Thanks!
childofthe1980s
Express doesn't come with SQL Agent. Have a look at these:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:2FC46958-D2C0-4120-B6C1-583FCE607044@.microsoft.com...
> Hello:
> I know how to create database maintenance plans (automated backups) in SQL
> Server 2000 and 2005.
> But, I have never done so in SQL Server 2005 Express Edition.
> I tried right-clicking on maintenance plans in 2005 Express so that I
> could
> choose to create new plans, but no such selection is available to choose.
> So, how do you create maintenance plans in SQL Server 2005 Express
> Edition?
> Thanks!
> childofthe1980s

database maintenance plans for 2005 Express

Hello:
I know how to create database maintenance plans (automated backups) in SQL
Server 2000 and 2005.
But, I have never done so in SQL Server 2005 Express Edition.
I tried right-clicking on maintenance plans in 2005 Express so that I could
choose to create new plans, but no such selection is available to choose.
So, how do you create maintenance plans in SQL Server 2005 Express Edition?
Thanks!
childofthe1980sExpress doesn't come with SQL Agent. Have a look at these:
http://blogs.msdn.com/rogerwolterbl.../13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:2FC46958-D2C0-4120-B6C1-583FCE607044@.microsoft.com...
> Hello:
> I know how to create database maintenance plans (automated backups) in SQL
> Server 2000 and 2005.
> But, I have never done so in SQL Server 2005 Express Edition.
> I tried right-clicking on maintenance plans in 2005 Express so that I
> could
> choose to create new plans, but no such selection is available to choose.
> So, how do you create maintenance plans in SQL Server 2005 Express
> Edition?
> Thanks!
> childofthe1980s

database maintenance plans for 2005 Express

Hello:
I know how to create database maintenance plans (automated backups) in SQL
Server 2000 and 2005.
But, I have never done so in SQL Server 2005 Express Edition.
I tried right-clicking on maintenance plans in 2005 Express so that I could
choose to create new plans, but no such selection is available to choose.
So, how do you create maintenance plans in SQL Server 2005 Express Edition?
Thanks!
childofthe1980sExpress doesn't come with SQL Agent. Have a look at these:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:2FC46958-D2C0-4120-B6C1-583FCE607044@.microsoft.com...
> Hello:
> I know how to create database maintenance plans (automated backups) in SQL
> Server 2000 and 2005.
> But, I have never done so in SQL Server 2005 Express Edition.
> I tried right-clicking on maintenance plans in 2005 Express so that I
> could
> choose to create new plans, but no such selection is available to choose.
> So, how do you create maintenance plans in SQL Server 2005 Express
> Edition?
> Thanks!
> childofthe1980s

database maintenance plan...

I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
automatically remove old transaction log backups. A couple of months ago,
the transaction log backups stop getting tossed. I don't think it has
anything to do with whether or not the db is set to do a simple backup.
I've split the dbs between different maintenance plans and have had no luck.
I'm thinking it has something to do with a hotfix because both SQL servers
had issues about the same time.
I didn't make any changes to any of the Dbs or the SQL server that I can
remember.
Do I need to register the SQL server with Active Directory? I upgraded the
network from NT4 to W2003 and AD.
Registering with AD should have no effect... Does the DBMaint Plan log show
any information?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Geiser" <glg.dell@.worldnet.att.net> wrote in message
news:%23CAcS9JaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
> automatically remove old transaction log backups. A couple of months ago,
> the transaction log backups stop getting tossed. I don't think it has
> anything to do with whether or not the db is set to do a simple backup.
> I've split the dbs between different maintenance plans and have had no
luck.
> I'm thinking it has something to do with a hotfix because both SQL servers
> had issues about the same time.
> I didn't make any changes to any of the Dbs or the SQL server that I can
> remember.
> Do I need to register the SQL server with Active Directory? I upgraded
the
> network from NT4 to W2003 and AD.
>

database maintenance plan...

I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
automatically remove old transaction log backups. A couple of months ago,
the transaction log backups stop getting tossed. I don't think it has
anything to do with whether or not the db is set to do a simple backup.
I've split the dbs between different maintenance plans and have had no luck.
I'm thinking it has something to do with a hotfix because both SQL servers
had issues about the same time.
I didn't make any changes to any of the Dbs or the SQL server that I can
remember.
Do I need to register the SQL server with Active Directory? I upgraded the
network from NT4 to W2003 and AD.Registering with AD should have no effect... Does the DBMaint Plan log show
any information?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Geiser" <glg.dell@.worldnet.att.net> wrote in message
news:%23CAcS9JaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
> automatically remove old transaction log backups. A couple of months ago,
> the transaction log backups stop getting tossed. I don't think it has
> anything to do with whether or not the db is set to do a simple backup.
> I've split the dbs between different maintenance plans and have had no
luck.
> I'm thinking it has something to do with a hotfix because both SQL servers
> had issues about the same time.
> I didn't make any changes to any of the Dbs or the SQL server that I can
> remember.
> Do I need to register the SQL server with Active Directory? I upgraded
the
> network from NT4 to W2003 and AD.
>

database maintenance plan...

I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
automatically remove old transaction log backups. A couple of months ago,
the transaction log backups stop getting tossed. I don't think it has
anything to do with whether or not the db is set to do a simple backup.
I've split the dbs between different maintenance plans and have had no luck.
I'm thinking it has something to do with a hotfix because both SQL servers
had issues about the same time.
I didn't make any changes to any of the Dbs or the SQL server that I can
remember.
Do I need to register the SQL server with Active Directory? I upgraded the
network from NT4 to W2003 and AD.Registering with AD should have no effect... Does the DBMaint Plan log show
any information?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Geiser" <glg.dell@.worldnet.att.net> wrote in message
news:%23CAcS9JaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
> automatically remove old transaction log backups. A couple of months ago,
> the transaction log backups stop getting tossed. I don't think it has
> anything to do with whether or not the db is set to do a simple backup.
> I've split the dbs between different maintenance plans and have had no
luck.
> I'm thinking it has something to do with a hotfix because both SQL servers
> had issues about the same time.
> I didn't make any changes to any of the Dbs or the SQL server that I can
> remember.
> Do I need to register the SQL server with Active Directory? I upgraded
the
> network from NT4 to W2003 and AD.
>

Database maintenance plan

I use the database maintenance plan to do my backups, I take all the defaults
and this works well for me but one system will not make a backup. Under jobs
all jobs fail but the database maintenance plan history is empty. the plan
name, server name and database name drop down boxes show all but not the
names. I have deleted and remade the jos still not working. What else can I
do.
ssovie
If all the scheduled jobs on that server failed, check the account that runs
SQLServerAgent. Maybe the account doesn't have proper rights to the server?
Incorrect password? Did you check Event Viewer? SQL Server Logs?
"SSovie" wrote:

> I use the database maintenance plan to do my backups, I take all the defaults
> and this works well for me but one system will not make a backup. Under jobs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can I
> do.
> --
> ssovie
|||The localsystem account starts SqlServerAgent, I tried to set ti to the
administrator but it had a logon failure when I tried to start it. I do not
see any errors in the event viewer or Sql logs that have to do with teh
maintenance plan
"Jack" wrote:
[vbcol=seagreen]
> If all the scheduled jobs on that server failed, check the account that runs
> SQLServerAgent. Maybe the account doesn't have proper rights to the server?
> Incorrect password? Did you check Event Viewer? SQL Server Logs?
> "SSovie" wrote:
|||Interesting... Why is it that you can't start the service using the
administrator account? Check User Right Assignment under Local Security
Settings in Administrative Tools and make sure the admin account can "Log On
As A Service"
"SSovie" wrote:
[vbcol=seagreen]
> The localsystem account starts SqlServerAgent, I tried to set ti to the
> administrator but it had a logon failure when I tried to start it. I do not
> see any errors in the event viewer or Sql logs that have to do with teh
> maintenance plan
> "Jack" wrote:
|||I checked local security settings and found under log on as a service local
policy setting for the administrator was not checked so I checked it. Also
checked Deny logon as a service was empty as was domain security settings. I
still can not start it as administrator.
"SSovie" wrote:

> I use the database maintenance plan to do my backups, I take all the defaults
> and this works well for me but one system will not make a backup. Under jobs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can I
> do.
> --
> ssovie

Database maintenance plan

I use the database maintenance plan to do my backups, I take all the defaults
and this works well for me but one system will not make a backup. Under jobs
all jobs fail but the database maintenance plan history is empty. the plan
name, server name and database name drop down boxes show all but not the
names. I have deleted and remade the jos still not working. What else can I
do.
--
ssovieIf all the scheduled jobs on that server failed, check the account that runs
SQLServerAgent. Maybe the account doesn't have proper rights to the server?
Incorrect password? Did you check Event Viewer? SQL Server Logs?
"SSovie" wrote:
> I use the database maintenance plan to do my backups, I take all the defaults
> and this works well for me but one system will not make a backup. Under jobs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can I
> do.
> --
> ssovie|||The localsystem account starts SqlServerAgent, I tried to set ti to the
administrator but it had a logon failure when I tried to start it. I do not
see any errors in the event viewer or Sql logs that have to do with teh
maintenance plan
"Jack" wrote:
> If all the scheduled jobs on that server failed, check the account that runs
> SQLServerAgent. Maybe the account doesn't have proper rights to the server?
> Incorrect password? Did you check Event Viewer? SQL Server Logs?
> "SSovie" wrote:
> > I use the database maintenance plan to do my backups, I take all the defaults
> > and this works well for me but one system will not make a backup. Under jobs
> > all jobs fail but the database maintenance plan history is empty. the plan
> > name, server name and database name drop down boxes show all but not the
> > names. I have deleted and remade the jos still not working. What else can I
> > do.
> > --
> > ssovie|||Interesting... Why is it that you can't start the service using the
administrator account? Check User Right Assignment under Local Security
Settings in Administrative Tools and make sure the admin account can "Log On
As A Service"
"SSovie" wrote:
> The localsystem account starts SqlServerAgent, I tried to set ti to the
> administrator but it had a logon failure when I tried to start it. I do not
> see any errors in the event viewer or Sql logs that have to do with teh
> maintenance plan
> "Jack" wrote:
> > If all the scheduled jobs on that server failed, check the account that runs
> > SQLServerAgent. Maybe the account doesn't have proper rights to the server?
> > Incorrect password? Did you check Event Viewer? SQL Server Logs?
> >
> > "SSovie" wrote:
> >
> > > I use the database maintenance plan to do my backups, I take all the defaults
> > > and this works well for me but one system will not make a backup. Under jobs
> > > all jobs fail but the database maintenance plan history is empty. the plan
> > > name, server name and database name drop down boxes show all but not the
> > > names. I have deleted and remade the jos still not working. What else can I
> > > do.
> > > --
> > > ssovie|||I checked local security settings and found under log on as a service local
policy setting for the administrator was not checked so I checked it. Also
checked Deny logon as a service was empty as was domain security settings. I
still can not start it as administrator.
"SSovie" wrote:
> I use the database maintenance plan to do my backups, I take all the defaults
> and this works well for me but one system will not make a backup. Under jobs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can I
> do.
> --
> ssovie

Database maintenance plan

I use the database maintenance plan to do my backups, I take all the default
s
and this works well for me but one system will not make a backup. Under jobs
all jobs fail but the database maintenance plan history is empty. the plan
name, server name and database name drop down boxes show all but not the
names. I have deleted and remade the jos still not working. What else can I
do.
--
ssovieIf all the scheduled jobs on that server failed, check the account that runs
SQLServerAgent. Maybe the account doesn't have proper rights to the server?
Incorrect password? Did you check Event Viewer? SQL Server Logs?
"SSovie" wrote:

> I use the database maintenance plan to do my backups, I take all the defau
lts
> and this works well for me but one system will not make a backup. Under jo
bs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can
I
> do.
> --
> ssovie|||The localsystem account starts SqlServerAgent, I tried to set ti to the
administrator but it had a logon failure when I tried to start it. I do not
see any errors in the event viewer or Sql logs that have to do with teh
maintenance plan
"Jack" wrote:
[vbcol=seagreen]
> If all the scheduled jobs on that server failed, check the account that ru
ns
> SQLServerAgent. Maybe the account doesn't have proper rights to the serve
r?
> Incorrect password? Did you check Event Viewer? SQL Server Logs?
> "SSovie" wrote:
>|||Interesting... Why is it that you can't start the service using the
administrator account? Check User Right Assignment under Local Security
Settings in Administrative Tools and make sure the admin account can "Log On
As A Service"
"SSovie" wrote:
[vbcol=seagreen]
> The localsystem account starts SqlServerAgent, I tried to set ti to the
> administrator but it had a logon failure when I tried to start it. I do no
t
> see any errors in the event viewer or Sql logs that have to do with teh
> maintenance plan
> "Jack" wrote:
>|||I checked local security settings and found under log on as a service local
policy setting for the administrator was not checked so I checked it. Also
checked Deny logon as a service was empty as was domain security settings. I
still can not start it as administrator.
"SSovie" wrote:

> I use the database maintenance plan to do my backups, I take all the defau
lts
> and this works well for me but one system will not make a backup. Under jo
bs
> all jobs fail but the database maintenance plan history is empty. the plan
> name, server name and database name drop down boxes show all but not the
> names. I have deleted and remade the jos still not working. What else can
I
> do.
> --
> ssovie

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
Kelly
Yes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:

> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>
|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.micro soft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
[vbcol=seagreen]
> "KellyLeia@.gmail.com" wrote:
|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> <EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.micro soft.com> wrote in message
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
KellyYes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:
> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
<EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
> "KellyLeia@.gmail.com" wrote:
>> Is there anyway to make the maintenance plan delete the backup files
>> BEFORE it backs up. My backups are failing due to lack of disk space,
>> because it essentially creates two copies of backups before deleting.
>> I know i can just write a script to delete them before hand, but it
>> would be alot neater to use the maintenace plan because the maintenance
>> plan is more dynamic that the script. Suggestions?
>> Kelly
>>|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> <EdgardoValdezMCTSMCITPMCSDMCDBA@.discussions.microsoft.com> wrote in message
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> > Yes. If you create a backup plan using the Database Maintenance Wizard,
> > you
> > can set the amount of time you want to keep the backup files. In SQL 2005
> > it
> > translates in a Maintenance Cleanup Task.
> >
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>
> > "KellyLeia@.gmail.com" wrote:
> >
> >> Is there anyway to make the maintenance plan delete the backup files
> >> BEFORE it backs up. My backups are failing due to lack of disk space,
> >> because it essentially creates two copies of backups before deleting.
> >> I know i can just write a script to delete them before hand, but it
> >> would be alot neater to use the maintenace plan because the maintenance
> >> plan is more dynamic that the script. Suggestions?
> >>
> >> Kelly
> >>
> >>

Database Maintenance - Order of Actions

Is there anyway to make the maintenance plan delete the backup files
BEFORE it backs up. My backups are failing due to lack of disk space,
because it essentially creates two copies of backups before deleting.
I know i can just write a script to delete them before hand, but it
would be alot neater to use the maintenace plan because the maintenance
plan is more dynamic that the script. Suggestions?
KellyYes. If you create a backup plan using the Database Maintenance Wizard, you
can set the amount of time you want to keep the backup files. In SQL 2005 it
translates in a Maintenance Cleanup Task.
"KellyLeia@.gmail.com" wrote:

> Is there anyway to make the maintenance plan delete the backup files
> BEFORE it backs up. My backups are failing due to lack of disk space,
> because it essentially creates two copies of backups before deleting.
> I know i can just write a script to delete them before hand, but it
> would be alot neater to use the maintenace plan because the maintenance
> plan is more dynamic that the script. Suggestions?
> Kelly
>|||"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
< EdgardoValdezMCTSMCITPMCSDMCDBA@.discussi
ons.microsoft.com> wrote in message
news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Yes. If you create a backup plan using the Database Maintenance Wizard,
> you
> can set the amount of time you want to keep the backup files. In SQL 2005
> it
> translates in a Maintenance Cleanup Task.
>
Right, 2000 is the same way. But as KellyLeia points out, that deletion
occurs AFTER the backup completes... if successul.
The problem is, if the backup fails, the deletion never occurs.
In addtion, I'd caution against doing what Kelly wants to do. Because if
the backup fails for another reason, you've already deleted one of your
backups.
Personally, I'd try to invest in more disk space if possible.
[vbcol=seagreen]
> "KellyLeia@.gmail.com" wrote:
>|||Conviently, we have tape backups that run at 4am (the db backups run
between 1 and 2 am). So if the job fails, we've still got yesterday's
backup on tape. If it fails as it did, i don't get in till 8am, so the
damage is done. Thanks for your help guys.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA"
> < EdgardoValdezMCTSMCITPMCSDMCDBA@.discussi
ons.microsoft.com> wrote in messa
ge
> news:33C5A070-12C7-4123-A72F-179C900DEC03@.microsoft.com...
> Right, 2000 is the same way. But as KellyLeia points out, that deletion
> occurs AFTER the backup completes... if successul.
> The problem is, if the backup fails, the deletion never occurs.
> In addtion, I'd caution against doing what Kelly wants to do. Because if
> the backup fails for another reason, you've already deleted one of your
> backups.
> Personally, I'd try to invest in more disk space if possible.
>

Database Maintenance - no backups being removed

Database maintenance plan doesn't allow me to choose to
delete files older than x number of days/weeks. I can
choose the option, input a number, but the drop down for
days/weeks contains no items.
I Jon,
a friend tell me to add in the registry the string value BackupDirectory
with default C: at the address
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\"Instance_Na me"\MSSQLServer...
and this work!!!
Mirco Scott
mailto: mscotta@.giada.it
Giada s.r.l.
<jon.epstein@.infov{REMOVE}.com> ha scritto nel messaggio
news:2cde01c42886$1f8f4bf0$a401280a@.phx.gbl...
> Database maintenance plan doesn't allow me to choose to
> delete files older than x number of days/weeks. I can
> choose the option, input a number, but the drop down for
> days/weeks contains no items.

Database Maintanence not deleting old backups

I have a database maintanence plan set up to backup all my SQL 2000
databases and to delete the backups older than 3 days. It doesn't delete the
old ones. Is this a bug or am I just missing something?You may be missing something.
Check the extention that you defined to delete. Check the folder that you
chose to delete the backup files.
Also, check the maintanence plan history for more info.
--
Ekrem Önsoy
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uO3WSRoNIHA.5360@.TK2MSFTNGP03.phx.gbl...
>I have a database maintanence plan set up to backup all my SQL 2000
>databases and to delete the backups older than 3 days. It doesn't delete
>the old ones. Is this a bug or am I just missing something?
>

Database Maintanence not deleting old backups

I have a database maintanence plan set up to backup all my SQL 2000
databases and to delete the backups older than 3 days. It doesn't delete the
old ones. Is this a bug or am I just missing something?
You may be missing something.
Check the extention that you defined to delete. Check the folder that you
chose to delete the backup files.
Also, check the maintanence plan history for more info.
Ekrem nsoy
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uO3WSRoNIHA.5360@.TK2MSFTNGP03.phx.gbl...
>I have a database maintanence plan set up to backup all my SQL 2000
>databases and to delete the backups older than 3 days. It doesn't delete
>the old ones. Is this a bug or am I just missing something?
>

Database Maintanence not deleting old backups

I have a database maintanence plan set up to backup all my SQL 2000
databases and to delete the backups older than 3 days. It doesn't delete the
old ones. Is this a bug or am I just missing something?You may be missing something.
Check the extention that you defined to delete. Check the folder that you
chose to delete the backup files.
Also, check the maintanence plan history for more info.
Ekrem nsoy
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uO3WSRoNIHA.5360@.TK2MSFTNGP03.phx.gbl...
>I have a database maintanence plan set up to backup all my SQL 2000
>databases and to delete the backups older than 3 days. It doesn't delete
>the old ones. Is this a bug or am I just missing something?
>

Database losing changes

I have a SQL Server 2000 SP2 database. I can work on it all day, and everything is fine. Backups run at 7:00pm successfully. At some point after that, usually about midnight, all changes are rolled back. I can restore from the 7:00pm backup, but overnight every change (including those restored) will again disappear.

I have tinkered for two weeks, checking every setting and running every automated job (there are three) interactively. Nothing seems to cause or cure this problem. But every morning when I come in, my database is back to where it was on or about March 15.

One possible clue: there is a SQL log entry stating "Transaction Log Truncated" that appears to happen about the same time - midnight. I do not know why this is happening or whether it is significatnt; I truncated the transaction log interactively, with no negative effect. Also, the problems began after I installed SP2.

Any ideas?Probably best to leave the profiler running - logging t a text file to find out whats happenning.

It sounds like something is restoring a backup.
If your backup restore does hold the data then it gets lost again at midnight then there must be a task that is deleting everything that has been added after march 15 or a backup is being restored from that date.

Try creating a new table - if that disappears then you will now that it isn't just a task that deleted data.

Check with the network people that they aren't trying to backup the disk. This will probably lock the database files and crash the server. If you are holding open transactions then all changes in those transactions would be lost.