Recovery Models

In an attempt to manage the size of the transaction log , I would like to use
the
Simple verses the Full Recovery model. Other than the loss of transaction log
backup and recovery only to the last full or differential backup, are
there any
other “landmines” I should consider? Thanks for being there to help. Regards
Ed.
No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
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
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>
|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>
|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas

"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.

Recovery Models

In an attempt to manage the size of the transaction log , I would like to us
e
the
Simple verses the Full Recovery model. Other than the loss of transaction lo
g
backup and recovery only to the last full or differential backup, are
there any
other “landmines” I should consider? Thanks for being there to help. Reg
ards
Ed.No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
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
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.

Recovery Models

In an attempt to manage the size of the transaction log , I would like to use
the
Simple verses the Full Recovery model. Other than the loss of transaction log
backup and recovery only to the last full or differential backup, are
there any
other â'landminesâ' I should consider? Thanks for being there to help. Regards
Ed.No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
--
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
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.

Recovering using the log file

I have a situation where the server and data files were located on the C:
drive, and the log files on the F: drive.
The C: drive crashed, total loss. But I still have full backups for the
databases and also their log files.
Is there a way of recovering my DBs to the point of failure using the
backups and log files?
Everything I see in the BOL refer to use a log backup. I don't have log
backups, just the log files themselves.
ThanksIf the database is in full recovery mode, and no-one has truncated the log,
you can do:
BACKUP LOG dbname TO ... WITH NO_TRUNCATE
If the database is in simple recovery mode, then SQL Server will truncate
the log at each checkpoint. This means that the information you need is no
longer available in the transaction log files.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dallara" <someone@.microsoft.com> wrote in message
news:OPpd5co%23DHA.3232@.TK2MSFTNGP10.phx.gbl...
> I have a situation where the server and data files were located on the C:
> drive, and the log files on the F: drive.
> The C: drive crashed, total loss. But I still have full backups for the
> databases and also their log files.
> Is there a way of recovering my DBs to the point of failure using the
> backups and log files?
> Everything I see in the BOL refer to use a log backup. I don't have log
> backups, just the log files themselves.
> Thanks
>|||They were in full recovery.
Thanks for yr help, but the point you missed here is that the DBs are gone,
the server is gone as well.
So "BACKUP LOG dbname TO ... WITH NO_TRUNCATE" doesn't work as "dbname"
doesn't exist.
I've set up a new server, and I want to recreate the databases using the
full backups and log files that I have.
I know how to recover the DBs to the point of their full backup. I'm
wondering if there's a way of recover them to the point of failure using
their log files.
>"Tibor Karaszi" wrote in message
>If the database is in full recovery mode, and no-one has truncated the log,
>you can do:
>BACKUP LOG dbname TO ... WITH NO_TRUNCATE
>If the database is in simple recovery mode, then SQL Server will truncate
>the log at each checkpoint. This means that the information you need is no
>longer available in the transaction log files.
> Tibor Karaszi, SQL Server MVP
>
C:|||OK, so the SQL Server install in itself is toast...
The concept to save this is to have your new installation with a working SQL
Server, create a database with the same name and same file layout as the old
one. Stop that SQL Server, delete the mdf, ndf and ldf files. Then copy over
the ldf file from the broken install. Start SQL Server. Database is now
suspect (the mdf and ndf files are missing). You now do
BACKUP LOG dbname TO DISK = 'C:\...' WITH NO_TRUNCATE
Then restore the db backup and that log backups.
Above assumes that you indeed have all log records available in the ldf
file, since the db backup was taken.
There's a KB on the subject, I don't have the number, but it shouldn't be
hard to find with a little bit of searching...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dallara" <someone@.microsoft.com> wrote in message
news:OcVlVay%23DHA.1212@.TK2MSFTNGP12.phx.gbl...
> They were in full recovery.
> Thanks for yr help, but the point you missed here is that the DBs are
gone,
> the server is gone as well.
> So "BACKUP LOG dbname TO ... WITH NO_TRUNCATE" doesn't work as "dbname"
> doesn't exist.
> I've set up a new server, and I want to recreate the databases using the
> full backups and log files that I have.
> I know how to recover the DBs to the point of their full backup. I'm
> wondering if there's a way of recover them to the point of failure using
> their log files.
>
>
log,
no
> C:
the
log
>

Recovering using the log file

I have a situation where the server and data files were located on the C:
drive, and the log files on the F: drive.
The C: drive crashed, total loss. But I still have full backups for the
databases and also their log files.
Is there a way of recovering my DBs to the point of failure using the
backups and log files?
Everything I see in the BOL refer to use a log backup. I don't have log
backups, just the log files themselves.
ThanksIf the database is in full recovery mode, and no-one has truncated the log,
you can do:
BACKUP LOG dbname TO ... WITH NO_TRUNCATE
If the database is in simple recovery mode, then SQL Server will truncate
the log at each checkpoint. This means that the information you need is no
longer available in the transaction log files.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dallara" <someone@.microsoft.com> wrote in message
news:OPpd5co%23DHA.3232@.TK2MSFTNGP10.phx.gbl...
> I have a situation where the server and data files were located on the C:
> drive, and the log files on the F: drive.
> The C: drive crashed, total loss. But I still have full backups for the
> databases and also their log files.
> Is there a way of recovering my DBs to the point of failure using the
> backups and log files?
> Everything I see in the BOL refer to use a log backup. I don't have log
> backups, just the log files themselves.
> Thanks
>|||They were in full recovery.
Thanks for yr help, but the point you missed here is that the DBs are gone,
the server is gone as well.
So "BACKUP LOG dbname TO ... WITH NO_TRUNCATE" doesn't work as "dbname"
doesn't exist.
I've set up a new server, and I want to recreate the databases using the
full backups and log files that I have.
I know how to recover the DBs to the point of their full backup. I'm
wondering if there's a way of recover them to the point of failure using
their log files.
>"Tibor Karaszi" wrote in message
>If the database is in full recovery mode, and no-one has truncated the log,
>you can do:
>BACKUP LOG dbname TO ... WITH NO_TRUNCATE
>If the database is in simple recovery mode, then SQL Server will truncate
>the log at each checkpoint. This means that the information you need is no
>longer available in the transaction log files.
> Tibor Karaszi, SQL Server MVP
>
>> "Dallara" wrote in message
> > I have a situation where the server and data files were located on the
C:
> > drive, and the log files on the F: drive.
> > The C: drive crashed, total loss. But I still have full backups for the
> > databases and also their log files.
> >
> > Is there a way of recovering my DBs to the point of failure using the
> > backups and log files?
> > Everything I see in the BOL refer to use a log backup. I don't have log
> > backups, just the log files themselves.
> >
> > Thanks|||OK, so the SQL Server install in itself is toast...
The concept to save this is to have your new installation with a working SQL
Server, create a database with the same name and same file layout as the old
one. Stop that SQL Server, delete the mdf, ndf and ldf files. Then copy over
the ldf file from the broken install. Start SQL Server. Database is now
suspect (the mdf and ndf files are missing). You now do
BACKUP LOG dbname TO DISK = 'C:\...' WITH NO_TRUNCATE
Then restore the db backup and that log backups.
Above assumes that you indeed have all log records available in the ldf
file, since the db backup was taken.
There's a KB on the subject, I don't have the number, but it shouldn't be
hard to find with a little bit of searching...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dallara" <someone@.microsoft.com> wrote in message
news:OcVlVay%23DHA.1212@.TK2MSFTNGP12.phx.gbl...
> They were in full recovery.
> Thanks for yr help, but the point you missed here is that the DBs are
gone,
> the server is gone as well.
> So "BACKUP LOG dbname TO ... WITH NO_TRUNCATE" doesn't work as "dbname"
> doesn't exist.
> I've set up a new server, and I want to recreate the databases using the
> full backups and log files that I have.
> I know how to recover the DBs to the point of their full backup. I'm
> wondering if there's a way of recover them to the point of failure using
> their log files.
>
> >"Tibor Karaszi" wrote in message
> >
> >If the database is in full recovery mode, and no-one has truncated the
log,
> >you can do:
> >BACKUP LOG dbname TO ... WITH NO_TRUNCATE
> >
> >If the database is in simple recovery mode, then SQL Server will truncate
> >the log at each checkpoint. This means that the information you need is
no
> >longer available in the transaction log files.
> >
> > Tibor Karaszi, SQL Server MVP
> >
> >
> >> "Dallara" wrote in message
> >>
> > > I have a situation where the server and data files were located on the
> C:
> > > drive, and the log files on the F: drive.
> > > The C: drive crashed, total loss. But I still have full backups for
the
> > > databases and also their log files.
> > >
> > > Is there a way of recovering my DBs to the point of failure using the
> > > backups and log files?
> > > Everything I see in the BOL refer to use a log backup. I don't have
log
> > > backups, just the log files themselves.
> > >
> > > Thanks
>