Thursday, March 29, 2012

Backup Strategy for MSSQL

Hi everyone. As the company's "DBA" (long story) I wanted to get some opinions on my backup plan. Currently, I have the following in place:

Full weekly backup of master.
msdb is treated as a user database, so msdb and two user databases receive Full weekly backups, Daily differentials and hourly transaction log backups.

The maintenence plan will remove logs older than 2 weeks.

Our networking group backs up the MSSQL to tape regularly.

I'm fairly new to DBA work, but if this scenario sounds like a sound plan, I'd appreciate any feedback. Thanks in advance!We back our stuff up nightly, however you have to decide how much info you are willing to lose should your system crash. If weekly backups have been working and you feel confident in your hardware then stick to that schedule.


HTH,
Aric|||

Quote:

Originally Posted by DbAFtW

Hi everyone. As the company's "DBA" (long story) I wanted to get some opinions on my backup plan. Currently, I have the following in place:

Full weekly backup of master.
msdb is treated as a user database, so msdb and two user databases receive Full weekly backups, Daily differentials and hourly transaction log backups.

The maintenence plan will remove logs older than 2 weeks.

Our networking group backs up the MSSQL to tape regularly.

I'm fairly new to DBA work, but if this scenario sounds like a sound plan, I'd appreciate any feedback. Thanks in advance!


Hi there,

Backup / recovery plan should be designed based on the importance of your data. As a consultant i am managing 17 database servers, some servers are configured to run backup routine every hour, whereas, some servers are configured to run backup routine every day. Good luck & Take care.|||Thanks folks for the replies.
Well, the vendors that put the web server and the MSSQL DB in place were using a Simple Recovery Model and I felt the nature of the data dictated transaction log backups. The system accepts college applications and I felt any data loss was something I wanted to avoid due to the importance of that data! The intention is that we can experience minimal loss utilizing the strategy to restore from the weekly full, nightly differential and every half hour transaction log backups.|||</bumpitybump>sql

backup strategy for mirroring servers

Hi guys.
We have a DB system that is relatively small and transactions are not very
big, but very important, losing data will be very costly to the business.
I am assigned to port this system to SQL Server 2005 Sp1, we have decided to
implement the DB mirroring with the High Protection mode.
I have some questions regarding backup and restore.
Suppose we utilize 3 machines, A is the principal server, B is the mirroring
server, and C is the file server on which the backup files are stored.
My questions are:
1. Currently I have created 3 SQL Server Agent jobs to backup the principal
server, a) full backup once a day, b) differential backup once every 4 hours
,
c) transaction log back once every 15 minutes. The question is: should I
change the backup file (device) every day? Or I can use one backup
file(device) for all the backups day in and day out?
2. How do I backup the mirroring server? I think I can not do anything on
the mirroring server when it is in the Mirroring/Sync mode. And if I had the
same 3 agent jobs on the mirroring server, the jobs would fail? But what if
the principal server fails over, and mirroring server becomes the principal
server, do I have to create the backup agent jobs after failover?
3. When creating the mirroring server backup, can I reuse the same backup
file name(s) that I used on the principal server? or I better off storing th
e
backup file from the mirroring server on a different location?
4. Last question, not particular related to backup Should I store the
.MDF/.LDF file for the principal server and/or mirror server on machine C?
Thanks a lot!
WenbiaoSee comments inline below:
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
> Hi guys.
> We have a DB system that is relatively small and transactions are not very
> big, but very important, losing data will be very costly to the business.
> I am assigned to port this system to SQL Server 2005 Sp1, we have decided
to
> implement the DB mirroring with the High Protection mode.
> I have some questions regarding backup and restore.
> Suppose we utilize 3 machines, A is the principal server, B is the mirrori
ng
> server, and C is the file server on which the backup files are stored.
> My questions are:
> 1. Currently I have created 3 SQL Server Agent jobs to backup the principa
l
> server, a) full backup once a day, b) differential backup once every 4 hou
rs,
> c) transaction log back once every 15 minutes. The question is: should I
> change the backup file (device) every day? Or I can use one backup
> file(device) for all the backups day in and day out?
You have to decide this for yourself. You most probably want a few generatio
ns of the backups, and
whether to only have those on tape and also disk will influence this. I assu
me you are aware of the
INIT and NOINIT options.

> 2. How do I backup the mirroring server? I think I can not do anything on
> the mirroring server when it is in the Mirroring/Sync mode. And if I had t
he
> same 3 agent jobs on the mirroring server, the jobs would fail? But what i
f
> the principal server fails over, and mirroring server becomes the principa
l
> server, do I have to create the backup agent jobs after failover?
Run the same job on both servers. Have a preceeding jobstep which check the
mirroring catalog view
whether that server is primary or not. If not primary, exit with success, el
se do the backup.

> 3. When creating the mirroring server backup, can I reuse the same backup
> file name(s) that I used on the principal server? or I better off storing
the
> backup file from the mirroring server on a different location?
Basically same answer as 1. Logicaly, it doesn't matter from what machine th
e backup came. This
would work in faviour for using the same backup devices.

> 4. Last question, not particular related to backup Should I store the
> .MDF/.LDF file for the principal server and/or mirror server on machine C?
No, SQL Server doesn't support storing files on a mapped/UNC drive. Need to
be local, SAN or ISCSI.

> Thanks a lot!
> Wenbiao|||Tibor is correct that you cannot store database files on a UNC share,
however, you can store the backup files on a UNC share location.
Personally, I use a script to create a new backup file on a remote share for
each backup using a date and time stamp as part of the file name (just like
a DB maintenance plan). I have a separate job to clean out old backups
which makes it easy to adjust the retention time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ugBYISWwGHA.4972@.TK2MSFTNGP05.phx.gbl...
> See comments inline below:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
> news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
> You have to decide this for yourself. You most probably want a few
> generations of the backups, and whether to only have those on tape and
> also disk will influence this. I assume you are aware of the INIT and
> NOINIT options.
>
> Run the same job on both servers. Have a preceeding jobstep which check
> the mirroring catalog view whether that server is primary or not. If not
> primary, exit with success, else do the backup.
>
> Basically same answer as 1. Logicaly, it doesn't matter from what machine
> the backup came. This would work in faviour for using the same backup
> devices.
>
> No, SQL Server doesn't support storing files on a mapped/UNC drive. Need
> to be local, SAN or ISCSI.
>
>

backup strategy for mirroring servers

Hi guys.
We have a DB system that is relatively small and transactions are not very
big, but very important, losing data will be very costly to the business.
I am assigned to port this system to SQL Server 2005 Sp1, we have decided to
implement the DB mirroring with the High Protection mode.
I have some questions regarding backup and restore.
Suppose we utilize 3 machines, A is the principal server, B is the mirroring
server, and C is the file server on which the backup files are stored.
My questions are:
1. Currently I have created 3 SQL Server Agent jobs to backup the principal
server, a) full backup once a day, b) differential backup once every 4 hours,
c) transaction log back once every 15 minutes. The question is: should I
change the backup file (device) every day? Or I can use one backup
file(device) for all the backups day in and day out?
2. How do I backup the mirroring server? I think I can not do anything on
the mirroring server when it is in the Mirroring/Sync mode. And if I had the
same 3 agent jobs on the mirroring server, the jobs would fail? But what if
the principal server fails over, and mirroring server becomes the principal
server, do I have to create the backup agent jobs after failover?
3. When creating the mirroring server backup, can I reuse the same backup
file name(s) that I used on the principal server? or I better off storing the
backup file from the mirroring server on a different location?
4. Last question, not particular related to backup :) Should I store the
.MDF/.LDF file for the principal server and/or mirror server on machine C?
Thanks a lot!
WenbiaoSee comments inline below:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
> Hi guys.
> We have a DB system that is relatively small and transactions are not very
> big, but very important, losing data will be very costly to the business.
> I am assigned to port this system to SQL Server 2005 Sp1, we have decided to
> implement the DB mirroring with the High Protection mode.
> I have some questions regarding backup and restore.
> Suppose we utilize 3 machines, A is the principal server, B is the mirroring
> server, and C is the file server on which the backup files are stored.
> My questions are:
> 1. Currently I have created 3 SQL Server Agent jobs to backup the principal
> server, a) full backup once a day, b) differential backup once every 4 hours,
> c) transaction log back once every 15 minutes. The question is: should I
> change the backup file (device) every day? Or I can use one backup
> file(device) for all the backups day in and day out?
You have to decide this for yourself. You most probably want a few generations of the backups, and
whether to only have those on tape and also disk will influence this. I assume you are aware of the
INIT and NOINIT options.
> 2. How do I backup the mirroring server? I think I can not do anything on
> the mirroring server when it is in the Mirroring/Sync mode. And if I had the
> same 3 agent jobs on the mirroring server, the jobs would fail? But what if
> the principal server fails over, and mirroring server becomes the principal
> server, do I have to create the backup agent jobs after failover?
Run the same job on both servers. Have a preceeding jobstep which check the mirroring catalog view
whether that server is primary or not. If not primary, exit with success, else do the backup.
> 3. When creating the mirroring server backup, can I reuse the same backup
> file name(s) that I used on the principal server? or I better off storing the
> backup file from the mirroring server on a different location?
Basically same answer as 1. Logicaly, it doesn't matter from what machine the backup came. This
would work in faviour for using the same backup devices.
> 4. Last question, not particular related to backup :) Should I store the
> .MDF/.LDF file for the principal server and/or mirror server on machine C?
No, SQL Server doesn't support storing files on a mapped/UNC drive. Need to be local, SAN or ISCSI.
> Thanks a lot!
> Wenbiao|||Tibor is correct that you cannot store database files on a UNC share,
however, you can store the backup files on a UNC share location.
Personally, I use a script to create a new backup file on a remote share for
each backup using a date and time stamp as part of the file name (just like
a DB maintenance plan). I have a separate job to clean out old backups
which makes it easy to adjust the retention time.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ugBYISWwGHA.4972@.TK2MSFTNGP05.phx.gbl...
> See comments inline below:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wenbiao Liang" <Wenbiao Liang@.discussions.microsoft.com> wrote in message
> news:498AD6C0-1131-4DFC-9C24-3117EB7379CF@.microsoft.com...
>> Hi guys.
>> We have a DB system that is relatively small and transactions are not
>> very
>> big, but very important, losing data will be very costly to the business.
>> I am assigned to port this system to SQL Server 2005 Sp1, we have decided
>> to
>> implement the DB mirroring with the High Protection mode.
>> I have some questions regarding backup and restore.
>> Suppose we utilize 3 machines, A is the principal server, B is the
>> mirroring
>> server, and C is the file server on which the backup files are stored.
>> My questions are:
>> 1. Currently I have created 3 SQL Server Agent jobs to backup the
>> principal
>> server, a) full backup once a day, b) differential backup once every 4
>> hours,
>> c) transaction log back once every 15 minutes. The question is: should I
>> change the backup file (device) every day? Or I can use one backup
>> file(device) for all the backups day in and day out?
> You have to decide this for yourself. You most probably want a few
> generations of the backups, and whether to only have those on tape and
> also disk will influence this. I assume you are aware of the INIT and
> NOINIT options.
>
>> 2. How do I backup the mirroring server? I think I can not do anything on
>> the mirroring server when it is in the Mirroring/Sync mode. And if I had
>> the
>> same 3 agent jobs on the mirroring server, the jobs would fail? But what
>> if
>> the principal server fails over, and mirroring server becomes the
>> principal
>> server, do I have to create the backup agent jobs after failover?
> Run the same job on both servers. Have a preceeding jobstep which check
> the mirroring catalog view whether that server is primary or not. If not
> primary, exit with success, else do the backup.
>
>> 3. When creating the mirroring server backup, can I reuse the same backup
>> file name(s) that I used on the principal server? or I better off storing
>> the
>> backup file from the mirroring server on a different location?
> Basically same answer as 1. Logicaly, it doesn't matter from what machine
> the backup came. This would work in faviour for using the same backup
> devices.
>
>> 4. Last question, not particular related to backup :) Should I store the
>> .MDF/.LDF file for the principal server and/or mirror server on machine
>> C?
> No, SQL Server doesn't support storing files on a mapped/UNC drive. Need
> to be local, SAN or ISCSI.
>
>> Thanks a lot!
>> Wenbiao
>

backup strategy for a 1000GB database

Hi,

Could anyone tell me the backup strategy for a 1000GB database?

Thank you!

Peter Wang

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!The size of the database is one consideration, but probably more important
is how much data loss and downtime you can afford in the event of a problem.
You really need to get this information first, so you can decide how often
to do full/differential/log backups. This will also help you to decide how
much money you can reasonably allocate to your backup/availability solution.
There is a lot of information in BOL in the section called "Desiging a
Backup and Restore Strategy".

If you have the database on a NAS or SAN already, you may be able to use
features of the hardware (some storage devices support MSSQL snapshot
backup/restore). On the other hand, backing up to multiple disk devices
might be a good choice, depending on your requirements and storage
configuration.

Finally, don't forget that as a rule, backing up is easier than restoring.
So also consider how easily your solution will allow you to restore a
database if you need to. It's hard to be specific, as a lot depends on your
environment and your application/user requirements.

Simon

"peter wang" <anonymous@.devdex.com> wrote in message
news:3ef88c70$0$201$75868355@.news.frii.net...
> Hi,
> Could anyone tell me the backup strategy for a 1000GB database?
> Thank you!
> Peter Wang
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

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.sqlmonster.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 SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:d0f492b8d6dd48f497b96708d471369d@.SQLMonster.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.sqlmonster.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.sqlmonster.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 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.
> 3. For User databases, you mentioned that I should do a Log backup every 10
> 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 with 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 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.
> 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 everytime 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 restore architecture in SQL
Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:1a93f786c3c5435eafa4d96f81eb5393@.SQLMonster.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.sqlmonster.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\Userdb1Data.bak
deviceUserdb1Log --> \\backupserver\SQLBackups\Userdb1\Userdb1Log.bak
deviceMasterData --> \\backupserver\SQLBackups\Master\masterData.bak
deviceMasterLog --> \\backupserver\SQLBackups\Master\masterLog.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.sqlmonster.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 (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 ?
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 backup. 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 SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d4e1768dbf05435ca12c31fd4d9b9551@.SQLMonster.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\Userdb1Data.bak
> deviceUserdb1Log --> \\backupserver\SQLBackups\Userdb1\Userdb1Log.bak
> deviceMasterData --> \\backupserver\SQLBackups\Master\masterData.bak
> deviceMasterLog --> \\backupserver\SQLBackups\Master\masterLog.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.sqlmonster.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.sqlmonster.com|||Agent set recovery model to simple for msdb at startup. This is why scheduling the job at startup
work so good.
If you append backups all the time the backup files will grow until they fill 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 Sweden, talking to the client
picking the right tool (or code) for the job etc. I don't have the time to ask 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 SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ff7a1b3783be47078fdfb81576a687a7@.SQLMonster.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.sqlmonster.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.sqlmonster.com|||> Are there any concerns in backing up "system" dbs in Simple mode ?
Not really. The general consideration applies to system databases the same way as for user
databases; how much data can you afford to lose. So it is all about what information 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 SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4b11b9f76f284d2198959fde3f8412f3@.SQLMonster.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.sqlmonster.com|||Tibor,
Thank you very much for your help.
Now, I have a very good understanding of what kind of Recovery Model to
choose, depending on one's tolerance for data loss.
I highly appreciate for sharing your wisdom.
Sameer.
--
Message posted via http://www.sqlmonster.com|||At work we have one rule. Don't expect the most recent backup to be good,
but rather the one before that. You never know what could lead to data loss,
and probably the last backup is too late if you have corruption.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f03b84bcad554daa936eaad4548e735d@.SQLMonster.com...
> Tibor,
> Thank you very much for your help.
> Now, I have a very good understanding of what kind of Recovery Model to
> choose, depending on one's tolerance for data loss.
> I highly appreciate for sharing your wisdom.
> Sameer.
> --
> Message posted via http://www.sqlmonster.com|||Good point, Mike. This is yet another advantage using log backups. Assume you have a corruption, and
that the most recent db backup is also corrupt. In many cases, you can do a last log backup, apply
the next most recent db backup and all subsequent log backup, resulting in zero data loss.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:e$5Ud7KXFHA.2256@.TK2MSFTNGP14.phx.gbl...
> At work we have one rule. Don't expect the most recent backup to be good, but rather the one
> before that. You never know what could lead to data loss, and probably the last backup is too late
> if you have corruption.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:f03b84bcad554daa936eaad4548e735d@.SQLMonster.com...
>> Tibor,
>> Thank you very much for your help.
>> Now, I have a very good understanding of what kind of Recovery Model to
>> choose, depending on one's tolerance for data loss.
>> I highly appreciate for sharing your wisdom.
>> Sameer.
>> --
>> Message posted via http://www.sqlmonster.com
>|||Thanks for bringing up a very critical point.
Currently, I do database backups every hour (Simple mode) and I have
specified inside Maintenance Plan to remove last hour's backup file.
I will modify it to remove last 2 hour's instead.
Thanks,
Sameer.
--
Message posted via http://www.sqlmonster.com|||Hi
Any reason you are not using transaction log dumps every xx minutes and a
full DB dump every day?
It uses so much less resources doing a transaction log backup compared to a
full DB backup. On day, the DB backup time will get to a point where it runs
longer than 1 hour due to data size.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sameer Premji via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:516f642290114678b620b9579ea8b265@.SQLMonster.com...
> Thanks for bringing up a very critical point.
> Currently, I do database backups every hour (Simple mode) and I have
> specified inside Maintenance Plan to remove last hour's backup file.
> I will modify it to remove last 2 hour's instead.
> Thanks,
> Sameer.
> --
> Message posted via http://www.sqlmonster.com|||Hi Mike,
Say, if I were to do Tran Log backups every 15 mins, that would dump 4
files per hour * 24 hours = 96 log files ! and thats just for 1 database.
And I have 2 SQL Servers - 2K and 7, for a total of 12 databases (system &
user dbs).
Having a dependency on 96 files is somewhat uneasing for me and I'm sure as
soon as my Network Admin sees those files in every database-sub-folder,
he'll freak out and create a hoopla.
But you are correct that a log backup consumes less resources and its much
quicker than doing a full database backup every hour.
My biggest database size so far is 290 MB and it usually takes 2 mins & 50
secs.
What do you think ?
--
Message posted via http://www.sqlmonster.com

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.com
Hi,
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@.droptable.co m...
> 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 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.

> 3. For User databases, you mentioned that I should do a Log backup every 10
> 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 with 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 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.

> 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 everytime 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 restore 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@.droptable.co m...
> 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
|||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\Userdb1Data.bak
deviceUserdb1Log --> \\backupserver\SQLBackups\Userdb1\Userdb1Log.bak
deviceMasterData --> \\backupserver\SQLBackups\Master\masterData.bak
deviceMasterLog --> \\backupserver\SQLBackups\Master\masterLog.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 (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 ?
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 backup. 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@.droptable.co m...
> 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\Userdb1Data.bak
> deviceUserdb1Log --> \\backupserver\SQLBackups\Userdb1\Userdb1Log.bak
> deviceMasterData --> \\backupserver\SQLBackups\Master\masterData.bak
> deviceMasterLog --> \\backupserver\SQLBackups\Master\masterLog.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 scheduling the job at startup
work so good.
If you append backups all the time the backup files will grow until they fill 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 Sweden, talking to the client
picking the right tool (or code) for the job etc. I don't have the time to ask 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@.droptable.co m...
> 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
|||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 way as for user
databases; how much data can you afford to lose. So it is all about what information 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@.droptable.co m...
> 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
sql

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