Backup Strategy Clarifications

Hello,
I just wanted to check if my backup strategy is on the right-track or not,
so that I can improve on it. I am using SQL 2000 SP4.
I have the following backup strategy in place: -
STEP 1: MODEL
==========
Database: model
Recovery Model: Full
Log Backup Frequency: Once a day at 20:00
Log Backup Mode: Overwritten
Database Backup Frequency: Once a day at 20:05
Database Backup Mode: Overwritten
STEP 2: MSDB
==========
Database: msdb
Recovery Model: Simple
Log Backup Frequency: N/A
Log Backup Mode: N/A
Database Backup Frequency: Once a day at 20:05 (same time as model)
Database Backup Mode: Overwritten
STEP 3: MASTER
===========
Database: msdb
Recovery Model: Full
Log Backup Frequency: Once a day at 20:08
Log Backup Mode: Overwritten
Database Backup Frequency: Once a day at 20:10
Database Backup Mode: Overwritten
STEP 4: TEMPDB
===========
Database: tempdb
Recovery Model: Simple
I am not doing any backups on this database!
STEP 5: LIVE
===========
Database: live
Recovery Model: Full
I am doing 2 types of Log backups on all User databases:
---
Type 1. Log Backup Frequency: Every 20 mins starting from 10 mins on the
hour (eg. 9:10 AM, 9:30 AM, 9:50 AM)
Type 1. Log Backup Mode: APPENDED (Not Overwritten!)
Type 2. Log Backup Frequency: Every Hour sharp on the hour (eg. 9:00 AM,
10:00 AM, 11:00 AM)
Type 2. Log Backup Mode: Overwritten
---
Database Backup Frequency: Every Hour but 5 mins on the hour (eg. 9:05 AM,
10:05 AM, 11:05 AM)
Database Backup Mode: Overwritten
My reasoning was that I do frequent log backups (appended) every 20 mins
and do 1 log backup (overwritten) when it reaches the exact hour. Then
after 5 mins past the hour, do a Full Database backup (overwritten).
Please mention STEP 1, STEP 2, etc. to refer to the proper item and provide
your suggestions.
Thanks,
Sameer.
Message posted via http://www.droptable.comHi,
Your strategy is good enough, but instead of 2 types of transaction log
backups I recommend you to perform only one type of transaction log
backup, but reduce the frequency of trqansaction log backup to 10 Minutes.
Since you are performing the Transaction log backup it is not
required to a full database backup every hour. Full database backup once a
day is more thana enough:-
Steps:-
1. System Database backup strategy is good enough
2. For Live db
A. Schedule a full database backup at 02 AM
B. Schedule the Transaction log backup from 02:15 AM to 01:50 AM every 10
Minutes.
Best way is use the database Maintenence plan to create the jobs for this.
This will create unique transaction log backup files. You could also
enable house keeping mechanism.
Thanks
Hari
SQL SERVER MVP
"Sameer Premji via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:d0f492b8d6dd48f497b96708d471369d@.SQ
droptable.com...
> Hello,
> I just wanted to check if my backup strategy is on the right-track or not,
> so that I can improve on it. I am using SQL 2000 SP4.
> I have the following backup strategy in place: -
>
> STEP 1: MODEL
> ==========
> Database: model
> Recovery Model: Full
> Log Backup Frequency: Once a day at 20:00
> Log Backup Mode: Overwritten
> Database Backup Frequency: Once a day at 20:05
> Database Backup Mode: Overwritten
>
> STEP 2: MSDB
> ==========
> Database: msdb
> Recovery Model: Simple
> Log Backup Frequency: N/A
> Log Backup Mode: N/A
> Database Backup Frequency: Once a day at 20:05 (same time as model)
> Database Backup Mode: Overwritten
>
> STEP 3: MASTER
> ===========
> Database: msdb
> Recovery Model: Full
> Log Backup Frequency: Once a day at 20:08
> Log Backup Mode: Overwritten
> Database Backup Frequency: Once a day at 20:10
> Database Backup Mode: Overwritten
>
> STEP 4: TEMPDB
> ===========
> Database: tempdb
> Recovery Model: Simple
> I am not doing any backups on this database!
>
> STEP 5: LIVE
> ===========
> Database: live
> Recovery Model: Full
> I am doing 2 types of Log backups on all User databases:
> ---
> Type 1. Log Backup Frequency: Every 20 mins starting from 10 mins on the
> hour (eg. 9:10 AM, 9:30 AM, 9:50 AM)
> Type 1. Log Backup Mode: APPENDED (Not Overwritten!)
>
> Type 2. Log Backup Frequency: Every Hour sharp on the hour (eg. 9:00 AM,
> 10:00 AM, 11:00 AM)
> Type 2. Log Backup Mode: Overwritten
> ---
> Database Backup Frequency: Every Hour but 5 mins on the hour (eg. 9:05 AM,
> 10:05 AM, 11:05 AM)
> Database Backup Mode: Overwritten
>
> My reasoning was that I do frequent log backups (appended) every 20 mins
> and do 1 log backup (overwritten) when it reaches the exact hour. Then
> after 5 mins past the hour, do a Full Database backup (overwritten).
>
> Please mention STEP 1, STEP 2, etc. to refer to the proper item and
> provide
> your suggestions.
> Thanks,
> Sameer.
> --
> Message posted via http://www.droptable.com|||Thanks Hari but I have more questions: -
1. What is the best practice ? - Perform Log backup first and then Database
backup or vice-versa?
2. For System databases, you mentioned it was good enough. So the
sequence/order of backing model first, then msdb, then master is OK or
should I change the order ?
3. For User databases, you mentioned that I should do a Log backup every 10
mins and Database backup once a day. What kind of backup mode should I use
for Log backup - Overwritten or Appended ?
4. By Performing Log backups every 10 mins, will it increase the size of
the Tran Log file ?
- If yes, how can I control the size automatically without performing a
shrink on the database (I read bad things about shrinking as it defragments
pages & disk)?
Thanks,
Sameer.
Message posted via http://www.droptable.com|||> 2. For System databases, you mentioned it was good enough. So the
> sequence/order of backing model first, then msdb, then master is OK or
> should I change the order ?
I do master, model, userdb1, userdb2, ... userdbN, msdb. This is because bac
kup history is in msdb
and I want to have backup of latest backup history. I also do log backup for
msdb, but since Agent
sets msdb to simple recovery, I have a job that Agent autostart to set it to
full. Some think that
this is overkill, though.

> 3. For User databases, you mentioned that I should do a Log backup every 1
0
> mins and Database backup once a day.
Be aware that you can potentially have a situation where you need to restore
24*6 = 144 log backups.
I.e., latest db backup and all subsequent log backups. This might be fine wi
th you, but you should
be aware of it. One thing to consider is to do a differential backup perhaps
every 2 hours.

> What kind of backup mode should I use
> for Log backup - Overwritten or Appended ?
What is important is that you need all log backups since latest database bac
kup in order to do
restore. And, you also want some generations of backups (a few days back or
so). Etc. Based on this,
you can then plan how you do your backups, new devices all the time, or re-u
se same device (with
append), etc.

> 4. By Performing Log backups every 10 mins, will it increase the size of
> the Tran Log file ?
Compared to what? Every 1 minute? Yes. Every hour? No. The log is emptied ev
erytime you backup the
log.
I suggest you spend an hour or two reading the sections in Books Online that
deals with backup and
restore. Just to you feel confident that you understand the backup and resto
re architecture in SQL
Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via droptable.com" <forum@.droptable.com> wrote in message
news:1a93f786c3c5435eafa4d96f81eb5393@.SQ
droptable.com...
> Thanks Hari but I have more questions: -
> 1. What is the best practice ? - Perform Log backup first and then Databas
e
> backup or vice-versa?
>
> 2. For System databases, you mentioned it was good enough. So the
> sequence/order of backing model first, then msdb, then master is OK or
> should I change the order ?
>
> 3. For User databases, you mentioned that I should do a Log backup every 1
0
> mins and Database backup once a day. What kind of backup mode should I use
> for Log backup - Overwritten or Appended ?
>
> 4. By Performing Log backups every 10 mins, will it increase the size of
> the Tran Log file ?
> - If yes, how can I control the size automatically without performing a
> shrink on the database (I read bad things about shrinking as it defragment
s
> pages & disk)?
> Thanks,
> Sameer.
> --
> Message posted via http://www.droptable.com|||Hello Mr. Karaszi,
Just to let you know that I have had your site bookmarked since a couple of
yrs ago as its extremely useful. Thanks for your genuine advise.
From the suggestions and reading many forum threads on here, I'm realizing
that my Backup/Restore logic and how I'm doing it, is not up to par.
(A.)
I do master, model, userdb1, userdb2, ... userdbN, msdb. This is because
backup history is in msdb and I want to have backup of latest backup
history. I also do log backup for msdb, but since Agent sets msdb to simple
recovery, I have a job that Agent autostart to set it to full. Some think
that this is overkill, though.
I think your sequence makes a lot of sense. For msdb, I have had this
problem of recovery model being reset from Full to Simple automatically, in
the past. Then I gave up and just kept it as Simple. But I'm curious if you
could share how and when exactly you are resetting msdb's recovery model.
(B.)
Be aware that you can potentially have a situation where you need to
restore 24*6 = 144 log backups. I.e., latest db backup and all subsequent
log backups. This might be fine with you, but you should
be aware of it. One thing to consider is to do a differential backup
perhaps every 2 hours.
I think 144 log backups is overkill. My current Userdb size is 289MB (since
2 yrs!) and it is used daily by approx. 100 employees via our data-driven
web intranet. Given the scenario, what are your recommendations ?
(C.)
What is important is that you need all log backups since latest database
backup in order to do restore. And, you also want some generations of
backups (a few days back or so). Etc. Based on this, you can then plan how
you do your backups, new devices all the time, or re-use same device (with
append), etc.
I'll tell you exactly how I established my backup jobs.
1. I created a group of folders on our backup server (not SQL Server box),
each named after the corresponding database in SQL Server.
2. Inside SQL Server, I created 2 named Backup devices for each database,
pointing to its corresponding network folder. I created 1 for Database
Backup file and 1 for Tran Backup Log file.
e.g
deviceUserdb1Data --> \\backupserver\SQLBackups\Userdb1\Userdb
1Data.bak
deviceUserdb1Log --> \\backupserver\SQLBackups\Userdb1\Userdb
1Log.bak
deviceMasterData --> \\backupserver\SQLBackups\Master\masterD
ata.bak
deviceMasterLog --> \\backupserver\SQLBackups\Master\masterL
og.bak
.
.
.
etc.
3. For Full Database backup, I selected the database in question, right-
click, All Tasks, Backup Database..., General Tab, selected 'Database
Complete', selected the appropriate Backup Device name (suffix with 'Data')
, selected Overwritten, scheduled it to run every hour. Options tab,
selected 1st, 3rd and 4th checkbox.
I repeated the same step 3 for Transaction Log and selected Overwritten
that runs every 15 mins.
As a result, what I end up having is only 2 files for each database
constantly being overwritten. I don't have "series" of Data and Log backup
files.
As suggested by Hari, I tried using Database Maintenance Plan Wizard on my
development machine (as I haven't used this wizard at all) and it created a
"series" of data and log backup files with a timestamp at the end.
Should I use this wizard and if so, how do I limit/recycle/overwrite the
number of data and log files created with unique timestamps ?
(D.)
Compared to what? Every 1 minute? Yes. Every hour? No. The log is emptied
everytime you backup the log.
As I understand, the log gets emptied but the physical file size doesn't
shrink right ? I read your article about NOT shrinking the file.
Message posted via http://www.droptable.com|||> Just to let you know that I have had your site bookmarked since a couple of">
> yrs ago as its extremely useful.
I'm happy to hear that.

> But I'm curious if you
> could share how and when exactly you are resetting msdb's recovery model.
Create an Agent job with one jobstep:
ALTER DATABASE msdb SET RECOVERY FULL
Create a schedule for the job, schedule it as autostart.

> I think 144 log backups is overkill. My current Userdb size is 289MB (sinc
e
> 2 yrs!) and it is used daily by approx. 100 employees via our data-driven
> web intranet. Given the scenario, what are your recommendations ?
No can do. Only your client can decide that. The frequency of backup doesn't
(ultimately) have to do
with amount of data or number of users. In the end it is the amount of data
you can afford to lose
if worst come to worst.

> I'll tell you exactly how I established my backup jobs.
<snip>
It seems that each transaction log backup is overwriting the prior tlog back
up. That makes the tlog
backups useless. You need to sit down and think how you want to handle this.
Maint wiz is good (for
backup) in the sense that it will create a new file each time it does backup
, and thanks to that it
can delete old files. You can do the very same in your own TSQL code. Or you
can use several backup
devices (as I assume you want to keep a few days worth of backups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via droptable.com" <forum@.droptable.com> wrote in message
news:d4e1768dbf05435ca12c31fd4d9b9551@.SQ
droptable.com...
> Hello Mr. Karaszi,
> Just to let you know that I have had your site bookmarked since a couple o
f
> yrs ago as its extremely useful. Thanks for your genuine advise.
> From the suggestions and reading many forum threads on here, I'm realizing
> that my Backup/Restore logic and how I'm doing it, is not up to par.
> (A.)
> I do master, model, userdb1, userdb2, ... userdbN, msdb. This is because
> backup history is in msdb and I want to have backup of latest backup
> history. I also do log backup for msdb, but since Agent sets msdb to simpl
e
> recovery, I have a job that Agent autostart to set it to full. Some think
> that this is overkill, though.
> I think your sequence makes a lot of sense. For msdb, I have had this
> problem of recovery model being reset from Full to Simple automatically, i
n
> the past. Then I gave up and just kept it as Simple. But I'm curious if yo
u
> could share how and when exactly you are resetting msdb's recovery model.
>
> (B.)
> Be aware that you can potentially have a situation where you need to
> restore 24*6 = 144 log backups. I.e., latest db backup and all subsequent
> log backups. This might be fine with you, but you should
> be aware of it. One thing to consider is to do a differential backup
> perhaps every 2 hours.
> I think 144 log backups is overkill. My current Userdb size is 289MB (sinc
e
> 2 yrs!) and it is used daily by approx. 100 employees via our data-driven
> web intranet. Given the scenario, what are your recommendations ?
>
> (C.)
> What is important is that you need all log backups since latest database
> backup in order to do restore. And, you also want some generations of
> backups (a few days back or so). Etc. Based on this, you can then plan how
> you do your backups, new devices all the time, or re-use same device (with
> append), etc.
> I'll tell you exactly how I established my backup jobs.
> 1. I created a group of folders on our backup server (not SQL Server box),
> each named after the corresponding database in SQL Server.
> 2. Inside SQL Server, I created 2 named Backup devices for each database,
> pointing to its corresponding network folder. I created 1 for Database
> Backup file and 1 for Tran Backup Log file.
> e.g
> deviceUserdb1Data --> \\backupserver\SQLBackups\Userdb1\Userdb
1Data.bak
> deviceUserdb1Log --> \\backupserver\SQLBackups\Userdb1\Userdb
1Log.bak
> deviceMasterData --> \\backupserver\SQLBackups\Master\masterD
ata.bak
> deviceMasterLog --> \\backupserver\SQLBackups\Master\masterL
og.bak
> .
> .
> .
> etc.
> 3. For Full Database backup, I selected the database in question, right-
> click, All Tasks, Backup Database..., General Tab, selected 'Database
> Complete', selected the appropriate Backup Device name (suffix with 'Data'
)
> , selected Overwritten, scheduled it to run every hour. Options tab,
> selected 1st, 3rd and 4th checkbox.
> I repeated the same step 3 for Transaction Log and selected Overwritten
> that runs every 15 mins.
> As a result, what I end up having is only 2 files for each database
> constantly being overwritten. I don't have "series" of Data and Log backup
> files.
> As suggested by Hari, I tried using Database Maintenance Plan Wizard on my
> development machine (as I haven't used this wizard at all) and it created
a
> "series" of data and log backup files with a timestamp at the end.
> Should I use this wizard and if so, how do I limit/recycle/overwrite the
> number of data and log files created with unique timestamps ?
>
> (D.)
> Compared to what? Every 1 minute? Yes. Every hour? No. The log is emptied
> everytime you backup the log.
> As I understand, the log gets emptied but the physical file size doesn't
> shrink right ? I read your article about NOT shrinking the file.
> --
> Message posted via http://www.droptable.com|||Create an Agent job with one jobstep:
ALTER DATABASE msdb SET RECOVERY FULL
Create a schedule for the job, schedule it as autostart.
Thanks a lot. So I assume that Agent starts everyday (or Agent resets it to
'Simple' everyday or at some specific time), otherwise the following Data
and Log backups on msdb would fail ?
No can do. Only your client can decide that. The frequency of backup
doesn't (ultimately) have to do with amount of data or number of users. In
the end it is the amount of data you can afford to lose if worst come to
worst.
Just spoke to the client and they are willing to lose 1 hour of data.
It seems that each transaction log backup is overwriting the prior tlog
backup. That makes the tlog backups useless. You need to sit down and think
how you want to handle this.
Would an append on tlog help (on the same backup device) instead of
overwrite?
Maint wiz is good (for backup) in the sense that it will create a new file
each time it does backup, and thanks to that it can delete old files. You
can do the very same in your own TSQL code. Or you can use several backup
devices (as I assume you want to keep a few days worth of backups).
I use the same backup device (network folder) for each database because
every night, those folders get backed up on tape. So I don't need to keep
several days worth of backups on the same folder.
I would like to use Maint Wizard to backup data and tlog on the same backup
device due to its other helpful features like integrity checks,
optimizations, etc.
Given my scenario, could you please guide me how to achieve this so that I
don't have several copies of the backup files on the same folder?
Thank you so much.
Message posted via http://www.droptable.com|||Agent set recovery model to simple for msdb at startup. This is why scheduli
ng the job at startup
work so good.
If you append backups all the time the backup files will grow until they fil
l your disks. Decide
what you want to do, pick the tool (maint wiz, your own jobs with TSQL code
in them having proper
overwrite etc) based on that. I do this on a consultancy basis here in Swede
n, talking to the client
picking the right tool (or code) for the job etc. I don't have the time to a
sk questions enough to
know what I have to know and whip up the code over a newsgroup conversation,
I'm afraid.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via droptable.com" <forum@.droptable.com> wrote in message
news:ff7a1b3783be47078fdfb81576a687a7@.SQ
droptable.com...
> Create an Agent job with one jobstep:
> ALTER DATABASE msdb SET RECOVERY FULL
> Create a schedule for the job, schedule it as autostart.
> Thanks a lot. So I assume that Agent starts everyday (or Agent resets it t
o
> 'Simple' everyday or at some specific time), otherwise the following Data
> and Log backups on msdb would fail ?
>
> No can do. Only your client can decide that. The frequency of backup
> doesn't (ultimately) have to do with amount of data or number of users. In
> the end it is the amount of data you can afford to lose if worst come to
> worst.
> Just spoke to the client and they are willing to lose 1 hour of data.
>
> It seems that each transaction log backup is overwriting the prior tlog
> backup. That makes the tlog backups useless. You need to sit down and thin
k
> how you want to handle this.
> Would an append on tlog help (on the same backup device) instead of
> overwrite?
>
> Maint wiz is good (for backup) in the sense that it will create a new file
> each time it does backup, and thanks to that it can delete old files. You
> can do the very same in your own TSQL code. Or you can use several backup
> devices (as I assume you want to keep a few days worth of backups).
> I use the same backup device (network folder) for each database because
> every night, those folders get backed up on tape. So I don't need to keep
> several days worth of backups on the same folder.
> I would like to use Maint Wizard to backup data and tlog on the same backu
p
> device due to its other helpful features like integrity checks,
> optimizations, etc.
> Given my scenario, could you please guide me how to achieve this so that I
> don't have several copies of the backup files on the same folder?
> Thank you so much.
> --
> Message posted via http://www.droptable.com|||Given that my client is willing to lose the data for 1 hour, I chose to go
with the Simple Recovery Model for both user dbs and system dbs (master,
model, msdb).
Are there any concerns in backing up "system" dbs in Simple mode ?
I used Maintenance Wizard and it works fine.
Message posted via http://www.droptable.com|||> Are there any concerns in backing up "system" dbs in Simple mode ?
Not really. The general consideration applies to system databases the same w
ay as for user
databases; how much data can you afford to lose. So it is all about what inf
ormation you have in the
system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via droptable.com" <forum@.droptable.com> wrote in message
news:4b11b9f76f284d2198959fde3f8412f3@.SQ
droptable.com...
> Given that my client is willing to lose the data for 1 hour, I chose to go
> with the Simple Recovery Model for both user dbs and system dbs (master,
> model, msdb).
> Are there any concerns in backing up "system" dbs in Simple mode ?
> I used Maintenance Wizard and it works fine.
> --
> Message posted via http://www.droptable.com