Back up Strategy

Question

Back up Strategy

ekonagu

SSChasing Mays

Points: 657
More actions
August 19, 2009 at 12:27 am

#138304

helo guys
i am having production database
i need to take backup
which back up strategy should i follow and how.
wht i mean is full,differential,transaction log and restoring the same.
i need to know the perfect strategy tht big companies are using
hope u guys can help me out this

Viewing 15 posts - 1 through 15 (of 17 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's no single perfect backup strategy. It depends on the size of the DB, the amount of data that's allowed to be lost in the case of a disaster, the amount of downtime that's allowed, etc.

If you want advice, you're going to need to give a lot more information.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 2

We follow the below strategy

Daily Full Backup

and Every 2 hrs Transaction Log Backup

so the data loss in case of failure will be last 2 hrs data..

so how do you want to plan, it also depends on the agreement with the client you maintaining the database for...

Thanks ,

Shekhar

ekonagu SSChasing Mays Points: 657 More actions · Answer 3

so daily full back up and 2 hrs interval transaction log its okk

so this full back up will replace existing backup or every day a new file wil be there ???

and in transaction log new files in every two hours or one file only ??

and wht abt differential back up

ekonagu SSChasing Mays Points: 657 More actions · Answer 4

ekonagu

SSChasing Mays

Points: 657

August 19, 2009 at 2:04 am

#1041319

size of db wil be 5 gb and down time can be 30 min

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 5

noooooooo

we will have maintenance plans for full backup and transaction log backup. For every backup there will be a new file.

we will keep last 3 days full backups in the server

and a copy will also be moved to tape (tape will contain latest one month backups)

Transaction log backups will be available in server(new file created for every 2 hrs pre and post the full backup).

Thanks ,

Shekhar

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

ekonagu (8/19/2009)
size of db wil be 5 gb and down time can be 30 min

Then you need to design a backup strategy so that, no matter what, you can restore within 30 minutes. Do some testing (on a dev/test server) and see what works best.

What's the allowable data loss?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shabba SSCertifiable Points: 5007 More actions · Answer 7

There's no single perfect backup strategy. It depends on the size of the DB, the amount of data that's allowed to be lost in the case of a disaster, the amount of downtime that's allowed, etc.
If you want advice, you're going to need to give a lot more information.

Echo Gila's advice... it's impossible to suggest you should be doing 'this & that' without further information from you. What fits one organisation doesn't necessarily mean it can be used in the next.

Mark

ekonagu SSChasing Mays Points: 657 More actions · Answer 8

thts ok it wil be diffrenet strategies for different organisations.but i just need the basic inf only

ekonagu SSChasing Mays Points: 657 More actions · Answer 9

i am doing the restoration of database.so i have to make up the last updated data.so i am taking the tail log backup so the database is in restoring state.so whts the next step.its still showing restoring.wht i have to do next.

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 10

restore the tail log backup with

"WITH RECOVERY " optiona

instead of WITH NO RECOVERY

Thanks ,

Shekhar

CJ Deaver SSC Veteran Points: 231 More actions · Answer 11

Another thing to consider is when to do full backups if your hardware is older or not very fast. The full backups can create deadlocks and hold up your server.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

CJ (8/19/2009)
The full backups can create deadlocks and hold up your server.

If you're talking about SQL deadlocks, then no. Backups don't take locks. They can increase IO load and slow the entire system down.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29422 More actions · Answer 13

ekonagu (8/19/2009)
size of db wil be 5 gb and down time can be 30 min

I suspect you're confused between the downtime duration and amount of allowable data loss. Downtime will be the time you'll need to bring back the database online in case of disaster. This depends hugely on the size of the database plus speed of your hardware resources.

Frequency of transaction log tells you how much data your organization can afford to lose in case of a disaster. (if your transaction log gets corrupted, you'll lose the data until your last transaction log backup).

Pradeep Singh

Xxx Right there with Babe Points: 738 More actions · Answer 14

Make sure when you decide on a backup plan you build a well documented restore strategy. Lets say you're doing a full backup every night at midnight, differential backup at noon and a transaction log backup every 2 hours starting at 2AM. A failure occurs at 1:00PM. What which backups are you going to restore?

The above seems like a straightforward answer, however, in a panic, things can get pretty messy. This information is extremely important to minimize downtime. If you don't know, you'll have to make another post here and wait for someone to reply, etc.