Simple Backup for Small Database

Question

Simple Backup for Small Database

PhilM99

SSCrazy

Points: 2214
More actions
September 23, 2009 at 9:13 pm

#211401

Setup:
SS 2008
Small Online Database (127Mb)
A few hundred web transactions per day modify the database.
This seems so complicated I have been reduced to detaching the db every day (mostly) and making a zip copy of it for backup and then reattaching it. So far so good except the log file has grown to 377Mb.
I have recovery set to SIMPLE.
I managed to cook up a maintenance plan that runs every night at 3am that does two things...
1. Does a FULL backup of the database
2. Backs up the transaction log.
This seems to work in that it gives me two files every night.
(a .bak file and a .trn file).
But the log keeps on growing and despite studying this endlessly including on this site I'm lost in the terminiology between truncate and shrink and simple and full. I have even read the treatises written by various gurus.
Sorry, but I'm not a DBA nor do I plan to be one.
I need to know how to get a daily backup of the database, how to run a differential backup maybe every hour during the day, and how to get the log to stop growing... because once I've done my backup the log file size should reset itself or something.
I like to use the SSMS for the setup because the DBCC commands are kinda Greek to me (no disrespect meant to people from Greece).
I need to know what to do.
Should I set the recovery model to FULL?
Should I continue to do a full backup every night?
How do I do a differential backup hourly?
Is there a way to back up the log and do I need to do that, and how to I get it to reset itself so that it is smaller again?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Silverfox SSC-Insane Points: 23150 More actions · Answer 1

ok, lets see if I can cut through some of the issues here.

a (.LDF) log file, will always grow, when you backup the log, you are removing all the transactions that have been flushed to disk. but it will not affect the log file physical size. it just increases the free space within the log file. the truncate option for the backup log chops off the free space at the end of the transaction log.

To physically resize the log file, if it is too big, is to do a backup log, then use dbcc shrinkfile, and shrink the log file, this can cause fragmentation which you can sort later on, by rebuilding the indexes.

Lets base this on an database called adventureworks

To shrink the log

USE [AdventureWorks]

GO

DBCC SHRINKFILE (N'AdventureWorks_Log' , 0, TRUNCATEONLY)

GO

to do a differential backup

BACKUP DATABASE [AdventureWorks] TO DISK = N'c:\AdventureWorks.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

to do a log backup

BACKUP LOG [AdventureWorks] TO DISK = N'c:\AdventureWorks.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

These are the default options.

To fix your current situation, I would suggest doing a log backup, followed by a dbcc shrinkfile. that should bring the size of the log file down.

You need to change the recovery model to full, otherwise you cannot do log backups. depending on the number of transaction per day, you could schedule your backup log every hour perhaps.

Another thing to remember, in your maintenance plan you can also schedule log backups and shrinking files if needed.

If you need clarification on any of this, let me know.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 2

...
Should I set the recovery model to FULL?

If you want to be able to perform point in time restores, you should set it to full recovery mode.

Should I continue to do a full backup every night?

That depends on its size and modification load.

How do I do a differential backup hourly?

I wouldn't do a defferential backup, because a differential backup will need to read the whole datapages of your dabase !

I would prefer making incremental LOG backups.

Is there a way to back up the log and do I need to do that, and how to I get it to reset itself so that it is smaller again?

Check out "Backup log" in books online.

The scenario I install by default for a database is:

1) every x hours an incremental logbackup. (x depends on the load and the SLA of the database)

2) every x days a full backup

The way it works:

3 jobs:

A) dbnameFULL following steps for each db:

1) BACKUP LOG [Db] TO [DbLog] WITH NOINIT , NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Full Backup' , NOFORMAT

2) BACKUP DATABASE [Db] TO [DbFULL] WITH INIT , NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Log Backup' , NOFORMAT

B) dbnameLog_Incremental following step for each db:

1) BACKUP LOG [Db] TO [DbLog] WITH NOINIT , NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Log Backup' , NOFORMAT

C) dbnameLog_Init following step for each db:

1) BACKUP LOG [Db] TO [DbLog] WITH INIT , NOUNLOAD , NAME = 'Db_Log', SKIP , STATS = 10, DESCRIPTION = 'Log Backup' , NOFORMAT

Every job copies the .Bak files to its own safe zone.

How it works:

- Job dbnameFULL starts with disabling dbnameLog_Incremental renaming it to "BU_PROC_Disabled_dbnameLog_Incremental". (The rename is done just to keep track the backup sequence is on going)

Right before its last step, it sets the next run-time for the dbnameLog_Init job

as last step , it copies all bak.files to its own safe zone.

- Job dbnameLog_Init has a "run only once" schedule that is being modified by dbnameFull job.

Job dbnameLog_Init makes LOG backups (using the with INI parameter)

Before it copies the bak files to its own safe zone, it enables (and renames back to original) the BU_PROC_Disabled_dbnameLog_Incremental job.

-edited-

We only shrink files if we run out of space or after we planned a huge data cleanup (and don't expect teh data to grow within a reasonable periode of time !

Because of the frequent log backups, the log file(s) shouldn't grow that much, unless someone performes an exceptional huge transaction)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

PhilM99 SSCrazy Points: 2214 More actions · Answer 3

Thanks to Silverfox and ALZDBA for the quick replies!

OK, if I do this am I on the right track?:

1) Manually:

Set recovery model to Full

Do a log backup using the DBCC command with INIT to get the log file contents to a reasonable size

Do a Shrinfile of the log using the DBCC command to get the file size back down

2) Set up scheduled jobs steps to

Backup the database fully in the middle of the night

i.e. BackupLOG with NOINIT, BACKUP Database with INIT (what does that mean, I'll look itup)

Schedule an Incremental Backup of the log, say, hourly during daylighthours (that's probably NOINIT, too)

at some point schedule a log backup with INIT (I don't get that part)

ALZDBA, I don't understand why you have the complex arrangement of jobs modifying the schedule of other jobs. What is it that you are achieving by doing that?

Thanks

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 4

PhilM99 (9/24/2009)
Thanks to Silverfox and ALZDBA for the quick replies!
OK, if I do this am I on the right track?:
1) Manually:
Set recovery model to Full
Do a log backup using the DBCC command with INIT to get the log file contents to a reasonable size
Do a Shrinfile of the log using the DBCC command to get the file size back down
2) Set up scheduled jobs steps to
Backup the database fully in the middle of the night
i.e. BackupLOG with NOINIT, BACKUP Database with INIT (what does that mean, I'll look itup)
Schedule an Incremental Backup of the log, say, hourly during daylighthours (that's probably NOINIT, too)
at some point schedule a log backup with INIT (I don't get that part)
ALZDBA, I don't understand why you have the complex arrangement of jobs modifying the schedule of other jobs. What is it that you are achieving by doing that?
Thanks

You will understand that once you get to your statement

(what does that mean, I'll look itup)

The INIT parameter means it will overwrite the given backup file's content.

The NOINIT parameter means it will NOT overwrite the file's content.

I've built in this little piece of rocket science :hehe: with the construct of these 3 jobs because I don't want to have two backup jobs running at the same time and because I aim for incremental log-bak files having a single file spanning between two full backup sequences.

The separate Log _Init job is in place because the last step of the Full bak job has this xcopy step towards the safe zone.

By default, I alter the schedule of the log_init job the current time + 30 minutes.

Since the log_init will overwrite the log-bak file, size of that file will be small.

After that, the incremental log backup job is activated, and the circle is complete.