Backing up online log after loss of primary data file

My question is this:

Is there anyway to backup the current online log after complete loss of the current correponding datafile?

Example:

(2) Logical Disk Volumes

Disk 1 (D:) contains pubs_data.mdf
Disk 2 (E:) contains pubs_log.ldf

Disk 1 becomes corrupt and goes offline leaving the the database pubs in a suspect state. Is backup of the current online log pubs_log.ldf possible? If a backup of the log is not possible are there any other restoration methods that can be used to bring this database back online, rescuing as much information as possible from the current online log pubs_log.ldf.

Thanks,
PI can't beleive there is no way to get at the information in the current online log depite the fact that the database file is gone.

So, if I can't get at the current online log I must rely on a backup of the log which I would take every 5 or 15 minutes but that 0-5 minute period is not retrievable even if its just the data file that fails.

Any thoughts?

Backing up MS SQL?

Hi

I'm hoping that some could point me in the right direction for "best
practice" in:

1) securely backing up a complete sql7 and 2000 server containing many
databases.

2) backing up an individual database

3) Securing the transaction on the hour as a mile stone to return to in the
event of error or system failure.

Thanks

MarkHi

The first place to start is Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
insql.chm::/ad_bkprst_9zcj.htm

John

"mark" <kardtech@.on.aibn.com> wrote in message
news:uFRIb.46$b06.7@.newsfep3-gui.server.ntli.net...
> Hi
> I'm hoping that some could point me in the right direction for "best
> practice" in:
> 1) securely backing up a complete sql7 and 2000 server containing many
> databases.
> 2) backing up an individual database
> 3) Securing the transaction on the hour as a mile stone to return to in
the
> event of error or system failure.
> Thanks
> Mark|||Standard Answer then. Thanks for NO help

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:b8UIb.3645$PB3.32098570@.news-text.cableinet.net...
> Hi
> The first place to start is Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
> insql.chm::/ad_bkprst_9zcj.htm
> John
> "mark" <kardtech@.on.aibn.com> wrote in message
> news:uFRIb.46$b06.7@.newsfep3-gui.server.ntli.net...
> > Hi
> > I'm hoping that some could point me in the right direction for "best
> > practice" in:
> > 1) securely backing up a complete sql7 and 2000 server containing many
> > databases.
> > 2) backing up an individual database
> > 3) Securing the transaction on the hour as a mile stone to return to in
> the
> > event of error or system failure.
> > Thanks
> > Mark|||"mark" <kardtech@.on.aibn.com> wrote in message
news:iuUIb.85$b06.63@.newsfep3-gui.server.ntli.net...
> Standard Answer then. Thanks for NO help

What about the standard answer don't you like?

With more detail:

1) For backing up the actual installation, I generally find it easier to
reinstall the application if need be.

2) Backing up individual databases, look into creating a maintenance plan.
Or roll your own and use backup database as appropriate.

3) use BACKUP LOG on the required databases.

I'd also recommend getting a book on high availabilibity. The one from MS
press is quite good.

>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:b8UIb.3645$PB3.32098570@.news-text.cableinet.net...
> > Hi
> > The first place to start is Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
> > insql.chm::/ad_bkprst_9zcj.htm
> > John
> > "mark" <kardtech@.on.aibn.com> wrote in message
> > news:uFRIb.46$b06.7@.newsfep3-gui.server.ntli.net...
> > > Hi
> > > > I'm hoping that some could point me in the right direction for "best
> > > practice" in:
> > > > 1) securely backing up a complete sql7 and 2000 server containing many
> > > databases.
> > > > 2) backing up an individual database
> > > > 3) Securing the transaction on the hour as a mile stone to return to
in
> > the
> > > event of error or system failure.
> > > > Thanks
> > > > Mark
> >|||Thankyou

"Greg D. Moore (Strider)" <mooregr@.greenms.com> wrote in message
news:7oWIb.34408$q55.5924@.twister.nyroc.rr.com...
> "mark" <kardtech@.on.aibn.com> wrote in message
> news:iuUIb.85$b06.63@.newsfep3-gui.server.ntli.net...
> > Standard Answer then. Thanks for NO help
> What about the standard answer don't you like?
> With more detail:
> 1) For backing up the actual installation, I generally find it easier to
> reinstall the application if need be.
> 2) Backing up individual databases, look into creating a maintenance plan.
> Or roll your own and use backup database as appropriate.
> 3) use BACKUP LOG on the required databases.
> I'd also recommend getting a book on high availabilibity. The one from MS
> press is quite good.
>
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:b8UIb.3645$PB3.32098570@.news-text.cableinet.net...
> > > Hi
> > > > The first place to start is Books Online:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adm
> > > insql.chm::/ad_bkprst_9zcj.htm
> > > > John
> > > > "mark" <kardtech@.on.aibn.com> wrote in message
> > > news:uFRIb.46$b06.7@.newsfep3-gui.server.ntli.net...
> > > > Hi
> > > > > > I'm hoping that some could point me in the right direction for "best
> > > > practice" in:
> > > > > > 1) securely backing up a complete sql7 and 2000 server containing
many
> > > > databases.
> > > > > > 2) backing up an individual database
> > > > > > 3) Securing the transaction on the hour as a mile stone to return to
> in
> > > the
> > > > event of error or system failure.
> > > > > > Thanks
> > > > > > Mark
> > > > > >|||"mark" <kardtech@.on.aibn.com> wrote in message
news:uFRIb.46$b06.7@.newsfep3-gui.server.ntli.net...
> Hi
> I'm hoping that some could point me in the right direction for "best
> practice" in:
> 1) securely backing up a complete sql7 and 2000 server containing many
> databases.

1.1) Schedule a complete backup of all production databases and include
the system databases master, msdb, model at some appropriate
timeslot.
To do this for the first time, try using Enterprise Manager, select
the
"schedule" option and then examine the TSQL code in the scheduled
task. Modify the schedule times to suit your operations.

1.2) Take a copy of the backup files offsite.

> 2) backing up an individual database

As above, so here below ... much like the
Hermetic tradition.

> 3) Securing the transaction on the hour as a mile stone to return to in
the
> event of error or system failure.

You will need to examine the differential backup strategies available
and gauge the best approach for this method in relation to your data.

The M$ documentation is not too bad at all if you are prepared to
go and experiment and learn by practice the operations therein
documented, and I'd be surprised if step by step instructions on
all of the above are not clearly established within it.

Pete Brown
Falls Creek
OZ

Backing up a DataBase

Hello:
I want to schedule a backup of a Database where a complete backup is done
keeping the Last 10 Complete BackUps Available at all times.
The Backup device file should be something like the DBName+SystemDateTime.
After 10days these backup files will be removed after being copied to
someother location and the whole process of starts all over again from
complete backup1 to complete back10..
Pls suggest some way of doing it.
Thank you.
Hi
The Maintenace Wizard works on this priciple. You define the backup
retention period, and after xx days, the oldest backup is deleted.
If you need to copy the backup off to another server, setup a seperate job,
using XCOPY
Regards
Mike
"Robin Chatterjee" wrote:

> Hello:
> I want to schedule a backup of a Database where a complete backup is done
> keeping the Last 10 Complete BackUps Available at all times.
> The Backup device file should be something like the DBName+SystemDateTime.
> After 10days these backup files will be removed after being copied to
> someother location and the whole process of starts all over again from
> complete backup1 to complete back10..
> Pls suggest some way of doing it.
>
> Thank you.
>

Backing up a DataBase

Hello:
I want to schedule a backup of a Database where a complete backup is done
keeping the Last 10 Complete BackUps Available at all times.
The Backup device file should be something like the DBName+SystemDateTime.
After 10days these backup files will be removed after being copied to
someother location and the whole process of starts all over again from
complete backup1 to complete back10..
Pls suggest some way of doing it.
Thank you.Hi
The Maintenace Wizard works on this priciple. You define the backup
retention period, and after xx days, the oldest backup is deleted.
If you need to copy the backup off to another server, setup a seperate job,
using XCOPY
Regards
Mike
"Robin Chatterjee" wrote:

> Hello:
> I want to schedule a backup of a Database where a complete backup is done
> keeping the Last 10 Complete BackUps Available at all times.
> The Backup device file should be something like the DBName+SystemDateTime.
> After 10days these backup files will be removed after being copied to
> someother location and the whole process of starts all over again from
> complete backup1 to complete back10..
> Pls suggest some way of doing it.
>
> Thank you.
>

Backing up a DataBase

Hello:
I want to schedule a backup of a Database where a complete backup is done
keeping the Last 10 Complete BackUps Available at all times.
The Backup device file should be something like the DBName+SystemDateTime.
After 10days these backup files will be removed after being copied to
someother location and the whole process of starts all over again from
complete backup1 to complete back10..
Pls suggest some way of doing it.
Thank you.Hi
The Maintenace Wizard works on this priciple. You define the backup
retention period, and after xx days, the oldest backup is deleted.
If you need to copy the backup off to another server, setup a seperate job,
using XCOPY
Regards
Mike
"Robin Chatterjee" wrote:
> Hello:
> I want to schedule a backup of a Database where a complete backup is done
> keeping the Last 10 Complete BackUps Available at all times.
> The Backup device file should be something like the DBName+SystemDateTime.
> After 10days these backup files will be removed after being copied to
> someother location and the whole process of starts all over again from
> complete backup1 to complete back10..
> Pls suggest some way of doing it.
>
> Thank you.
>sql

autoshrink causes "Could not complete cursor operation...table schema changed&quo

I've had a report that turning on autoshrink causes the error "Could not
complete cursor operation because the table schema changed after the cursor
was declared"
Has anyone saw this before ?
If a maintenance plan to re-index kicked in during some long running process
caused this error, that I could understand. But why would autoshrink cause
this ? The only thing I can speculate is that autoshrink (which happens ever
y
30 minutes I believe) is taking a sufficiently long time to run, that it may
be timing out the long running application process.
Autoshrink was turned on as a way to keep physical transaction log size unde
r
control [yes, I know, not a good idea]
Thanks,
Andy Mackie.That is not the way to keep the log file under control. Either it needs
that much space or it doesn't. If it doesn't it wont grow past it unless
you are not doing regular log backups or you have long running open
transactions. If it does need more space than you have you are only hurting
the situation by shrinking it.
Andrew J. Kelly SQL MVP
"A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
news:xn0e2pqso958n30000@.news.microsoft.com...
> I've had a report that turning on autoshrink causes the error "Could not
> complete cursor operation because the table schema changed after the
> cursor
> was declared"
> Has anyone saw this before ?
> If a maintenance plan to re-index kicked in during some long running
> process
> caused this error, that I could understand. But why would autoshrink cause
> this ? The only thing I can speculate is that autoshrink (which happens
> every
> 30 minutes I believe) is taking a sufficiently long time to run, that it
> may
> be timing out the long running application process.
> Autoshrink was turned on as a way to keep physical transaction log size
> under
> control [yes, I know, not a good idea]
> Thanks,
> Andy Mackie.|||And shrink (auto or otherwise) _may_ cause the error as it has to move IAM
pages. Depending on the kind of internal scan the cursor was doing, the
locks required to move the IAM page may be interpreted the by the scan as
the IAM chain changing and hence the schema changing. I'm not 100% sure but
it kind of makes sense to me. If its causing you difficulty and you can't
turn off autoshrink (you really should..), call PSS to help you.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#qXFTUhYFHA.3096@.TK2MSFTNGP15.phx.gbl...
> That is not the way to keep the log file under control. Either it needs
> that much space or it doesn't. If it doesn't it wont grow past it unless
> you are not doing regular log backups or you have long running open
> transactions. If it does need more space than you have you are only
hurting
> the situation by shrinking it.
> --
> Andrew J. Kelly SQL MVP
>
> "A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
> news:xn0e2pqso958n30000@.news.microsoft.com...
cause[vbcol=seagreen]
>|||Paul S Randal [MS] wrote:

> And shrink (auto or otherwise) may cause the error as it has to move IAM
> pages. Depending on the kind of internal scan the cursor was doing, the
> locks required to move the IAM page may be interpreted the by the scan as
> the IAM chain changing and hence the schema changing. I'm not 100% sure bu
t
> it kind of makes sense to me. If its causing you difficulty and you can't
> turn off autoshrink (you really should..), call PSS to help you.
> Thanks
OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's maintenanc
e
plans and re-indexing, that is the real problem (all 2nd hand info).
I've recommended to turn off auto-shrink, make sure no re-indexing is
occuring at the time of the processing, and make sure regular log backups ar
e
scheduled, to keep the log file under control. Hopefully that will sort
things out.
Thanks,
Andy.|||A Mackie wrote:

> Paul S Randal [MS] wrote:
>
> OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's
> maintenance plans and re-indexing, that is the real problem (all 2nd hand
> info).
> I've recommended to turn off auto-shrink, make sure no re-indexing is
> occuring at the time of the processing, and make sure regular log backups
> are scheduled, to keep the log file under control. Hopefully that will sor
t
> things out.
> Thanks,
> Andy.
Looks like there was a maintenance plan that included a re-index scheduled t
o
run at the same time as some cursor-processing.
Andy.

autoshrink causes "Could not complete cursor operation...table schema changed"

I've had a report that turning on autoshrink causes the error "Could not
complete cursor operation because the table schema changed after the cursor
was declared"
Has anyone saw this before ?
If a maintenance plan to re-index kicked in during some long running process
caused this error, that I could understand. But why would autoshrink cause
this ? The only thing I can speculate is that autoshrink (which happens every
30 minutes I believe) is taking a sufficiently long time to run, that it may
be timing out the long running application process.
Autoshrink was turned on as a way to keep physical transaction log size under
control [yes, I know, not a good idea]
Thanks,
Andy Mackie.
That is not the way to keep the log file under control. Either it needs
that much space or it doesn't. If it doesn't it wont grow past it unless
you are not doing regular log backups or you have long running open
transactions. If it does need more space than you have you are only hurting
the situation by shrinking it.
Andrew J. Kelly SQL MVP
"A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
news:xn0e2pqso958n30000@.news.microsoft.com...
> I've had a report that turning on autoshrink causes the error "Could not
> complete cursor operation because the table schema changed after the
> cursor
> was declared"
> Has anyone saw this before ?
> If a maintenance plan to re-index kicked in during some long running
> process
> caused this error, that I could understand. But why would autoshrink cause
> this ? The only thing I can speculate is that autoshrink (which happens
> every
> 30 minutes I believe) is taking a sufficiently long time to run, that it
> may
> be timing out the long running application process.
> Autoshrink was turned on as a way to keep physical transaction log size
> under
> control [yes, I know, not a good idea]
> Thanks,
> Andy Mackie.
|||And shrink (auto or otherwise) _may_ cause the error as it has to move IAM
pages. Depending on the kind of internal scan the cursor was doing, the
locks required to move the IAM page may be interpreted the by the scan as
the IAM chain changing and hence the schema changing. I'm not 100% sure but
it kind of makes sense to me. If its causing you difficulty and you can't
turn off autoshrink (you really should..), call PSS to help you.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#qXFTUhYFHA.3096@.TK2MSFTNGP15.phx.gbl...
> That is not the way to keep the log file under control. Either it needs
> that much space or it doesn't. If it doesn't it wont grow past it unless
> you are not doing regular log backups or you have long running open
> transactions. If it does need more space than you have you are only
hurting[vbcol=seagreen]
> the situation by shrinking it.
> --
> Andrew J. Kelly SQL MVP
>
> "A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
> news:xn0e2pqso958n30000@.news.microsoft.com...
cause
>
|||Paul S Randal [MS] wrote:

> And shrink (auto or otherwise) may cause the error as it has to move IAM
> pages. Depending on the kind of internal scan the cursor was doing, the
> locks required to move the IAM page may be interpreted the by the scan as
> the IAM chain changing and hence the schema changing. I'm not 100% sure but
> it kind of makes sense to me. If its causing you difficulty and you can't
> turn off autoshrink (you really should..), call PSS to help you.
> Thanks
OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's maintenance
plans and re-indexing, that is the real problem (all 2nd hand info).
I've recommended to turn off auto-shrink, make sure no re-indexing is
occuring at the time of the processing, and make sure regular log backups are
scheduled, to keep the log file under control. Hopefully that will sort
things out.
Thanks,
Andy.
|||A Mackie wrote:

> Paul S Randal [MS] wrote:
>
> OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's
> maintenance plans and re-indexing, that is the real problem (all 2nd hand
> info).
> I've recommended to turn off auto-shrink, make sure no re-indexing is
> occuring at the time of the processing, and make sure regular log backups
> are scheduled, to keep the log file under control. Hopefully that will sort
> things out.
> Thanks,
> Andy.
Looks like there was a maintenance plan that included a re-index scheduled to
run at the same time as some cursor-processing.
Andy.

autoshrink causes "Could not complete cursor operation...table schema changed"

I've had a report that turning on autoshrink causes the error "Could not
complete cursor operation because the table schema changed after the cursor
was declared"
Has anyone saw this before ?
If a maintenance plan to re-index kicked in during some long running process
caused this error, that I could understand. But why would autoshrink cause
this ? The only thing I can speculate is that autoshrink (which happens every
30 minutes I believe) is taking a sufficiently long time to run, that it may
be timing out the long running application process.
Autoshrink was turned on as a way to keep physical transaction log size under
control [yes, I know, not a good idea]
Thanks,
Andy Mackie.That is not the way to keep the log file under control. Either it needs
that much space or it doesn't. If it doesn't it wont grow past it unless
you are not doing regular log backups or you have long running open
transactions. If it does need more space than you have you are only hurting
the situation by shrinking it.
--
Andrew J. Kelly SQL MVP
"A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
news:xn0e2pqso958n30000@.news.microsoft.com...
> I've had a report that turning on autoshrink causes the error "Could not
> complete cursor operation because the table schema changed after the
> cursor
> was declared"
> Has anyone saw this before ?
> If a maintenance plan to re-index kicked in during some long running
> process
> caused this error, that I could understand. But why would autoshrink cause
> this ? The only thing I can speculate is that autoshrink (which happens
> every
> 30 minutes I believe) is taking a sufficiently long time to run, that it
> may
> be timing out the long running application process.
> Autoshrink was turned on as a way to keep physical transaction log size
> under
> control [yes, I know, not a good idea]
> Thanks,
> Andy Mackie.|||And shrink (auto or otherwise) _may_ cause the error as it has to move IAM
pages. Depending on the kind of internal scan the cursor was doing, the
locks required to move the IAM page may be interpreted the by the scan as
the IAM chain changing and hence the schema changing. I'm not 100% sure but
it kind of makes sense to me. If its causing you difficulty and you can't
turn off autoshrink (you really should..), call PSS to help you.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#qXFTUhYFHA.3096@.TK2MSFTNGP15.phx.gbl...
> That is not the way to keep the log file under control. Either it needs
> that much space or it doesn't. If it doesn't it wont grow past it unless
> you are not doing regular log backups or you have long running open
> transactions. If it does need more space than you have you are only
hurting
> the situation by shrinking it.
> --
> Andrew J. Kelly SQL MVP
>
> "A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
> news:xn0e2pqso958n30000@.news.microsoft.com...
> > I've had a report that turning on autoshrink causes the error "Could not
> > complete cursor operation because the table schema changed after the
> > cursor
> > was declared"
> >
> > Has anyone saw this before ?
> >
> > If a maintenance plan to re-index kicked in during some long running
> > process
> > caused this error, that I could understand. But why would autoshrink
cause
> > this ? The only thing I can speculate is that autoshrink (which happens
> > every
> > 30 minutes I believe) is taking a sufficiently long time to run, that it
> > may
> > be timing out the long running application process.
> >
> > Autoshrink was turned on as a way to keep physical transaction log size
> > under
> > control [yes, I know, not a good idea]
> >
> > Thanks,
> > Andy Mackie.
>|||Paul S Randal [MS] wrote:
> And shrink (auto or otherwise) may cause the error as it has to move IAM
> pages. Depending on the kind of internal scan the cursor was doing, the
> locks required to move the IAM page may be interpreted the by the scan as
> the IAM chain changing and hence the schema changing. I'm not 100% sure but
> it kind of makes sense to me. If its causing you difficulty and you can't
> turn off autoshrink (you really should..), call PSS to help you.
> Thanks
OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's maintenance
plans and re-indexing, that is the real problem (all 2nd hand info).
I've recommended to turn off auto-shrink, make sure no re-indexing is
occuring at the time of the processing, and make sure regular log backups are
scheduled, to keep the log file under control. Hopefully that will sort
things out.
Thanks,
Andy.|||A Mackie wrote:
> Paul S Randal [MS] wrote:
> > And shrink (auto or otherwise) may cause the error as it has to move IAM
> > pages. Depending on the kind of internal scan the cursor was doing, the
> > locks required to move the IAM page may be interpreted the by the scan as
> > the IAM chain changing and hence the schema changing. I'm not 100% sure
> > but it kind of makes sense to me. If its causing you difficulty and you
> > can't turn off autoshrink (you really should..), call PSS to help you.
> >
> > Thanks
> OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's
> maintenance plans and re-indexing, that is the real problem (all 2nd hand
> info).
> I've recommended to turn off auto-shrink, make sure no re-indexing is
> occuring at the time of the processing, and make sure regular log backups
> are scheduled, to keep the log file under control. Hopefully that will sort
> things out.
> Thanks,
> Andy.
Looks like there was a maintenance plan that included a re-index scheduled to
run at the same time as some cursor-processing.
Andy.