Questions on simple recovery model

Hello. What is the rational behind not allowing a transaction log to be
backed up on a DB that is using the simple recovery model and how does
xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
mode?
As we know if left unchecked logs can become huge even in simple recovery
mode. They still need backups. For that matter if they're not deemed reliable
for point in time recovery why do they even exist? We run a reporting system
with most activity at night. We're happy to use differential backups to get
us back to yesterday. Why can't the simple model not use logs at all and not
make us deal with their growth spurts? TIA
Ken Trock
1) There is no benefit to backing up a tlog that is in simple mode because
nothing useful remains there.
2) Not sure what you are asking about xp_sqlmaint and single-user mode.
3) I wasn't aware that logs grow unchecked if not backed up if the database
is in simple recovery mode. Checkpoints flush committed transactions from
the tlog in that scenario, freeing space to be reused by new transactions.
4) Tlogs exist even in simple recovery mode because sql server is a
write-to-tlog-first engine and this cannot be disabled. No database
modification (insert, update, delete) can occur unless it is first written
to log.
5) I would like to see tlogging be able to be disabled, but not for the log
growth issue. Rather it would provide a fairly significant performance
increase.
TheSQLGuru
President
Indicium Resources, Inc.
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>
|||The transaction log exists for two reasons. It's primary purpose is to act
as a transaction integrity mechanism. The ltransaction log acts in a
write-ahead fashion. That is, the transaciton officially begins when the
first log entry is written. The relevant data is modified (either in memory
or in the data file) and then the transaction is committed by writing a
second log entry. Finally, a checkpoint process notes when committed
transactions are written to the data file. This allows for transactionally
consistent recovery even on an instantaneous failure of any type. SiMPLE
recovery alows the system to auto-truncate logs once a segment is
checkpointed. Without the separate log file, there would be no way to
guarantee transactional consistency on a hardware or system failure.
The other purpose is to allow for restore (different from recovery!) up to a
specific point in time or to a named transaction. This requires FULL
recovery so that log truncation only occurs after a segment is both
checkpointed and backed up.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>
|||Thanks for the quick response SQLGuru . On 3), we've indeed had our logs grow
large. Not sure where these checkpoints have been. Also several others here
have posted the same sizing issue. So we (try to) back them up only to keep
their size manageable (maybe that executes a checkpoint). In 2000 if you do
it thru DB maintenance plans in SEM and schedule it you'll notice that the
resultant job calls master.xp_sqlmaint extended proc which is a .dll. I
haven't been able to script a successful tlog backup under simple recovery
model.
Ken
"TheSQLGuru" wrote:

> 1) There is no benefit to backing up a tlog that is in simple mode because
> nothing useful remains there.
> 2) Not sure what you are asking about xp_sqlmaint and single-user mode.
> 3) I wasn't aware that logs grow unchecked if not backed up if the database
> is in simple recovery mode. Checkpoints flush committed transactions from
> the tlog in that scenario, freeing space to be reused by new transactions.
> 4) Tlogs exist even in simple recovery mode because sql server is a
> write-to-tlog-first engine and this cannot be disabled. No database
> modification (insert, update, delete) can occur unless it is first written
> to log.
> 5) I would like to see tlogging be able to be disabled, but not for the log
> growth issue. Rather it would provide a fairly significant performance
> increase.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
>
>
|||On Wed, 15 Aug 2007 09:34:01 -0700, ktrock
<ktrock@.discussions.microsoft.com> wrote:

>As we know if left unchecked logs can become huge even in simple recovery
>mode.
The log has to be at least large enough for an entire transaction. To
be more precise, it has to hold everything from the start of the
oldest transaction up to the present moment. So it really has to be
large enough for the longest running transaction plus all the other
transactions happening at the same time. If you run really big
transactions you will need a really big log, even in Simple recovery
mode.
In Simple recovery mode you can not back up the log, SQL Server will
not accept the command.
Roy Harvey
Beacon Falls, CT

Questions on simple recovery model

Hello. What is the rational behind not allowing a transaction log to be
backed up on a DB that is using the simple recovery model and how does
xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
mode?
As we know if left unchecked logs can become huge even in simple recovery
mode. They still need backups. For that matter if they're not deemed reliabl
e
for point in time recovery why do they even exist? We run a reporting system
with most activity at night. We're happy to use differential backups to get
us back to yesterday. Why can't the simple model not use logs at all and not
make us deal with their growth spurts? TIA
Ken Trock1) There is no benefit to backing up a tlog that is in simple mode because
nothing useful remains there.
2) Not sure what you are asking about xp_sqlmaint and single-user mode.
3) I wasn't aware that logs grow unchecked if not backed up if the database
is in simple recovery mode. Checkpoints flush committed transactions from
the tlog in that scenario, freeing space to be reused by new transactions.
4) Tlogs exist even in simple recovery mode because sql server is a
write-to-tlog-first engine and this cannot be disabled. No database
modification (insert, update, delete) can occur unless it is first written
to log.
5) I would like to see tlogging be able to be disabled, but not for the log
growth issue. Rather it would provide a fairly significant performance
increase.
TheSQLGuru
President
Indicium Resources, Inc.
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>|||The transaction log exists for two reasons. It's primary purpose is to act
as a transaction integrity mechanism. The ltransaction log acts in a
write-ahead fashion. That is, the transaciton officially begins when the
first log entry is written. The relevant data is modified (either in memory
or in the data file) and then the transaction is committed by writing a
second log entry. Finally, a checkpoint process notes when committed
transactions are written to the data file. This allows for transactionally
consistent recovery even on an instantaneous failure of any type. SiMPLE
recovery alows the system to auto-truncate logs once a segment is
checkpointed. Without the separate log file, there would be no way to
guarantee transactional consistency on a hardware or system failure.
The other purpose is to allow for restore (different from recovery!) up to a
specific point in time or to a named transaction. This requires FULL
recovery so that log truncation only occurs after a segment is both
checkpointed and backed up.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>|||Thanks for the quick response SQLGuru . On 3), we've indeed had our logs gro
w
large. Not sure where these checkpoints have been. Also several others here
have posted the same sizing issue. So we (try to) back them up only to keep
their size manageable (maybe that executes a checkpoint). In 2000 if you do
it thru DB maintenance plans in SEM and schedule it you'll notice that the
resultant job calls master.xp_sqlmaint extended proc which is a .dll. I
haven't been able to script a successful tlog backup under simple recovery
model.
Ken
"TheSQLGuru" wrote:

> 1) There is no benefit to backing up a tlog that is in simple mode because
> nothing useful remains there.
> 2) Not sure what you are asking about xp_sqlmaint and single-user mode.
> 3) I wasn't aware that logs grow unchecked if not backed up if the databas
e
> is in simple recovery mode. Checkpoints flush committed transactions from
> the tlog in that scenario, freeing space to be reused by new transactions.
> 4) Tlogs exist even in simple recovery mode because sql server is a
> write-to-tlog-first engine and this cannot be disabled. No database
> modification (insert, update, delete) can occur unless it is first written
> to log.
> 5) I would like to see tlogging be able to be disabled, but not for the lo
g
> growth issue. Rather it would provide a fairly significant performance
> increase.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
>
>|||On Wed, 15 Aug 2007 09:34:01 -0700, ktrock
<ktrock@.discussions.microsoft.com> wrote:

>As we know if left unchecked logs can become huge even in simple recovery
>mode.
The log has to be at least large enough for an entire transaction. To
be more precise, it has to hold everything from the start of the
oldest transaction up to the present moment. So it really has to be
large enough for the longest running transaction plus all the other
transactions happening at the same time. If you run really big
transactions you will need a really big log, even in Simple recovery
mode.
In Simple recovery mode you can not back up the log, SQL Server will
not accept the command.
Roy Harvey
Beacon Falls, CT

Questions on simple recovery model

Hello. What is the rational behind not allowing a transaction log to be
backed up on a DB that is using the simple recovery model and how does
xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
mode?
As we know if left unchecked logs can become huge even in simple recovery
mode. They still need backups. For that matter if they're not deemed reliable
for point in time recovery why do they even exist? We run a reporting system
with most activity at night. We're happy to use differential backups to get
us back to yesterday. Why can't the simple model not use logs at all and not
make us deal with their growth spurts? TIA
Ken Trock1) There is no benefit to backing up a tlog that is in simple mode because
nothing useful remains there.
2) Not sure what you are asking about xp_sqlmaint and single-user mode.
3) I wasn't aware that logs grow unchecked if not backed up if the database
is in simple recovery mode. Checkpoints flush committed transactions from
the tlog in that scenario, freeing space to be reused by new transactions.
4) Tlogs exist even in simple recovery mode because sql server is a
write-to-tlog-first engine and this cannot be disabled. No database
modification (insert, update, delete) can occur unless it is first written
to log.
5) I would like to see tlogging be able to be disabled, but not for the log
growth issue. Rather it would provide a fairly significant performance
increase.
TheSQLGuru
President
Indicium Resources, Inc.
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>|||Thanks for the quick response SQLGuru . On 3), we've indeed had our logs grow
large. Not sure where these checkpoints have been. Also several others here
have posted the same sizing issue. So we (try to) back them up only to keep
their size manageable (maybe that executes a checkpoint). In 2000 if you do
it thru DB maintenance plans in SEM and schedule it you'll notice that the
resultant job calls master.xp_sqlmaint extended proc which is a .dll. I
haven't been able to script a successful tlog backup under simple recovery
model.
Ken
"TheSQLGuru" wrote:
> 1) There is no benefit to backing up a tlog that is in simple mode because
> nothing useful remains there.
> 2) Not sure what you are asking about xp_sqlmaint and single-user mode.
> 3) I wasn't aware that logs grow unchecked if not backed up if the database
> is in simple recovery mode. Checkpoints flush committed transactions from
> the tlog in that scenario, freeing space to be reused by new transactions.
> 4) Tlogs exist even in simple recovery mode because sql server is a
> write-to-tlog-first engine and this cannot be disabled. No database
> modification (insert, update, delete) can occur unless it is first written
> to log.
> 5) I would like to see tlogging be able to be disabled, but not for the log
> growth issue. Rather it would provide a fairly significant performance
> increase.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> > Hello. What is the rational behind not allowing a transaction log to be
> > backed up on a DB that is using the simple recovery model and how does
> > xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> > mode?
> >
> > As we know if left unchecked logs can become huge even in simple recovery
> > mode. They still need backups. For that matter if they're not deemed
> > reliable
> > for point in time recovery why do they even exist? We run a reporting
> > system
> > with most activity at night. We're happy to use differential backups to
> > get
> > us back to yesterday. Why can't the simple model not use logs at all and
> > not
> > make us deal with their growth spurts? TIA
> >
> > Ken Trock
> >
> >
>
>|||The transaction log exists for two reasons. It's primary purpose is to act
as a transaction integrity mechanism. The ltransaction log acts in a
write-ahead fashion. That is, the transaciton officially begins when the
first log entry is written. The relevant data is modified (either in memory
or in the data file) and then the transaction is committed by writing a
second log entry. Finally, a checkpoint process notes when committed
transactions are written to the data file. This allows for transactionally
consistent recovery even on an instantaneous failure of any type. SiMPLE
recovery alows the system to auto-truncate logs once a segment is
checkpointed. Without the separate log file, there would be no way to
guarantee transactional consistency on a hardware or system failure.
The other purpose is to allow for restore (different from recovery!) up to a
specific point in time or to a named transaction. This requires FULL
recovery so that log truncation only occurs after a segment is both
checkpointed and backed up.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:D022013E-B95F-4681-8B12-26D2322F36CF@.microsoft.com...
> Hello. What is the rational behind not allowing a transaction log to be
> backed up on a DB that is using the simple recovery model and how does
> xp_sqlmaint do it for you? Does it temporarily put the DB in single-user
> mode?
> As we know if left unchecked logs can become huge even in simple recovery
> mode. They still need backups. For that matter if they're not deemed
> reliable
> for point in time recovery why do they even exist? We run a reporting
> system
> with most activity at night. We're happy to use differential backups to
> get
> us back to yesterday. Why can't the simple model not use logs at all and
> not
> make us deal with their growth spurts? TIA
> Ken Trock
>|||On Wed, 15 Aug 2007 09:34:01 -0700, ktrock
<ktrock@.discussions.microsoft.com> wrote:
>As we know if left unchecked logs can become huge even in simple recovery
>mode.
The log has to be at least large enough for an entire transaction. To
be more precise, it has to hold everything from the start of the
oldest transaction up to the present moment. So it really has to be
large enough for the longest running transaction plus all the other
transactions happening at the same time. If you run really big
transactions you will need a really big log, even in Simple recovery
mode.
In Simple recovery mode you can not back up the log, SQL Server will
not accept the command.
Roy Harvey
Beacon Falls, CT

questions on recovery (zero day for me, wrapping up recovery)

I lost a whole server (was windows 2000 sp4 with sql server 2000 sp3).
Restored from backup on new server hardware (new idently as far as domain
goes also, did not bring back old server's identity).
Luckily I had a full backup from just the day before. I setup new server
(w2k3 r2 sp2) and installed sql server 2000 sp3. I was using simple recovery
model with just 1 full backup every day (fine for our needs) and so it was a
simple full restore from backup device (file). Completed, no problems. All
user data appears to be there and custom client application seems to be
working fine so far with it today (after updating ODBC config to reflect new
server and refreshing the linked tables in the Access front end mdb file)
1) so from here I'm not sure if I should restore my backups of the system
databases (master, msdb)? In my situation, the front end app just uses one
sql user account (via odbc connection), so recreating and assigning the one
user permissions was no problem (thats what I did). I didn't have any custom
system or user error messages, and wasn't using any linked servers. Users
are already using the system for 1/2 day now without any issues. Also, as
far as msdb, I don't mind creating new backup devices and a new maintenance
plan for this restored database. My backups of these system databases are
about 1 year old. I'm afraid I may cause a problem by restoring these... '
any input on this would be GREATLY appreciated. Just leave it be? is there
something I am overlooking on the need to restore these system databases in
*my* scenario?
2) REPLICATION: also, along the same lines, I'm not sure if I should restore
my distribution database for the merge replication that was on the old
server, or just recreate instead? Again, this backup is about 1 year old.
I'm wondering if it would be simpler/better for me to just recreate from
scratch? In fact it just occurred to me that I need to change to
transactional replication anyway (if I got the term right, its been a while
since I setup replication) because the requirement changed from 2 way
synching to just 1-way recently anyway. I have all the most recent user data
on my end to setup new. Will I have issues with this due to traces of the
old replication in my database? do I need to clean up first? or just create
new?
all in all, as far as I can tell to this point, we have lost zero user data,
which is all my DR plan really set out to do. I planned on having a day of
downtime to rebuild a server if this happened, and as luck would have it, it
happened on a sunday so the company experienced no down time. So what I'm
hoping is that what I've done is fine, and that I don't need to restore the
system databases or distribustion databases, and that instead, I just need
to re-do some configs. yes/no?
sorry so long.David,
You system setup seems very simple. Is this is infact true, by creating the
user account manually and recreating backup/maintenance jobs, you no longer
need to worry about restoring master or MSDB. For future reference, I would
include these DBs in my backup plan though.
"David" <nospam@.nospam.com> wrote in message
news:%23vaaqqNFIHA.1324@.TK2MSFTNGP06.phx.gbl...
>I lost a whole server (was windows 2000 sp4 with sql server 2000 sp3).
>Restored from backup on new server hardware (new idently as far as domain
>goes also, did not bring back old server's identity).
> Luckily I had a full backup from just the day before. I setup new server
> (w2k3 r2 sp2) and installed sql server 2000 sp3. I was using simple
> recovery model with just 1 full backup every day (fine for our needs) and
> so it was a simple full restore from backup device (file). Completed, no
> problems. All user data appears to be there and custom client application
> seems to be working fine so far with it today (after updating ODBC config
> to reflect new server and refreshing the linked tables in the Access front
> end mdb file)
> 1) so from here I'm not sure if I should restore my backups of the system
> databases (master, msdb)? In my situation, the front end app just uses one
> sql user account (via odbc connection), so recreating and assigning the
> one user permissions was no problem (thats what I did). I didn't have any
> custom system or user error messages, and wasn't using any linked servers.
> Users are already using the system for 1/2 day now without any issues.
> Also, as far as msdb, I don't mind creating new backup devices and a new
> maintenance plan for this restored database. My backups of these system
> databases are about 1 year old. I'm afraid I may cause a problem by
> restoring these... ' any input on this would be GREATLY appreciated. Just
> leave it be? is there something I am overlooking on the need to restore
> these system databases in *my* scenario?
>
> 2) REPLICATION: also, along the same lines, I'm not sure if I should
> restore my distribution database for the merge replication that was on the
> old server, or just recreate instead? Again, this backup is about 1 year
> old. I'm wondering if it would be simpler/better for me to just recreate
> from scratch? In fact it just occurred to me that I need to change to
> transactional replication anyway (if I got the term right, its been a
> while since I setup replication) because the requirement changed from 2
> way synching to just 1-way recently anyway. I have all the most recent
> user data on my end to setup new. Will I have issues with this due to
> traces of the old replication in my database? do I need to clean up first?
> or just create new?
> all in all, as far as I can tell to this point, we have lost zero user
> data, which is all my DR plan really set out to do. I planned on having a
> day of downtime to rebuild a server if this happened, and as luck would
> have it, it happened on a sunday so the company experienced no down time.
> So what I'm hoping is that what I've done is fine, and that I don't need
> to restore the system databases or distribustion databases, and that
> instead, I just need to re-do some configs. yes/no?
> sorry so long.
>|||I agree with Dragon: you seem to be fine.
You might want to study the system tables in the system databases on a 2000 install as well as Books
Online. They can give you some extra hints on what information are stored in system databases so you
can pick it up in case you forgot about it (like sp_configure setting as an example).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David" <nospam@.nospam.com> wrote in message news:%23vaaqqNFIHA.1324@.TK2MSFTNGP06.phx.gbl...
>I lost a whole server (was windows 2000 sp4 with sql server 2000 sp3). Restored from backup on new
>server hardware (new idently as far as domain goes also, did not bring back old server's identity).
> Luckily I had a full backup from just the day before. I setup new server (w2k3 r2 sp2) and
> installed sql server 2000 sp3. I was using simple recovery model with just 1 full backup every day
> (fine for our needs) and so it was a simple full restore from backup device (file). Completed, no
> problems. All user data appears to be there and custom client application seems to be working fine
> so far with it today (after updating ODBC config to reflect new server and refreshing the linked
> tables in the Access front end mdb file)
> 1) so from here I'm not sure if I should restore my backups of the system databases (master,
> msdb)? In my situation, the front end app just uses one sql user account (via odbc connection), so
> recreating and assigning the one user permissions was no problem (thats what I did). I didn't have
> any custom system or user error messages, and wasn't using any linked servers. Users are already
> using the system for 1/2 day now without any issues. Also, as far as msdb, I don't mind creating
> new backup devices and a new maintenance plan for this restored database. My backups of these
> system databases are about 1 year old. I'm afraid I may cause a problem by restoring these... '
> any input on this would be GREATLY appreciated. Just leave it be? is there something I am
> overlooking on the need to restore these system databases in *my* scenario?
>
> 2) REPLICATION: also, along the same lines, I'm not sure if I should restore my distribution
> database for the merge replication that was on the old server, or just recreate instead? Again,
> this backup is about 1 year old. I'm wondering if it would be simpler/better for me to just
> recreate from scratch? In fact it just occurred to me that I need to change to transactional
> replication anyway (if I got the term right, its been a while since I setup replication) because
> the requirement changed from 2 way synching to just 1-way recently anyway. I have all the most
> recent user data on my end to setup new. Will I have issues with this due to traces of the old
> replication in my database? do I need to clean up first? or just create new?
> all in all, as far as I can tell to this point, we have lost zero user data, which is all my DR
> plan really set out to do. I planned on having a day of downtime to rebuild a server if this
> happened, and as luck would have it, it happened on a sunday so the company experienced no down
> time. So what I'm hoping is that what I've done is fine, and that I don't need to restore the
> system databases or distribustion databases, and that instead, I just need to re-do some configs.
> yes/no?
> sorry so long.
>|||thanks. I appreciate it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0231D4CD-9B7A-47B1-BF27-131FB29400C0@.microsoft.com...
>I agree with Dragon: you seem to be fine.
> You might want to study the system tables in the system databases on a
> 2000 install as well as Books Online. They can give you some extra hints
> on what information are stored in system databases so you can pick it up
> in case you forgot about it (like sp_configure setting as an example).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "David" <nospam@.nospam.com> wrote in message
> news:%23vaaqqNFIHA.1324@.TK2MSFTNGP06.phx.gbl...
>>I lost a whole server (was windows 2000 sp4 with sql server 2000 sp3).
>>Restored from backup on new server hardware (new idently as far as domain
>>goes also, did not bring back old server's identity).
>> Luckily I had a full backup from just the day before. I setup new server
>> (w2k3 r2 sp2) and installed sql server 2000 sp3. I was using simple
>> recovery model with just 1 full backup every day (fine for our needs) and
>> so it was a simple full restore from backup device (file). Completed, no
>> problems. All user data appears to be there and custom client application
>> seems to be working fine so far with it today (after updating ODBC config
>> to reflect new server and refreshing the linked tables in the Access
>> front end mdb file)
>> 1) so from here I'm not sure if I should restore my backups of the system
>> databases (master, msdb)? In my situation, the front end app just uses
>> one sql user account (via odbc connection), so recreating and assigning
>> the one user permissions was no problem (thats what I did). I didn't have
>> any custom system or user error messages, and wasn't using any linked
>> servers. Users are already using the system for 1/2 day now without any
>> issues. Also, as far as msdb, I don't mind creating new backup devices
>> and a new maintenance plan for this restored database. My backups of
>> these system databases are about 1 year old. I'm afraid I may cause a
>> problem by restoring these... ' any input on this would be GREATLY
>> appreciated. Just leave it be? is there something I am overlooking on the
>> need to restore these system databases in *my* scenario?
>>
>> 2) REPLICATION: also, along the same lines, I'm not sure if I should
>> restore my distribution database for the merge replication that was on
>> the old server, or just recreate instead? Again, this backup is about 1
>> year old. I'm wondering if it would be simpler/better for me to just
>> recreate from scratch? In fact it just occurred to me that I need to
>> change to transactional replication anyway (if I got the term right, its
>> been a while since I setup replication) because the requirement changed
>> from 2 way synching to just 1-way recently anyway. I have all the most
>> recent user data on my end to setup new. Will I have issues with this due
>> to traces of the old replication in my database? do I need to clean up
>> first? or just create new?
>> all in all, as far as I can tell to this point, we have lost zero user
>> data, which is all my DR plan really set out to do. I planned on having a
>> day of downtime to rebuild a server if this happened, and as luck would
>> have it, it happened on a sunday so the company experienced no down time.
>> So what I'm hoping is that what I've done is fine, and that I don't need
>> to restore the system databases or distribustion databases, and that
>> instead, I just need to re-do some configs. yes/no?
>> sorry so long.
>|||thanks. I appreciate it.
"Dragon" <noSpam_baadil@.hotmail.com> wrote in message
news:On$CWTOFIHA.4880@.TK2MSFTNGP03.phx.gbl...
> David,
> You system setup seems very simple. Is this is infact true, by creating
> the user account manually and recreating backup/maintenance jobs, you no
> longer need to worry about restoring master or MSDB. For future reference,
> I would include these DBs in my backup plan though.
>
> "David" <nospam@.nospam.com> wrote in message
> news:%23vaaqqNFIHA.1324@.TK2MSFTNGP06.phx.gbl...
>>I lost a whole server (was windows 2000 sp4 with sql server 2000 sp3).
>>Restored from backup on new server hardware (new idently as far as domain
>>goes also, did not bring back old server's identity).
>> Luckily I had a full backup from just the day before. I setup new server
>> (w2k3 r2 sp2) and installed sql server 2000 sp3. I was using simple
>> recovery model with just 1 full backup every day (fine for our needs) and
>> so it was a simple full restore from backup device (file). Completed, no
>> problems. All user data appears to be there and custom client application
>> seems to be working fine so far with it today (after updating ODBC config
>> to reflect new server and refreshing the linked tables in the Access
>> front end mdb file)
>> 1) so from here I'm not sure if I should restore my backups of the system
>> databases (master, msdb)? In my situation, the front end app just uses
>> one sql user account (via odbc connection), so recreating and assigning
>> the one user permissions was no problem (thats what I did). I didn't have
>> any custom system or user error messages, and wasn't using any linked
>> servers. Users are already using the system for 1/2 day now without any
>> issues. Also, as far as msdb, I don't mind creating new backup devices
>> and a new maintenance plan for this restored database. My backups of
>> these system databases are about 1 year old. I'm afraid I may cause a
>> problem by restoring these... ' any input on this would be GREATLY
>> appreciated. Just leave it be? is there something I am overlooking on the
>> need to restore these system databases in *my* scenario?
>>
>> 2) REPLICATION: also, along the same lines, I'm not sure if I should
>> restore my distribution database for the merge replication that was on
>> the old server, or just recreate instead? Again, this backup is about 1
>> year old. I'm wondering if it would be simpler/better for me to just
>> recreate from scratch? In fact it just occurred to me that I need to
>> change to transactional replication anyway (if I got the term right, its
>> been a while since I setup replication) because the requirement changed
>> from 2 way synching to just 1-way recently anyway. I have all the most
>> recent user data on my end to setup new. Will I have issues with this due
>> to traces of the old replication in my database? do I need to clean up
>> first? or just create new?
>> all in all, as far as I can tell to this point, we have lost zero user
>> data, which is all my DR plan really set out to do. I planned on having a
>> day of downtime to rebuild a server if this happened, and as luck would
>> have it, it happened on a sunday so the company experienced no down time.
>> So what I'm hoping is that what I've done is fine, and that I don't need
>> to restore the system databases or distribustion databases, and that
>> instead, I just need to re-do some configs. yes/no?
>> sorry so long.
>