I've recently taken over our comapnies SQL servers. I'm trying to go through
the way my predecessor had things done. I came across two questions
regarding the maintenance plans he had set up to schedule backups through the
enterprise manager.
1) Can you backup all databses in one plan both system and user provided
they are of similar type simple or full
2) When I scheduled the backups how far apart should the BAK and TRN backups
be apart.
They currently have weird scheduled times ex. 11:00 PM for BAK and TRN 1:00
Daily, 1:00 AM for BAK Monday through Saturday, TRN not backed up, BAK 2:00
AM Monday through Saturday, TRN 12:00 AM Monday through Saturday.
Also when these backups run do they interfere with accessing the Database.First, when in process, a backup will have a small but noticeable impact
upon server performance. (It is relative to CPU/Memory, etc.) Under normal
circumstances, user rarely notice.
Backup plans should be carefully considered based upon need: How much data
is new or changed, and how long can you afford to be offline in case of a
system failure and need to restore.
Look in SQL Server Books Online for the topic "Backing Up and Restoring
Databases'. That will get you started down the path of better understanding
how to balance need vs. capability.
System databases: Usually these are not subject to much daily change
activity, and don't require frequent backups. Master is critical and must be
backed up immediately after any changes to database schema, users,
permissions, etc. Never bother with backing up TempDb. MSDB needs to be
backed up when you create or change any scheduled jobs, add 'operators', or
'alerts'. (Maintenance Plans are scheduled 'jobs'.)
User databases: Again, judge the frequency of new and changed data, and
balance with the risk of loss. In some situations, such as databases used
for reporting, the data is relatively static and doesn't require regular
backups. Other needs include high frequency input with little room for
potential loss. A common schedule for high frequency online databases would
be weekly (or nightly) FULL backups, with hourly Transaction Log backups.
After you do some reading and gather your business requirements, you will be
better prepared to make a schedule that works best for your organization.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:A4C1032D-BE35-4F85-A863-EA2DDF856CF6@.microsoft.com...
> I've recently taken over our comapnies SQL servers. I'm trying to go
> through
> the way my predecessor had things done. I came across two questions
> regarding the maintenance plans he had set up to schedule backups through
> the
> enterprise manager.
> 1) Can you backup all databses in one plan both system and user provided
> they are of similar type simple or full
> 2) When I scheduled the backups how far apart should the BAK and TRN
> backups
> be apart.
> They currently have weird scheduled times ex. 11:00 PM for BAK and TRN
> 1:00
> Daily, 1:00 AM for BAK Monday through Saturday, TRN not backed up, BAK
> 2:00
> AM Monday through Saturday, TRN 12:00 AM Monday through Saturday.
> Also when these backups run do they interfere with accessing the Database.|||I think Arnie covered most, but just to add one little thing:
> 1) Can you backup all databses in one plan both system and user provided
> they are of similar type simple or full
You can definitely have the same plan for the db backup part. But not for the log backup part, since
you cannot do log backup of the master database. You can do log backup of msdb, but 2000 has this
weird behavior of setting msdb to simple each time Agent starts (which you can handle if you do wish
to log backup msdb).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:A4C1032D-BE35-4F85-A863-EA2DDF856CF6@.microsoft.com...
> I've recently taken over our comapnies SQL servers. I'm trying to go through
> the way my predecessor had things done. I came across two questions
> regarding the maintenance plans he had set up to schedule backups through the
> enterprise manager.
> 1) Can you backup all databses in one plan both system and user provided
> they are of similar type simple or full
> 2) When I scheduled the backups how far apart should the BAK and TRN backups
> be apart.
> They currently have weird scheduled times ex. 11:00 PM for BAK and TRN 1:00
> Daily, 1:00 AM for BAK Monday through Saturday, TRN not backed up, BAK 2:00
> AM Monday through Saturday, TRN 12:00 AM Monday through Saturday.
> Also when these backups run do they interfere with accessing the Database.|||> We've established its exceptable to just do One backup at a point in time
> lets say 3:00 Am every night. How soon after the BAK backup should I have the
> trn backup run?
I usually consider the frequency for log backups instead. Every 10 minutes? Every hour? This is of
course not my decision, it is the system owner's decision. Say you only want to do one log backup
each day (to enable point in time restore, for instance), then it doesn't matter much when you do
it...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:F9E2C858-B650-4588-B1BF-6D88D81D50D9@.microsoft.com...
> What I usually do is have a Plan for all the simple db and one for all the
> full backups.
> We've established its exceptable to just do One backup at a point in time
> lets say 3:00 Am every night. How soon after the BAK backup should I have the
> trn backup run?
> "Tibor Karaszi" wrote:
>> I think Arnie covered most, but just to add one little thing:
>> > 1) Can you backup all databses in one plan both system and user provided
>> > they are of similar type simple or full
>> You can definitely have the same plan for the db backup part. But not for the log backup part,
>> since
>> you cannot do log backup of the master database. You can do log backup of msdb, but 2000 has this
>> weird behavior of setting msdb to simple each time Agent starts (which you can handle if you do
>> wish
>> to log backup msdb).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "George Schneider" <georgedschneider@.news.postalias> wrote in message
>> news:A4C1032D-BE35-4F85-A863-EA2DDF856CF6@.microsoft.com...
>> > I've recently taken over our comapnies SQL servers. I'm trying to go through
>> > the way my predecessor had things done. I came across two questions
>> > regarding the maintenance plans he had set up to schedule backups through the
>> > enterprise manager.
>> >
>> > 1) Can you backup all databses in one plan both system and user provided
>> > they are of similar type simple or full
>> > 2) When I scheduled the backups how far apart should the BAK and TRN backups
>> > be apart.
>> >
>> > They currently have weird scheduled times ex. 11:00 PM for BAK and TRN 1:00
>> > Daily, 1:00 AM for BAK Monday through Saturday, TRN not backed up, BAK 2:00
>> > AM Monday through Saturday, TRN 12:00 AM Monday through Saturday.
>> >
>> > Also when these backups run do they interfere with accessing the Database.
>>|||George Schneider wrote:
> I guess I'm having a little trouble understanding the purpose of the
> transaction log backups. A typically backup schedule for our databse would
> be a full at 1:00 AM and the trans backup at 2:00 AM. Would exactly is the
> function of trans backups?
>
Think of the transaction log as a sort of journal. In that journal is a
record of every change that is made to your database. When you run a
full backup, you're saving a "snapshot" of the database as it exists at
that point in time, at 1:00am. For the next 24 hours, changes are being
made to your database. The transaction log is recording those changes.
If you suffer a drive failure, or some other catastrophic loss, you
likely are going to have to restore from the last full backup, losing
those 24 hours of changes.
If you're taking regular backups of the transaction log, you can restore
the last full backup, THEN restore, or "playback", the entries in your
transaction log, thus reproducing the changes that were made to your
database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||typically what I've done on other servers is have the db backup run at 3:00
Am and the tyransaction backup at 3:15 Am. Its exceptable for our purposes
to backup at one point in time. I use enterpsrise manager through a
maintenance plan to create a disk based backup and then backup the backup
directory to tape. If we had a failure all I would need to restore would be
the db backup and then the transaction log backup which would contain all the
changes since the full backup or is it since the last transaction backup?
Does this sound right?
"Tracy McKibben" wrote:
> George Schneider wrote:
> > I guess I'm having a little trouble understanding the purpose of the
> > transaction log backups. A typically backup schedule for our databse would
> > be a full at 1:00 AM and the trans backup at 2:00 AM. Would exactly is the
> > function of trans backups?
> >
> Think of the transaction log as a sort of journal. In that journal is a
> record of every change that is made to your database. When you run a
> full backup, you're saving a "snapshot" of the database as it exists at
> that point in time, at 1:00am. For the next 24 hours, changes are being
> made to your database. The transaction log is recording those changes.
> If you suffer a drive failure, or some other catastrophic loss, you
> likely are going to have to restore from the last full backup, losing
> those 24 hours of changes.
> If you're taking regular backups of the transaction log, you can restore
> the last full backup, THEN restore, or "playback", the entries in your
> transaction log, thus reproducing the changes that were made to your
> database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||What if something disastrous happens at 02:50, in a way so you cannot perform a transaction log
backup at that time. You have now lost one day worth of information. This is what you have to ask
yourself: Is this acceptable?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:65BEB0C9-1499-4243-AAB0-580DFEAD3C9E@.microsoft.com...
> typically what I've done on other servers is have the db backup run at 3:00
> Am and the tyransaction backup at 3:15 Am. Its exceptable for our purposes
> to backup at one point in time. I use enterpsrise manager through a
> maintenance plan to create a disk based backup and then backup the backup
> directory to tape. If we had a failure all I would need to restore would be
> the db backup and then the transaction log backup which would contain all the
> changes since the full backup or is it since the last transaction backup?
> Does this sound right?
> "Tracy McKibben" wrote:
>> George Schneider wrote:
>> > I guess I'm having a little trouble understanding the purpose of the
>> > transaction log backups. A typically backup schedule for our databse would
>> > be a full at 1:00 AM and the trans backup at 2:00 AM. Would exactly is the
>> > function of trans backups?
>> >
>> Think of the transaction log as a sort of journal. In that journal is a
>> record of every change that is made to your database. When you run a
>> full backup, you're saving a "snapshot" of the database as it exists at
>> that point in time, at 1:00am. For the next 24 hours, changes are being
>> made to your database. The transaction log is recording those changes.
>> If you suffer a drive failure, or some other catastrophic loss, you
>> likely are going to have to restore from the last full backup, losing
>> those 24 hours of changes.
>> If you're taking regular backups of the transaction log, you can restore
>> the last full backup, THEN restore, or "playback", the entries in your
>> transaction log, thus reproducing the changes that were made to your
>> database.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:65BEB0C9-1499-4243-AAB0-580DFEAD3C9E@.microsoft.com...
> typically what I've done on other servers is have the db backup run at
> 3:00
> Am and the tyransaction backup at 3:15 Am. Its exceptable for our
> purposes
> to backup at one point in time. I use enterpsrise manager through a
> maintenance plan to create a disk based backup and then backup the backup
> directory to tape. If we had a failure all I would need to restore would
> be
> the db backup and then the transaction log backup which would contain all
> the
> changes since the full backup or is it since the last transaction backup?
> Does this sound right?
>
Not exactly. You really want to be doing transaction backups throughout the
day.
i.e. schedule transaction backups to run say every 15 minutes throughout the
day.
If you completely lose your server, you can restore the DB to the state it
was 15 minutes previously by restoring the full backup (with NORECOVERY
explicitely used) and then each subsequent transaction backup (also with
NORECOVERY).
At the very end you can either restore with recovery and have a functional
database, or if possibly apply the "tail of the log" from the now dead
(assuming you could at least get the log) server.
In this way it's possible in some cases to recover a database to the moment
of failure.
