database move

I recently moved a database and its transaction log file to another drive due
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
> > I recently moved a database and its transaction log file to another drive due
> > to space issues. I followed the kb article 224071 on how to move a user
> > database and it appears that it worked correctly.
> >
> > However, now the database comes up as "read-only" and if I view the
> > properties of the db and try to remove the "read-only" designation, I get an
> > error that it can't.
> >
> > Any thoughts'
> >
> > Robert|||Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
> > Hi Robert
> >
> > Check the SQL Server error log to see if there is any information on the
> > problem. You may also want to check that the readonly attribute is not set on
> > the mdf and ldf files.
> >
> > John
> >
> > "Robert Gandrud" wrote:
> >
> > > I recently moved a database and its transaction log file to another drive due
> > > to space issues. I followed the kb article 224071 on how to move a user
> > > database and it appears that it worked correctly.
> > >
> > > However, now the database comes up as "read-only" and if I view the
> > > properties of the db and try to remove the "read-only" designation, I get an
> > > error that it can't.
> > >
> > > Any thoughts'
> > >
> > > Robertsql

Database Migration

Hello.
I'm going to migrate an entrie SQL Server to a new box. The original plan
was to backup and restore all DBs on the new server, but due the long time o
f
the backup I'd like to try other way.
I'm thinking of simply offline->copy->attach .MDF to the new server, but as
the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if a
simple attach is risk-free (basically in terms of systables/sysprocedures) o
r
maybe exist a better way to do it.
What can you advice me?
Thanks
RodrigoBackups should give you much less downtime than you can achieve by
detaching and re-attaching:
1. Database backup on Server A
2. Restore to new Server B
3. Set A to single-user mode
4. Transaction log backup on A
5. Restore transaction log(s) on B
Make sure you read the following article. Particularly the references
to orphaned users:
http://support.microsoft.com/?id=314546
David Portas
SQL Server MVP
--|||Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
> Hello.
> I'm going to migrate an entrie SQL Server to a new box. The original plan
> was to backup and restore all DBs on the new server, but due the long time
> of
> the backup I'd like to try other way.
> I'm thinking of simply offline->copy->attach .MDF to the new server, but
> as
> the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if
> a
> simple attach is risk-free (basically in terms of systables/sysprocedures)
> or
> maybe exist a better way to do it.
> What can you advice me?
> Thanks
> Rodrigo|||Thanks David & Narayana; both excellent links.
There they solve the doubt about moving DBs between SQL versions (answer: no
problem).
The orphan users were been considerated too.
Thanks
"Narayana Vyas Kondreddi" wrote:

> Have a look at this article: http://vyaskn.tripod.com/moving_sql...skn.tripod.com/
>
> "Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in messag
e
> news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
>
>sql

Database Migration

Hello.
I'm going to migrate an entrie SQL Server to a new box. The original plan
was to backup and restore all DBs on the new server, but due the long time of
the backup I'd like to try other way.
I'm thinking of simply offline->copy->attach .MDF to the new server, but as
the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if a
simple attach is risk-free (basically in terms of systables/sysprocedures) or
maybe exist a better way to do it.
What can you advice me?
Thanks
Rodrigo
Backups should give you much less downtime than you can achieve by
detaching and re-attaching:
1. Database backup on Server A
2. Restore to new Server B
3. Set A to single-user mode
4. Transaction log backup on A
5. Restore transaction log(s) on B
Make sure you read the following article. Particularly the references
to orphaned users:
http://support.microsoft.com/?id=314546
David Portas
SQL Server MVP
|||Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
> Hello.
> I'm going to migrate an entrie SQL Server to a new box. The original plan
> was to backup and restore all DBs on the new server, but due the long time
> of
> the backup I'd like to try other way.
> I'm thinking of simply offline->copy->attach .MDF to the new server, but
> as
> the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if
> a
> simple attach is risk-free (basically in terms of systables/sysprocedures)
> or
> maybe exist a better way to do it.
> What can you advice me?
> Thanks
> Rodrigo
|||Thanks David & Narayana; both excellent links.
There they solve the doubt about moving DBs between SQL versions (answer: no
problem).
The orphan users were been considerated too.
Thanks
"Narayana Vyas Kondreddi" wrote:

> Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
> news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
>
>

Database Migration

Hello.
I'm going to migrate an entrie SQL Server to a new box. The original plan
was to backup and restore all DBs on the new server, but due the long time of
the backup I'd like to try other way.
I'm thinking of simply offline->copy->attach .MDF to the new server, but as
the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if a
simple attach is risk-free (basically in terms of systables/sysprocedures) or
maybe exist a better way to do it.
What can you advice me?
Thanks
RodrigoBackups should give you much less downtime than you can achieve by
detaching and re-attaching:
1. Database backup on Server A
2. Restore to new Server B
3. Set A to single-user mode
4. Transaction log backup on A
5. Restore transaction log(s) on B
Make sure you read the following article. Particularly the references
to orphaned users:
http://support.microsoft.com/?id=314546
--
David Portas
SQL Server MVP
--|||Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
> Hello.
> I'm going to migrate an entrie SQL Server to a new box. The original plan
> was to backup and restore all DBs on the new server, but due the long time
> of
> the backup I'd like to try other way.
> I'm thinking of simply offline->copy->attach .MDF to the new server, but
> as
> the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if
> a
> simple attach is risk-free (basically in terms of systables/sysprocedures)
> or
> maybe exist a better way to do it.
> What can you advice me?
> Thanks
> Rodrigo|||Thanks David & Narayana; both excellent links.
There they solve the doubt about moving DBs between SQL versions (answer: no
problem).
The orphan users were been considerated too.
Thanks
"Narayana Vyas Kondreddi" wrote:
> Have a look at this article: http://vyaskn.tripod.com/moving_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Rodrigo Guerra" <RodrigoGuerra@.discussions.microsoft.com> wrote in message
> news:D364210D-6F34-4806-B58F-6DA671E2A248@.microsoft.com...
> > Hello.
> >
> > I'm going to migrate an entrie SQL Server to a new box. The original plan
> > was to backup and restore all DBs on the new server, but due the long time
> > of
> > the backup I'd like to try other way.
> >
> > I'm thinking of simply offline->copy->attach .MDF to the new server, but
> > as
> > the new server has SQL-SP4 and the old one has SQL-SP3 I wanted to know if
> > a
> > simple attach is risk-free (basically in terms of systables/sysprocedures)
> > or
> > maybe exist a better way to do it.
> >
> > What can you advice me?
> >
> > Thanks
> > Rodrigo
>
>sql

Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
TaliaFrom EM or Query Analyzer, are you able to do anything ? If you can, create
a new database and copy all the data from the bad db to the new db.
-Jimmy
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.

>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
> ****************************************
******************
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
Talia
From EM or Query Analyzer, are you able to do anything ? If you can, create a new database and copy all the data from the bad db to the new db.
-Jimmy
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>
|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.

>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
>************************************************* *********
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
TaliaFrom EM or Query Analyzer, are you able to do anything ? If you can, create a new database and copy all the data from the bad db to the new db.
-Jimmy
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.
>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
>**********************************************************
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

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.
>