HOW TO IMPLEMENT LOG BACKUP

Question

HOW TO IMPLEMENT LOG BACKUP

LMtz

Mr or Mrs. 500

Points: 514
More actions
July 20, 2006 at 10:15 am

#113739

HI, I'M TRYING TO IMPLEMENT A NEW BACKUP PLAN FOR MY PSERVER DATABASE (18GB).
EVERY NIGHT MY PSERVER EXECUTES A FULL DATABASE BACKUP, BUT I NEED TO IMPLEMENT A BETTER BACKUP PLAN TO RESTORE PSERVER DATABASE TO AN ESPECIFIC POINT IN TIME.
AS I HAVE READ THE BEST OPTION IS TRANSACTION LOG BACKUP IN COMPLEMENT WITH A FULL DATABASE
BACKUP.
I'M NOT SURE HOW TO DO THIS, AND I NEED SOME HELP TO GUIDE ME, STEP BY STEP TO IMPLEMENT
THANKS FOR YOUR HELP

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

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

dbatodd SSC Eights! Points: 850 More actions · Answer 1

The easiest way to do this is to create a new "Data Maintenance Plan" under the Management tab in EM.

Select the User databases, never select the master db to tlog backups as this can not be done and will error out the job. De-Select database backup and select transaction log backup. Never do database backups and Tlog backups in the same job as they will always be on different schedules. Change the schedule to every x minutes or y hour(s), depending on your level of risk for data lost, between 6AM and 8 PM (Heavy use period). I backup my Production DB every 20 minutes. You can create another job for other hours of less use just do not over lap the times.

Create a subdirectory for each database so it will be easier to recover if needed and delete old files, older than 1 or 2 days max as you already have daily full backup and lots of disk space can be used up quickly. Also choose a disk other than your Data & Log disks as if you lose these disks you have your full backs and tlog backups on another disk.

Name the Plan "20 minute TLog BKUP Job" or something that make sense to you. That is all there is to it. Now restoring if tragedy occurs is a different story.

dbatodd SSC Eights! Points: 850 More actions · Answer 2

I am assuming that your databases are in FULL recovery mode. If they are in SIMPLE recovery mode than you can not do a tlog backup, that is why you can not do a tlog backup of the MASTER DB. If they are change to FULL which is what you want for a Production DB anyway.

LMtz Mr or Mrs. 500 Points: 514 More actions · Answer 3

Yes my database is in Full Model.

If I understanding well when a failure occurs the first thing that I have to do is to restore the last full backup file and then restore every sinlge log bakup file before failure. My question is if I have to remove the database crashed and then create a new one with the last full backup or I need to create a new one with other name?

Thanks for your help.

dbatodd SSC Eights! Points: 850 More actions · Answer 4

Do the Following:

-- Get the FileNames in the backup

RESTORE FILELISTONLY FROM DISK='H:\BACKUPDir\YourDataBase_20060720.Bak'

GO

-- VERIFY the backup is Good

RESTORE VERIFYONLY FROM DISK='H:\BACKUPDir\YourDataBase_20060720.Bak'

GO

-- to see if you need to move the .mdf or .ldf files

sp_helpDB YourDataBase

go

RESTORE DATABASE [YourDataBase] from DISK='H:\BACKUPDir\YourDataBase_20060720.Bak'

with stats = 5, replace,

move 'YourDataBase_Data' to 'H:\MSSQL\Data\YourDataBase_Data.mdf',

move 'YourDataBase_Log' to 'F:\MSSQL\Log\YourDataBase_Log.ldf'

,NORECOVERY

GO

You need “Norecovery” to apply tlogs and also when you restore the tlogs except the last tlog then uses recovery. The “replace” will replace the DB so be careful. See BOL for more info.