Backup strategy

I've recently inherited a position where I am responsible for the well-being
of some DBs.
2 (much) more important than others.
The current recovery model, from what I can tell, is to do a full db/log
backup overnight.
This .bak file is then written to tape as well as saved on the disk for 2
days.

Both these dbs are used fairly extensively 8-5pm and losing data would not
be good.
The db sizes are approx 5gb and 3gb.
This doesn't seem like the ideal situation to me. Everything I read tells
me... full backup periodically, differential nightly and transaction hourly.
Agreed?
If so then I have 2 questions:
1. Is the best way to do this via a maintenance plan or by scripting and
scheduling?
2. What, if any, overhead can be expected with regular transaction backups
during work hours?

A bit of a pointer to #1 would be appreciated also.
Thanks.Both the databases are quite small, so using full, differential and log
backups may be overkill - you might find that a full backup once or
twice a day with log backups every hour (or whatever) is fine. On the
other hand, if the databases are growing fast, then you may want to
allow for that from the beginning.

In my opinion, maintenance plans are useful for quickly getting backups
running in a new environment, but their functionality and flexibility
can be limited. So I would probably use them as a starting point while
I worked out what the longer term backup plan should be. For example, I
don't believe that maintenance plans support differential backups, so
if you need them then you would have to create your own jobs anyway.
See "Reducing Recovery Time" in Books Online for details of using
different backup types together.

The overhead of a transaction log backup is likely to be minimal, but
as always, it's best to test it yourself in your own environment. It
would depend on the number of transactions, the disk layout and the
destination for the backups etc. But even if there is some slight
impact, it's probably a small price to pay for having the backups
available in the case of an outage.

Simon|||Jay,

The only thing I use the maintenance jobs for are two (2) tasks.
1. checking the consistency of the database (usually at 11pm on Sat)
2. doing the reindex of the database (usally at 1 am on Fri)

The rest of the backups I script manually. I create a backup device for
each database.
Job #1 - Full Backup of the database (usually at 6pm each night)
Job #2 - Transaction log backup every hour from 7am to 5pm.
Job #3 - Full backup of the database to a separate device at 12 noon (not
all databases)

Oscar...

"Jay" <jazemail@.gmail.com> wrote in message
news:Hqepe.5753$U4.811596@.news.xtra.co.nz...
> I've recently inherited a position where I am responsible for the
well-being
> of some DBs.
> 2 (much) more important than others.
> The current recovery model, from what I can tell, is to do a full db/log
> backup overnight.
> This .bak file is then written to tape as well as saved on the disk for 2
> days.
> Both these dbs are used fairly extensively 8-5pm and losing data would not
> be good.
> The db sizes are approx 5gb and 3gb.
> This doesn't seem like the ideal situation to me. Everything I read tells
> me... full backup periodically, differential nightly and transaction
hourly.
> Agreed?
> If so then I have 2 questions:
> 1. Is the best way to do this via a maintenance plan or by scripting and
> scheduling?
> 2. What, if any, overhead can be expected with regular transaction backups
> during work hours?
> A bit of a pointer to #1 would be appreciated also.
> Thanks.|||"Oscar Santiesteban Jr." <oscarsantiesteban@.worldnet.att.net> wrote in
message news:Qgtpe.912601$w62.11314@.bgtnsc05-news.ops.worldnet.att.net...
> Jay,
> The only thing I use the maintenance jobs for are two (2) tasks.
> 1. checking the consistency of the database (usually at 11pm on Sat)
> 2. doing the reindex of the database (usally at 1 am on Fri)

Forgive me if I don't follow your practise. Those hours are the only hours
I'm *not* thinking about DBs :)
I've learned that the transaction log is written to 2 HDD (raid) so it isn't
as bad as I thought.
As a precaution I now back up the transaction log to a USB drive each
lunchtime.

Cheers
Jay

> The rest of the backups I script manually. I create a backup device for
> each database.
> Job #1 - Full Backup of the database (usually at 6pm each night)
> Job #2 - Transaction log backup every hour from 7am to 5pm.
> Job #3 - Full backup of the database to a separate device at 12 noon (not
> all databases)
> Oscar...
> "Jay" <jazemail@.gmail.com> wrote in message
> news:Hqepe.5753$U4.811596@.news.xtra.co.nz...
>> I've recently inherited a position where I am responsible for the
> well-being
>> of some DBs.
>> 2 (much) more important than others.
>> The current recovery model, from what I can tell, is to do a full db/log
>> backup overnight.
>> This .bak file is then written to tape as well as saved on the disk for 2
>> days.
>>
>> Both these dbs are used fairly extensively 8-5pm and losing data would
>> not
>> be good.
>> The db sizes are approx 5gb and 3gb.
>> This doesn't seem like the ideal situation to me. Everything I read tells
>> me... full backup periodically, differential nightly and transaction
> hourly.
>> Agreed?
>> If so then I have 2 questions:
>> 1. Is the best way to do this via a maintenance plan or by scripting and
>> scheduling?
>> 2. What, if any, overhead can be expected with regular transaction
>> backups
>> during work hours?
>>
>> A bit of a pointer to #1 would be appreciated also.
>> Thanks.
>>
>>|||"Jay" <jazemail@.gmail.com> wrote in message
news:Hqepe.5753$U4.811596@.news.xtra.co.nz...
> I've recently inherited a position where I am responsible for the
well-being
> of some DBs.
> 2 (much) more important than others.
> The current recovery model, from what I can tell, is to do a full db/log
> backup overnight.
> This .bak file is then written to tape as well as saved on the disk for 2
> days.
> Both these dbs are used fairly extensively 8-5pm and losing data would not
> be good.
> The db sizes are approx 5gb and 3gb.
> This doesn't seem like the ideal situation to me. Everything I read tells
> me... full backup periodically, differential nightly and transaction
hourly.
> Agreed?
> If so then I have 2 questions:
> 1. Is the best way to do this via a maintenance plan or by scripting and
> scheduling?

For this I'd use a simple maintenance plan to back up your logs every X
minutes.

> 2. What, if any, overhead can be expected with regular transaction backups
> during work hours?

VERY little.

We run our transaction log backups every 20 minutes and notice no impact.

(in fact, ironically in some cases, running them MORE often can be better
since they'll be smaller when you do run them.)

> A bit of a pointer to #1 would be appreciated also.
> Thanks.

Backup solution - question about transaction log

I've just inherited (i.e., our sys admin / DBA left the company) a fairly small SQL Server that's running 7 production databases. Most are quite small, but there are two which are about 40gb each. Traffic is quite low - ~30-40 users at one time doing your basic SELECT / UPDATE / INSERT stuff.

Anyway, I was going through some of the backups jobs and noticed that the transaction logs for each database were absolutely huge (in some cases bigger than the DB itself) which led me to think the log wasn't getting truncated.

The T-SQL being run in each case was

Currently, the transaction log for 6 DBs is backed up 3 times a day (and the 7th, "mission critical" DB is backed up every 15 minutes) with the following T-SQL:

BACKUP LOG <database> to <device> WITH NOINIT, NOFORMAT, NOSKIP, NOUNLOAD

5 of the 7 databases get a full backup twice a day, with the 2 larger ones getting a differential, with e.g.,

BACKUP DATABASE <database> TO <device> WITH NOINIT , NOUNLOAD , NAME = N'db', NOSKIP , STATS = 10, DESCRIPTION = N'db', NOFORMAT , MEDIANAME = N'db'DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='db'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='db')
RESTORE VERIFYONLY FROM <device> WITH FILE = @.i

This is then backed up to tape each night.

Looking through the documentation, those WITH commands are largely the default settings so I'm not sure why they're specified explicitly.

If I issue a

BACKUP LOG <database> to <device> WITH INIT, SKIP

then the log does get truncated. However, could someone explain the implications of that for me? As I understand it, INIT will overwrite any existing sets in the device, but considering that it will always backup anything that hasn't been committed then should it be a problem?

Alternatively could someone perhaps explain why the log wasn't getting truncated? It is my understanding that this should happen every time a full backup is completed... which is twice a day. Or does the Transaction log need to be manually shrunk every now and then?

Also, I understand the DECLARE... part in the last part of that DB backup SQL, but is it at all necessary?

Finally, does this backup strategy seem viable? Any thoughts and comments are appreciated!
Matt

By default in the backup database command if you do not specify anything it will append ,eg...

backup database xxxx to disk='F:\test\xxxx.bak' and check the bak file size

now again reissue the same command,

backup database xxxx to disk='F:\test\xxxx.bak' and once again check the bak file size.......it will be double the size

next time issue the same command using WITH INIT option,

backup database xxxx to disk='F:\test\xxxx.bak' WITH INIT now see the bak file size it will be the same size as in 1st case.........if you do not specify anything SQL server will use the default With NOINIT option..........so you need to specify them explicitly if you needed to overwrite........

the log gets truncated if you specify the command,

backup log dbname with truncate_only.........this command will be depreciated in future versions........once you issue this comand, all the committed transactions will be rolled forward and written to data file and uncommitted trans will be rolled back.......

since you have a mission critical db i suggest you go for Log shipping or Database mirroring in sql 2005.........instead of relying on your db backups.........if you have sufficient disk space go for full backup and if you feel the db is important and if you want to have db consistency you can have tran log backups and differential backups else not required........

|||Am I right about when the transaction log should get truncated - i.e., on a full database backup?|||

You are right, that the transaction log should be truncated after a full backup. However, note that the contents of the log will be truncated and the physical file will maintain its size. Therefore, you may see a large transaction log file although it won't be full. In order to reduce the size of the file, you'll need to run some form of SHRINKFILE operation.

HTH!